The MySQL master slave replication configuration heartbeat feature is described

In the master-slave replication of MySQL, we sometimes encounter such failures: on Slave, Slave_IO_Running and Slave_SQL_Running are both Yes, and Slave_SQL_Running_State shows Slave has all relay log. waiting for the slave I/O thread update it Master_Log_File and Read_Master_Log_Pos are not the latest positions on the actual host. One possibility is that the binlog dump thread on Master hangs. But sometimes it's perfectly normal to check on Master. What about the delay in Slave?

In the replication protocol of MySQL, after Slave sends one COM_BINLOG_DUMP command, Master pushes data completely, and there is no need for interaction between Master and Slave. If Master is not updated, there will be no data flow and Slave will not receive any packets. However, if Master is unable to send data to Slave for some reason, such as a network failure or other reasons caused the loss of the TCP connection on Master, Slave has no chance to be notified due to the nature of TCP protocol, so there is no way to know whether the data is not received because Master was not updated or due to a failure.

The good news is that MySQL 5.5 started adding 1 replication heartbeat.

Such as

stop slave;
change master to master_heartbeat_period = 10;
set global slave_net_timeout = 25;
start slave;

This will tell Master to send a heartbeat packet every 10 seconds when no data is available. So Slave will know if Master is still working. slave_net_timeout sets how long it takes for the network to timeout after receiving no data, after which the IO thread of Slave will reconnect to Master. Combine these two Settings to avoid replication delays due to network problems. master_heartbea

riod is in seconds and can be a decimal, as in 10.5. The highest accuracy is 1 millisecond.

The default for slave_net_timeout is 3600, which is 1 hour. That is, in the previous case, Slave would not attempt to reconnect until an hour later. When master_heartbea

riod is not set, setting slave_net_timeout too short will cause Master to reconnect frequently without data updates.

Oddly enough, the current master_heartbea

riod value cannot be viewed with show slave status, but with show status like 'Slave_heartbea

riod'. In addition, the status variable Slave_last_heartbeat represents the time of the last heartbeat received and Slave_received_heartbeats represents the total number of heartbeats received.

Such as:

mysql> show status like 'slave%';
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| Slave_heartbeat_period     | 5.000               |
| Slave_last_heartbeat       | 2014-05-08 11:48:57 |
| Slave_open_temp_tables     | 0                   |
| Slave_received_heartbeats  | 1645                |
| Slave_retried_transactions | 0                   |
| Slave_running              | ON                  |
+----------------------------+---------------------+
6 rows in set (0.00 sec)