slave的中继日志relay-log损坏

千家信息网最后更新 2026年11月02日slave的中继日志relay-log损坏当slave意外宕机时，有可能会损坏中继日志relay-log，再次开启同步复制时，会出现报错。
解决方法：找到同步的binlog日志和POS点，然后重新进行同步，这样就可以有新的中继日志了。
下边看个案例，模拟了中继日志损坏的情况，查看到的信息如下：

点击(此处)折叠或打开

1. mysql> show slave status \G
   
2. ERROR 2006 (HY000): MySQL server has gone away
   
3. No connection. Trying to reconnect...
   
4. Connection id: 4
   
5. Current database: test
   
6. 
   
7. *************************** 1. row ***************************
   
8. Slave_IO_State: Waiting for master to send event
   
9. Master_Host: 10.10.10.200
   
10. Master_User: repl
    
11. Master_Port: 3306
    
12. Connect_Retry: 60
    
13. Master_Log_File: mysql-bin.000005
    
14. Read_Master_Log_Pos: 220
    
15. Relay_Log_File: mysql-relay-bin.000010
    
16. Relay_Log_Pos: 283
    
17. Relay_Master_Log_File: mysql-bin.000004
    
18. Slave_IO_Running: Yes
    
19. Slave_SQL_Running: No
    
20. Replicate_Do_DB:
    
21. Replicate_Ignore_DB:
    
22. Replicate_Do_Table:
    
23. Replicate_Ignore_Table:
    
24. Replicate_Wild_Do_Table:
    
25. Replicate_Wild_Ignore_Table:
    
26. Last_Errno: 1032
    
27. Last_Error: Could not execute Update_rows event on table test.test01; Can't find record in 'test01', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 310
    
28. Skip_Counter: 0
    
29. Exec_Master_Log_Pos: 120
    
30. Relay_Log_Space: 2251
    
31. Until_Condition: None
    
32. Until_Log_File:
    
33. Until_Log_Pos: 0
    
34. Master_SSL_Allowed: No
    
35. Master_SSL_CA_File:
    
36. Master_SSL_CA_Path:
    
37. Master_SSL_Cert:
    
38. Master_SSL_Cipher:
    
39. Master_SSL_Key:
    
40. Seconds_Behind_Master: NULL
    
41. Master_SSL_Verify_Server_Cert: No
    
42. Last_IO_Errno: 0
    
43. Last_IO_Error:
    
44. Last_SQL_Errno: 1032
    
45. Last_SQL_Error: Could not execute Update_rows event on table test.test01; Can't find record in 'test01', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000004, end_log_pos 310
    
46. Replicate_Ignore_Server_Ids:
    
47. Master_Server_Id: 1
    
48. Master_UUID: 4adfcd1d-4059-11e7-9532-080027d597f9
    
49. Master_Info_File: mysql.slave_master_info
    
50. SQL_Delay: 0
    
51. SQL_Remaining_Delay: NULL
    
52. Slave_SQL_Running_State:
    
53. Master_Retry_Count: 86400
    
54. Master_Bind:
    
55. Last_IO_Error_Timestamp:
    
56. Last_SQL_Error_Timestamp: 170808 11:45:26
    
57. Master_SSL_Crl:
    
58. Master_SSL_Crlpath:
    
59. Retrieved_Gtid_Set:
    
60. Executed_Gtid_Set:
    
61. Auto_Position: 0

其中，涉及几个重要的参数：
slave_IO_Running:接受master的binlog的信息
master_Log_file:正在读取master上binlog日志名
Read_master_Log_Pos:正在读取master上当前binlog日志POS点。
slave_SQL_Running:执行写操作
Relay_master_Log_File:正在同步master上的binlog日志名。
Exec_master_Log_Pos:正在同步当前binlog日志的POS点。
以Relay_master_Log_File参数值和Exec_master_Log_Pos参数值为基准。
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 283
接下来开始重置主从复制：

点击(此处)折叠或打开

1. mysql> stop slave;
   
2. Query OK, 0 rows affected (0.04 sec)
   
3. 
   
4. mysql> change master to master_log_file='mysql-relay-bin.000010',master_log_pos=283;
   
5. Query OK, 0 rows affected (0.06 sec)
   
6. 
   
7. mysql> start slave;
   
8. Query OK, 0 rows affected (0.12 sec)

通过这种方法可以修复中继日志，但是在MySQL5.5版本之后，在slave的my.cnf配置文件中加入relay_log_recovery=1就可以了。