Monday, March 23, 2015

MySQL - Replication It Not Moving

We have a MySQL master - master setup. serverA and serverB replicates each other. Today something is happened to serverB and we have to rebuild serverB, and also rebuild MySQL DB from serverA. After MySQL rebuild, replication on serverB is syncing and everything looks fine. Strange thing happend after replication on serverB are caught, I noticed that replcation stopped moving after it is caught. This means:

Exec_Master_Log_Pos: 251635040
    Relay_Log_Space: 251635596

are not updating on serverB. This effectively means that no updates are replicated from the master, even though the everything appears to be alright. I verified that both slave IO and slave SQL are running fine:

 Slave_IO_Running: Yes
Slave_SQL_Running: Yes

If you run "STOP SLAVE; START SLAVE" or "STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;" you will see "Seconds_Behind_Master" is not 0.

After some digging, I found that the reason for this strange behaviour is the default setting (3600s) of the "slave_net_timeout" variable.

mysql> select @@slave_net_timeout;
+---------------------+
| @@slave_net_timeout |
+---------------------+
|                3600 |
+---------------------+

1 row in set (0.00 sec)

Once the slave has connected to the master it waits for data to arrive:

Slave_IO_State: Waiting for master to send event

If it does not receive any data within "slave_net_timeout" seconds it considers the connection broken and reconnects again. To prevent frequent reconnects when there is little activity on the master this setting is defaults to 3600 seconds.

In our case, writes request on the master are pretty heavy, 3600 seconds without any updates will let the slave far behind the master. With the settings of "slave_net_timeout" change to 60 seconds, updates from master are caught in time.

mysql> set global slave_net_timeout=60;
mysql> select @@slave_net_timeout;
+---------------------+
| @@slave_net_timeout |
+---------------------+
|                  60 |
+---------------------+

1 row in set (0.00 sec)

Don't forget to update your my.cnf file to make the change permanent

No comments: