ProxySQL: Configure Monitoring
Creating & Monitoring User
-- create `proxysql` user
CREATE USER 'proxysql'@'%' IDENTIFIED WITH mysql_native_password by 'pmm_mon1t0r';
-- grant privilelge
GRANT USAGE ON *.* TO 'proxysql'@'%';
Update mysql-monitor_username
UPDATE global_variables
SET variable_value='proxysql'
WHERE variable_name='mysql-monitor_username';
Update mysql-monitor_password
UPDATE global_variables
SET variable_value='pmm_mon1t0r'
WHERE variable_name='mysql-monitor_password';
Update monitoring intervals
UPDATE global_variables
SET variable_value='2000'
WHERE variable_name
IN (
'mysql-monitor_connect_interval',
'mysql-monitor_ping_interval',
'mysql-monitor_read_only_interval');
Display monitor variables
SELECT * FROM global_variables
WHERE variable_name
LIKE 'mysql-monitor_%';
result:
+--------------------------------------------------------------+------------------+
| variable_name | variable_value |
+--------------------------------------------------------------+------------------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_groupreplication_healthcheck_max_timeout_count | 3 |
| mysql-monitor_groupreplication_max_transactions_behind_count | 3 |
| mysql-monitor_galera_healthcheck_interval | 5000 |
| mysql-monitor_galera_healthcheck_timeout | 800 |
| mysql-monitor_galera_healthcheck_max_timeout_count | 3 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-monitor_username | proxysql_monitor |
| mysql-monitor_password | sql_mon1t0r |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 2000 |
| mysql-monitor_ping_interval | 2000 |
| mysql-monitor_read_only_interval | 2000 |
| mysql-monitor_read_only_timeout | 500 |
+--------------------------------------------------------------+------------------+
Load variables to runtime
LOAD MYSQL VARIABLES TO RUNTIME;
Permanently stored variables to disk
SAVE MYSQL VARIABLES TO DISK;
Backend’s health check
Show tables from monitor database
proxy_admin> show tables from monitor;
+--------------------------------------+
| tables |
+--------------------------------------+
| mysql_server_aws_aurora_check_status |
| mysql_server_aws_aurora_failovers |
| mysql_server_aws_aurora_log |
| mysql_server_connect_log |
| mysql_server_galera_log |
| mysql_server_group_replication_log |
| mysql_server_ping_log |
| mysql_server_read_only_log |
| mysql_server_replication_lag_log |
+--------------------------------------+
Display mysql_server_connect_log
SELECT *
FROM monitor.mysql_server_connect_log
ORDER BY time_start_us DESC
LIMIT 10;
result:
+--------------+------+------------------+-------------------------+---------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+--------------+------+------------------+-------------------------+---------------+
| 10.10.10.22 | 3306 | 1595089624174123 | 1247 | NULL |
| 10.10.10.210 | 3306 | 1595089624140255 | 785 | NULL |
| 10.10.10.210 | 3306 | 1595089622176072 | 792 | NULL |
| 10.10.10.22 | 3306 | 1595089622140146 | 1254 | NULL |
| 10.10.10.210 | 3306 | 1595089620167651 | 786 | NULL |
| 10.10.10.22 | 3306 | 1595089620140042 | 1314 | NULL |
| 10.10.10.210 | 3306 | 1595089618164266 | 782 | NULL |
| 10.10.10.22 | 3306 | 1595089618139939 | 1348 | NULL |
| 10.10.10.22 | 3306 | 1595089616176055 | 1341 | NULL |
| 10.10.10.210 | 3306 | 1595089616139823 | 779 | NULL |
+--------------+------+------------------+-------------------------+---------------+
Display mysql_server_ping_log
SELECT *
FROM monitor.mysql_server_ping_log
ORDER BY time_start_us DESC
LIMIT 10;
result:
+--------------+------+------------------+----------------------+------------+
| hostname | port | time_start_us | ping_success_time_us | ping_error |
+--------------+------+------------------+----------------------+------------+
| 10.10.10.210 | 3306 | 1595089666423547 | 160 | NULL |
| 10.10.10.22 | 3306 | 1595089666396191 | 398 | NULL |
| 10.10.10.210 | 3306 | 1595089664420034 | 157 | NULL |
| 10.10.10.22 | 3306 | 1595089664396076 | 406 | NULL |
| 10.10.10.22 | 3306 | 1595089662416829 | 394 | NULL |
| 10.10.10.210 | 3306 | 1595089662396027 | 160 | NULL |
| 10.10.10.22 | 3306 | 1595089660430673 | 407 | NULL |
| 10.10.10.210 | 3306 | 1595089660395922 | 163 | NULL |
| 10.10.10.210 | 3306 | 1595089658418224 | 166 | NULL |
| 10.10.10.22 | 3306 | 1595089658395839 | 398 | NULL |
+--------------+------+------------------+----------------------+------------+
One important thing to note here is that monitoring on connect and ping is performed based on the content of the table
mysql_servers, even before this is loaded to RUNTIME. This approach is intentional: in this way it is possible to perform basic health checks before adding the nodes in production. 1
Load servers to runtime
-- load servers to runtime
LOAD MYSQL SERVERS TO RUNTIME;
-- display `mysql_servers`
SELECT hostgroup_id, hostname, port, status
FROM mysql_servers;
result:
+--------------+--------------+------+--------+
| hostgroup_id | hostname | port | status |
+--------------+--------------+------+--------+
| 1 | 10.10.10.22 | 3306 | ONLINE |
| 1 | 10.10.10.210 | 3306 | ONLINE |
+--------------+--------------+------+--------+