MySQL主从同步异常之Relay log异常1594
一、现象描述
现象：MySQL从库复制出现1594错误，即relay log（中继）日志读取失败，无法解析relay log日志事件条目。
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.23.53.29 Master_User: ucloudbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.004513 Read_Master_Log_Pos: 100630843 Relay_Log_File: mysql-relay.009141 Relay_Log_Pos: 1028042575 Relay_Master_Log_File: mysql-bin.004486 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1594 Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Skip_Counter: 0 Exec_Master_Log_Pos: 1028042442 Relay_Log_Space: 29115692374 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: 0 Last_IO_Error: Last_SQL_Errno: 1594 Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Replicate_Ignore_Server_Ids: Master_Server_Id: 169293085 Master_UUID: e316ad62-50e0-11eb-af02-525400bce724 Master_Info_File: /opt/udb/instance/mysql-5.7/2bc88e93-7768-4635-8ea2-85704f60d000/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: 210721 19:03:41 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: e316ad62-50e0-11eb-af02-525400bce724:1837734240-1884726145 Executed_Gtid_Set: 3b197e7e-40de-11eb-96fd-525400bce724:1-36137013, 4dc9d1f6-8df6-11eb-b2ba-525400979c7e:1-69154545, 9397b24e-ca6b-11ea-a8d6-525400979c7e:1-86787951, 93cd61ee-ca6b-11ea-a87b-525400bce724:1-217801788, e316ad62-50e0-11eb-af02-525400bce724:1-1877437576, ebc7cbbb-257a-11eb-824e-525400979c7e:1-814457941 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
二、问题分析
由于relay log日志文件受损，无法继续通过受损的relay log 回放SQL语句，那么解决思路就是从主库重新拉取binlog，恢复复制线程。
（1）从库通过show slave status\G获取关键指标
Relay_Master_Log_File: mysql-bin.004486 Exec_Master_Log_Pos: 1028042442
【注】Master_Log_File是io线程的位置，Relay_Master_Log_File 是sql线程的位置，多数情况下是，io线程位置与sql线程位置并不一致，所以RESET SLAVE后需要使用Relay_Master_Log_File 位置。
（2）重新启动复制
mysql> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> RESET SLAVE; Query OK, 0 rows affected (0.72 sec) mysql> CHANGE MASTER TO master_log_file='mysql-bin.004486', master_log_pos=1028042442; Query OK, 0 rows affected (0.01 sec) mysql> START SLAVE; Query OK, 0 rows affected (0.00 sec)
（3）查看复制状态
mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Master_Host: 10.23.53.29 Master_User: ucloudbackup Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.004486 Read_Master_Log_Pos: 1028042442 Relay_Log_File: mysql-relay.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.004486 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: 1028042442 Relay_Log_Space: 154 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: Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 169293085 Master_UUID: e316ad62-50e0-11eb-af02-525400bce724 Master_Info_File: /opt/udb/instance/mysql-5.7/2bc88e93-7768-4635-8ea2-85704f60d000/data/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: 210721 19:09:05 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 3b197e7e-40de-11eb-96fd-525400bce724:1-36137013, 4dc9d1f6-8df6-11eb-b2ba-525400979c7e:1-69154545, 9397b24e-ca6b-11ea-a8d6-525400979c7e:1-86787951, 93cd61ee-ca6b-11ea-a87b-525400bce724:1-217801788, e316ad62-50e0-11eb-af02-525400bce724:1-1877437576, ebc7cbbb-257a-11eb-824e-525400979c7e:1-814457941 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
【注】通过重新拉取binlog后，主从同步恢复正常。
作者：UStarGao
链接：https://www.starcto.com/mysql/192.html
来源：STARCTO
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
UCloud云平台推荐
随便看看
- 2021-02-21Docker自定义镜像操作
- 2021-11-26MongoDB副本集设置节点优先级priority
- 2022-07-21Linux 磁盘性能指标之磁盘I/O观测与进程I/O观测
- 2021-03-27初识Redis数据库
- 2022-06-22MySQL如何创建全文索引
