ProxySQL is an open-source MySQL proxy server, meaning it serves as an intermediary between a MySQL server and the applications that access its databases. ProxySQL can improve performance by distributing traffic among a pool of multiple database servers.
Consider 2 slaves are routed under Proxysql , In any one of the slave, if the replication is broken, we could still see the traffic routing to the broken replication slave. We can make Proxy to not send traffic to broken replication slave, by setting appropriate value to the variable mysql-monitor_slave_lag_when_null
mysql-monitor_slave_lag_when_null
The default value for this variable is 60 sec. Whenever 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 to either shun or keep online a server where replication is broken/stopped.
As we all know, whenever replication lag reaches higher than the value max_replication_lag variable, Proxysql will stop sending traffic to that particular slave. Similarly, whenever replication is broken ,Proxysql assumes the value of mysql-monitor_slave_lag_when_null variable as current replication lag.
So to make ProxySQL to not send traffic to broken replication slave, we need to set value mysql-monitor_slave_lag_when_null higher than max_replication_lag. Let’s see the example below.
The max_replication_lag value is set as 3000, so whenever lag exceeds this value, ProxySQL will stop sending traffic to the respective slave.
Now I’m setting the value for mysql-monitor_slave_lag_when_null = 3100, higher than the max_replication_lag. So when replication is broken for the slave, Proxysql considers mysql-monitor_slave_lag_when_null as the current replication lag, and it compares with max_replication lag, as the value exceeds 3000, ProxySQL will halt sending traffic to the broken reader (replica).
ProxySQL>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.00 sec)
ProxySQL>UPDATE global_variables SET variable_value=3100 WHERE variable_name='mysql-monitor_slave_lag_when_null';
Query OK, 1 row affected (0.00 sec)
ProxySQL>load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
ProxySQL>save mysql variables to disk;
Query OK, 140 rows affected (0.01 sec)
ProxySQL>select * from runtime_global_variables where variable_name='mysql-monitor_slave_lag_when_null';
+-----------------------------------+----------------+
| variable_name | variable_value |
+-----------------------------------+----------------+
| mysql-monitor_slave_lag_when_null | 3100 |
+-----------------------------------+----------------+
1 row in set (0.01 sec)
Now let us pause the replication on 10.0.0.2 (Reader 2)
As soon as I stop the replica, replication check returns that Seconds_Behind_Master=NULL, and it checks for the value of mysql-monitor_slave_lag_when_null. As we have set mysql-monitor_slave_lag_when_null =3100, Proxysql assumes this as current replication lag which exceeds the max_replication_lag(3000 secs), so it will stop sending the traffic to the broken replication slave.In slave 10.0.0.2, We could see no traffic is being routed by Proxysql as soon as we stop the slave.
mysql> stop replica;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+--------+------------------+-------------------------------------+--------------------------+---------+------+----------+------------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined |
+--------+------------------+-------------------------------------+--------------------------+---------+------+----------+------------------+-----------+---------------+
| 234937 | root | localhost | NULL | Query | 0 | starting | show processlist | 0 | +--------+------------------+-------------------------------------+--------------------------+---------+------+----------+------------------+-----------+---------------+
61 rows in set (0.00 sec)
ProxySQL>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 | 10.0.0.1 | 3306 | 0 | ONLINE | 1 | 0 | 60000 | 3000 | 0 | 0 | |
| 1 | 10.0.0.2 | 3306 | 0 | SHUNNED | 1 | 0 | 60000 | 3000 | 0 | 0 | |
+--------------+---------------+------+-----------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
As soon as the replication is started, Proxysql will start sending traffic to the replica.
To know more about ProxySQL read-writes splits, please check our blogs and we have written a series of blog on ProxySQL
Unleash the Full Potential of Your MySQL Databases! Explore Mydbops' open-source database management solutions and services. We offer expert guidance and tools to streamline your database operations. Visit our website or contact us today!
%20vs%20systemctl%20stop%20(2).png)
.png)
.svg)
