What catches my attention is the word 'NETWORK'.
Communication between Master and Slave is implemented as bidirectional.
According to the MySQL Documentation on Replication
Binlog dump thread. The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.
The binary log dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.
Slave I/O thread. When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.
The slave I/O thread reads the updates that the master's Binlog Dump thread sends (see previous item) and copies them to local files that comprise the slave's relay log.
The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.
Given this description of the I/O aspect of Replication, what could you look for ???
The connection between Master and Slave requires that the firewall be open. Unfortunately, I have seen occasions where the firewall was open on the Master and a Slave would connect as usual. The Slave would have the I/O thread show up in the processlist like nothing was wrong. The Master would do the same. All of a sudden, 60 seconds later, the I/O thread disappears from the processlist of the Master, but remains visible on the Slave.
Given that scenario (that I eyewitnessed between two Amazon EC2 servers in two different AZs (Availability Zones)), the solution back then was to check the Security Groups and get port 3306 open in the Slave's AZ.
MySQL has settings for timing out network connections
From the MySQL Docs:
- rpl_semi_sync_master_timeout : A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds). This variable is available only if the master-side semisynchronous replication plugin is installed.
- net_read_timeout : The number of seconds to wait for more data from a connection before aborting the read. When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. See also slave_net_timeout.
- net_write_timeout : The number of seconds to wait for a block to be written to a connection before aborting the write. See also net_read_timeout.
- 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. The first retry occurs immediately after the timeout. The interval between retries is controlled by the MASTER_CONNECT_RETRY option for the CHANGE MASTER TO statement, and the number of reconnection attempts is limited by the --master-retry-count option. Prior to MySQL 5.7.7, the default was 3600 seconds (one hour). In MySQL 5.7.7 and later the default is 60 seconds (one minute).
Why talk about the network like this ??? You can be victimized in the form of data drift. Back on
Jun 17, 2014, I answered the post I have been tasked with Mysql Master-Master replication?. I briefly mentioned the network as an unsung hero in data drift:
- You must make sure the network traffic is not inundated, your switches are fine, any crossover cabling to bypass the network is not defective, there are no dropped packets,.
- In terms of MySQL, the Replication I/O thread is dependent on the network. You must make sure mysqld can regularly heartbeat its master. Perhaps semisync replication could be installed and configured for this.
YOUR ACTUAL QUESTION
STOP SLAVE; and
START SLAVE does not find the root cause but does indeed solve the problem at hand. How ??? All this does is disconnect both the I/O and SQL Threads and then reconnect from scratch.
You could also have done
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
which would also do just fine, especially if the SQL thread is busy and you do not want interrupt it.
You will need to check the connection between the Master and Slave for dropped packets.
If your monitoring has the same time granularity as the MySQL timeout values, you have nothing to alert you when it does happen. You would have to poll MySQL more frequently. As an alternative, you could probably create some kind of SNMP setup to monitor MySQL so if SNMP info from does not update in a timely manner you could detect MySQL being down or not responding without ever connecting to MySQL.
My answer may not have fully defined the root cause, but I do have two suggestions:
Look into your max_allowed_packet setting. Many times in the DBA StackExchange I have affectionately called the MySQL Packet
the Silent Killer of DB Connections. The I/O thread is just as much as DB Connection as any other. I would make sure max_allowed_packet is always set to 1073741824 (which is 1G).
You could manually set the I/O Thread Heartbeat. How ?
According to the MySQL 5.5 Documentation for
CHANGE MASTER TO
MASTER_HEARTBEAT_PERIOD sets the interval in seconds between replication heartbeats. Whenever the master's binary log is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution in milliseconds; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binary log file for a period longer than interval.
Setting interval to 0 disables heartbeats altogether. The default value for interval is equal to the value of slave_net_timeout divided by 2.
Setting @@global.slave_net_timeout to a value less than that of the current heartbeat interval results in a warning being issued. The effect of issuing RESET SLAVE on the heartbeat interval is to reset it to the default value.
Based on these paragraphs and the default value for slave_net_timeout (60 Seconds), it appears that the I/O thread should heartbeat every 30 seconds. You could change the heartbeat period to 10 seconds like this:
CHANGE MASTER TO MASTER_HEARTBEAT_PERIOD = 10;