Mysql Master-slave Replication configuration heartbeat function Introduction _mysql

In MySQL master-slave replication, sometimes encountered such a failure: on the Slave slave_io_running and slave_sql_running are yes,slave_sql_running_state display Slave has read All relay log; Waiting for the slave I/O thread to update it, it looks like the state is normal, but actually lags behind the main, Master_log_file and Read_master_log_pos is not the actual owner of the latest location. One of the possible binlog dump threads on Master is dead. But sometimes it's perfectly normal to check in Master. And how did the Slave delay cause it?

In the MySQL replication protocol, when the Slave sends a com_binlog_dump command, it is entirely up to master to push the data, and master and Slave no longer need to interact. If Master is not updated, there will be no data flow, and Slave will not receive any packets. But if, for some reason, Master is unable to send data to Slave, such as a network failure or other reason for the loss of TCP connections on master, Slave has no chance to be notified because of the characteristics of the TCP protocol, so there is no way to know if the data was not received because master There is no update or because of the fault.

Fortunately, MySQL 5.5 began to add a copy of the heartbeat function.

Such as

Copy Code code as follows:

Stop slave;
Change Master to Master_heartbeat_period = 10;
Set global slave_net_timeout = 25;
Start slave;

Will let Master send a heartbeat packet every 10 seconds when there is no data. So Slave can tell if Master is still normal. Slave_net_timeout is to set the network timeout after the data is confiscated, and then the IO thread of slave will reconnect to Master. Combining these two settings avoids replication delays due to network problems. The Master_heartbeat_period unit is a second, which can be a decimal, such as 10.5. The highest precision is 1 milliseconds.

The default for Slave_net_timeout is 3600, which is an hour. In other words, in the previous case, the Slave will be delayed for 1 hours before attempting to connect again. When the master_heartbeat_period is not set, setting the Slave_net_timeout very short will cause Master to be heavily connected when there is no data update.

Oddly enough, the current Master_heartbeat_period value cannot be viewed through show slave status, but is viewed using show status like ' Slave_heartbeat_period '. In addition, the state variable slave_last_heartbeat represents the last time a heartbeat was received, Slave_received_heartbeats represents the total number of heartbeats received.

Such as:

Copy Code code as follows:

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)