ProxySQL is a high-performance SQL proxy, which runs as a daemon watched by a monitoring process. The process monitors the daemon and restarts it in case of a crash to minimize downtime.
The daemon accepts incoming traffic from MySQL clients and forwards it to backend MySQL servers.
The proxy is designed to run continuously without needing to be restarted. Most configurations can be done at runtime using queries similar to SQL statements in the ProxySQL admin interface. These include runtime parameters, server grouping, and traffic-related settings.
Here, we will consider ProxySQL configured for async replication. Even when a replica is broken/stopped, ProxySQL still routes connections to replicas. It can be overcome by setting the appropriate value for mysql-monitor_slave_lag_when_null
mysql-monitor_slave_lag_when_null
When the replication check returns that Seconds_Behind_Master=NULL, the value of mysql-monitor_slave_lag_when_null (in seconds) is assumed to be the current replication lag. This allows us to either shun or keep online a server where replication is broken/stopped.
ProxSQL stops routing connections to replicas whenever the lag value is greater than the max_replication_lag value defined in ProxySQLl. When a replica is broken/stopped, the replication check will return that Seconds_Behind_Master=NULL. At this position, the value of mysql-monitor_slave_lag_when_null (in seconds) is assumed to be the current replication lag.
MySQL connections to broken/stopped replicas can be restricted by setting the value of mysql-monitor_slave_lag_when_null greater than max_replication_lag. Let’s see in the scenario below.
max_replication_lag is set to 600. ProxSQL stops routing connections to replicas when the lag is greater than 600.
1 2 3 4 5 6 7 8 9 | ProxySQLAdmin> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+--------+----- ---+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 127.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 | 127.0.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.002 sec) |
Let’s stop replication on replica 127.0.0.2:
1 2 | Replica_127.0.0.2>stop replica; Query OK, 0 rows affected (0.01 sec) |
Still, ProxySQL routes connections to replica, though replication is stopped.
1 2 3 4 5 6 7 8 | ProxySQLAdmin> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 127.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 | 127.0.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.002 sec) |
Now consider setting mysql-monitor_slave_lag_when_null = 610 as it is greater than the current value of max_replication_lag.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | ProxySQLAdmin> select * from runtime_global_variables where variable_name='mysql-monitor_slave_lag_when_null'; +-----------------------------------+----------------+ | variable_name | variable_value | +-----------------------------------+----------------+ | mysql-monitor_slave_lag_when_null | 60 | +-----------------------------------+----------------+ 1 row in set (0.002 sec) ProxySQLAdmin> UPDATE global_variables SET variable_value=610 WHERE variable_name='mysql-monitor_slave_lag_when_null'; Query OK, 1 row affected (0.002 sec) ProxySQLAdmin> load mysql variables to runtime; Query OK, 0 rows affected (0.002 sec) ProxySQLAdmin> save mysql variables to disk; Query OK, 158 rows affected (0.006 sec) ProxySQLAdmin> select * from runtime_global_variables where variable_name='mysql-monitor_slave_lag_when_null'; +-----------------------------------+----------------+ | variable_name | variable_value | +-----------------------------------+----------------+ | mysql-monitor_slave_lag_when_null | 610 | +-----------------------------------+----------------+ 1 row in set (0.002 sec) |
Let’s stop replication once again on replica 127.0.0.2:
1 2 | Replica_127.0.0.2>stop replica; Query OK, 0 rows affected (0.01 sec) |
As soon as replication is stopped on replica 127.0.0.2, the replication check returns that Seconds_Behind_Master=NULL. As mysql-monitor_slave_lag_when_null is set to 610, it assumes replication lag is greater than 600 and shuns the replica — thus it stops connections to the replica.
1 2 3 4 5 6 7 8 | ProxySQLAdmin> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 127.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 | 127.0.0.2 | 3306 | 0 | SHUNNED | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.002 sec) |
When replication is started on replica 127.0.0.2:
1 2 | Replica_127.0.0.2>start replica; Query OK, 0 rows affected (0.01 sec) |
ProxySQL will start routing connections to replica.
1 2 3 4 5 6 7 8 | ProxySQLAdmin> select * from runtime_mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | 127.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | | 2 | 127.0.0.2 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 600 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2 rows in set (0.002 sec) |
Conclusion
By setting the appropriate value for mysql-monitor_slave_lag_when_null in ProxySQL, MySQL connections can be restricted to broken/stopped replicas.
Related links:
How to Install ProxySQL From the Percona Repository
Install Percona Server for MySQL
How to set up a replica for replication in 6 simple steps with Percona XtraBackup
what if i have more than 1 slave and want to move traffic from one slave to another slave if one is broken.
Hi Sid,
Based on your host group & query rule configuration, ProxySQL automatically routes connections to available replica nodes.