Mysql replication heartbeat _ MySQL-mysql tutorial

Mysql replication heartbeat sometimes encounters such a fault during MySQL master-Slave replication: Slave_IO_Running and Slave_ SQL _Running on Slave are both Yes, and Slave_ SQL _Running_State displays Slave has read all relay log; waiting for the slave I/O thread to update it, it seems that the status is normal, but it actually lags behind the master, Master_Log_File and Read_Master_Log_Pos are not the latest location on the actual master. One possibility is that the binlog dump thread on the Master instance is suspended. But sometimes, the check on the Master is completely normal. What is the cause of Slave delay?

In the MySQL replication protocol, after the Slave sends a COM_BINLOG_DUMP command, the Master will push data completely, and the Master and Slave do not need to interact with each other. If the Master node is not updated, there will be no data streams and the Slave will not receive any data packets. However, if, for some reason, the Master node cannot send data to the Slave, for example, if a network failure occurs or another cause causes the loss of TCP connections on the Master node, due to the characteristics of the TCP protocol, the server load balancer has no chance to get a notification, so it cannot know whether the data is not received because the Master has not been updated yet or because of a fault.

Fortunately, MySQL 5.5 has added a replication heartbeat function.

For example

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

The Master will send a Heartbeat packet every 10 seconds when there is no data. In this way, Slave can know whether the Master is still normal. Slave_net_timeout is set to the time when the network times out after the data is not received, and the Slave IO thread will reconnect to the Master. Combining these two settings can avoid replication delays due to network problems. Master_heartbeat_period is measured in seconds. it can contain decimal places, for example, 10.5. The highest precision is 1 ms.

The default value of slave_net_timeout is 3600, that is, one hour. That is to say, in the previous circumstances, Slave will try to reconnect after one hour delay. When master_heartbeat_period is not set, setting slave_net_timeout very short will cause heavy time-frequency connection of the Master without data update.

It is strange that the current master_heartbeat_period value cannot be viewed through show slave status, but you need to view it using show status like 'slave _ heartbeat_period. In addition, the state variable Slave_last_heartbeat indicates the last time the heartbeat was received, and Slave_received_heartbeats indicates the total number of heartbeats received.

For example:

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)