一、主从报错 Relay log read failure
问题原因,MySQL主从使用的是kvm虚拟机,物理机超分严重,在负载高的情况下会kill掉占用资源最多的虚拟机,再启动后导致主从失败
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.8 Master_User: rsync Master_Port: 3310 Connect_Retry: 60 Master_Log_File: mysql-bin.004772 Read_Master_Log_Pos: 850868066 Relay_Log_File: mysql-relay-bin.5326697 Relay_Log_Pos: 46202283 Relay_Master_Log_File: mysql-bin.004772 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: 516345810 Relay_Log_Space: 380726348 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: 1 1 row in set (0.00 sec)
二、解决方法
在show slave status\G中找到如下信息,或者查看relay-log.info文件: Relay_Master_Log_File: mysql-bin.004772 # slave库已读取的master的binlog Exec_Master_Log_Pos: 516345810 # 在slave上已经执行的position位置点 停掉slave,以slave已经读取的binlog文件,和已经执行的position为起点,重新设置同步。 mysql> stop slave; Query OK, 0 rows affected (0.08 sec) mysql> change master to master_host='192.168.0.8', master_user='rsync',master_password='d25tgb33edc',master_port=3306,master_log_file='mysql-bin.004772',master_log_pos=516345810;
Query OK, 0 rows affected (1.03 sec) mysql> start slave; Query OK, 0 rows affected (0.41 sec)