背景
29和30两个MySQL实例互为主备,但是29作为从库同步来自于30的数据出现异常,具体如下:
29库执行 show slave status\G
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: xxx.xxx.xxx.30
Master_User: repl30
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001370
Read_Master_Log_Pos: 4
Relay_Log_File: relay-bin-30to29.002754
Relay_Log_Pos: 241
Relay_Master_Log_File: mysql-bin.001370
Slave_IO_Running: No
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: 4
Relay_Log_Space: 209711831
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event '' at 4, the last event read from '/data/mysql/logs/binlog/mysql-bin.001369' at 209715774, the last byte read from '/data/mysql/logs/binlog/mysql-bin.001369' at 209715774.'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 30
Master_UUID: 3b37ee51-101a-11ea-9cad-005056b8c86d
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 201108 14:56:54
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3b37ee51-101a-11ea-9cad-005056b8c86d:1-8651971
Executed_Gtid_Set: 3b37ee51-101a-11ea-9cad-005056b8c86d:1-8651971,
8e844528-1017-11ea-a305-005056b89215:1-45545273
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: 30to29
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
30库执行show slave status\G
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: xxx.xxx.xxx.29
Master_User: repl29
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001373
Read_Master_Log_Pos: 190639277
Relay_Log_File: relay-bin-29to30.003903
Relay_Log_Pos: 4891
Relay_Master_Log_File: mysql-bin.001373
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: 190639277
Relay_Log_Space: 5186
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
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: 29
Master_UUID: 8e844528-1017-11ea-a305-005056b89215
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 more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8e844528-1017-11ea-a305-005056b89215:22123711-45545273
Executed_Gtid_Set: 3b37ee51-101a-11ea-9cad-005056b8c86d:1-8723373,
8e844528-1017-11ea-a305-005056b89215:1-45545273
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: 29to30
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
通过观察参数发现29库在读取30库’/data/mysql/logs/binlog/mysql-bin.001369’ at 209715774的文件时,失败,mysql-bin.001369文件在30库又存在,目前不知道是什么原因
解决方法
由于出错的是30to29的通道,简单处理:重启30to29通道:
stop slave for channel '30to29';
start slave for channel '30to29'
然后就恢复了,具体是什么原因导致的,目前还不清楚,有知道的盆友,希望可以不吝告知