MySQL 5.6: Slave_IO thread stops working while replicating

mariadbMySQLreplication

Standard replication breaks for no apparent reason.

MariaDB [(none)]> SELECT @@version, @@version_comment;
+----------------+-------------------+
| @@version      | @@version_comment |
+----------------+-------------------+
| 5.5.52-MariaDB | MariaDB Server    |
+----------------+-------------------+
1 row in set (0.00 sec)

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



Id  | User        | Host                        | db       | Command | Time | State                                                                       | Info             | Progress |
+-----+-------------+-----------------------------+----------+---------+------+-----------------------------------------------------------------------------+------------------+----------+
|   1 | system user |                             | NULL     | Connect |  438 | Waiting for master to send event                                            | NULL             |    0.000 |
|   2 | system user |                             | NULL     | Connect |  120 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |    0.000 |
|   3 | u271869     | S0280I2.ba.ad.ssa.gov:59117 | mysql    | Sleep   |    2 |                                                                             | NULL             |    0.000 |
|   4 | nagiosxi    | localhost                   | nagiosxi | Sleep   |  176 |                                                                             | NULL             |    0.000 |


mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: $IP
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000007
          Read_Master_Log_Pos: 433
               Relay_Log_File: mysql-relay.000265
                Relay_Log_Pos: 283
        Relay_Master_Log_File: mysql-bin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 433
              Relay_Log_Space: 710
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /etc/mysql/ca-cert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /etc/mysql/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /etc/mysql/client-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1124732721
                  Master_UUID: 4412a455-e1d0-11e3-835a-5254007fe78d
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: /etc/mysql/ca-cert.pem
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)
mysql replication
shareedit
edited Jul 18 '14 at 13:16

Best Answer

Everything looks fine, what is making you think replication breaks? Is there any active writes being made to the master? If you did show master status\G on the master, as well as show processlist; are you seeing any active queries or any queries that are locked?

If you posted some of the binlog or taken a look at it, it may help you see, if replication is being broken, what exact query is breaking it. You can access the binlog by doing mysqlbinlog binlog.file

For more reference on binlog