MySQL 5.6: Slave_IO thread stops working

Standard replication breaks for no apparent reason.

mysql> SELECT @@version, @@version_comment;
+---------------+----------------------------------------------------------------------------+
| @@version | @@version_comment |
+---------------+----------------------------------------------------------------------------+
| 5.6.15-56-log | Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061 |
+---------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES LIKE 'wsrep_on';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_on | OFF |
+---------------+-------+
1 row in set (0.00 sec)

Crash safe replications enabled:

master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = 1

Slave is running fine:

# mysql -e "SHOW SLAVE STATUS\G" | grep "Slave"
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

but no connected slaves on MASTER and IO thread disappears from MASTER after some time:

mysql> SELECT * FROM information_schema.processlist WHERE command = 'Binlog Dump';
Empty set (0.10 sec)

mysql> SHOW SLAVE HOSTS;
Empty set (0.00 sec)

Master:

mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 | 568210 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave:

```
# mysql -e "SHOW SLAVE STATUS\G" | grep "Master_Log"
Master_Log_File: mysql-bin.000003
Read_Ma

What you're experiencing can easily occur in a low traffic situation, particularly if the two servers are separated by a firewall or other device that implements stateful packet inspection. (This is a situation that can occur, for example, within Amazon EC2/VPC). The intermediate networking hardware can "forget" about the TCP connection between the servers, because when there's no data being replicated, the connection is left idle.

mysql> STOP SLAVE;
Query OK, 0 rows affected (0.09 sec)

mysql> CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 60;
Query OK, 0 rows affected (0.13 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

When the slave connects to the master, it will request that the master inject a heartbeat message into the binlog stream every 60 seconds, but only when there have been no replication events for that amount of time -- so it has no impact when there's a lot of traffic, but when traffic is light, the heartbeat events will be sent, and the connection will stay alive.

Note that CHANGE MASTER TO is typically a disruptive command that can reset your replication configuration. In this case, if MASTER_HEARTBEAT_PERIOD is the only argument provided, the slave configuration does not get reset.

http://dev.mysql.com/doc/refman/5.6/en/change-master-to.html

Also consider setting the global variable slave_net_timeout to a value shorter than the default, but not less than twice the value you use for the master heartbeat period. This will cause the slave to drop and retry the connection to the master if nothing happens on the replication stream within the configured period of time.

StackExchange Database Administrators Q#71821, answer score: 8