When a MySQL Slave connects to the master, it requests a stream of the binary log, and the master autonomously sends binlog events as often as they occur, with no acknowledgement required from the slave unless you're using semisynchronous replication.
The slave doesn't originate any traffic, other than low level acknowledgements handled by the TCP stack. An interruption in connectivity (at various layers of the stack, not limited to an unplugged cable) can cause the connection to break in several ways, including the master's TCP stack tearing down the connection due to timeouts or an ICMP unreachable message or a stateful firewall between the machines "forgetting" about the TCP session and silently dropping the subsequent packets, with the slave quietly sitting and waiting for the next packet to come along from the master.
The solution here is the global variable slave_net_timeout
.
The number of seconds to wait for more data from the master before the slave considers the connection broken, aborts the read, and tries to reconnect.
This is configured on the slave. When the slave connects to the master, before requesting the binlog stream, it asks the master to send heartbeat events, which are formatted like binlog events and streamed as if they were the next event in the master's binlog, but don't actually increment the binlog position counters. They are essentially zero overhead in normal operation, because they aren't sent unless the master has generated no new binlog events for one-half of the slave's slave_net_timeout
setting (default; or another value you can configure during CHANGE MASTER TO
), so heartbeat events are only actually generated when traffic is very light... so there isn't any real harm as far as I can tell in setting this value as low as just a few seconds.
If the slave sees the timeout expire, it will close its connection and and reconnect to the master.
On the remote chance that the master doesn't realize that the slave has gone away, when the slave reconnects, the master will close the original connection, because a MySQL master, when accepting a new slave connection, checks whether another slave with the same server_id
is already connected, and if so, drops the original connection. This is, incidentally, the reason why two slaves configured with the same server_id
(an unsupported configuration) can't successfully stay connected to the same master -- as soon as one of them connects, it causes the other one to be bumped, and a cycle ensues with each slave forcing the other one's connection to be dropped.
Setting this variable to a suitably-low value in my.cnf and restarting the slave should remedy this problem.