Master-Slave replication is broken because of corrupted slave relay log on Slave server
It does not apply to Master-Master replication. We are not taking responsibility for data lost, caused by actions described in this manual.
Error shown in Slave server should look like:
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 111.111.111.111 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000474 Read_Master_Log_Pos: 228856355 Relay_Log_File: slave-relay.001158 Relay_Log_Pos: 949325382 Relay_Master_Log_File: mysql-bin.000472 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: mor 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: 949325236 Relay_Log_Space: 2376273076 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: 20 1 row in set (0.00 sec)
Make sure that failure caused not by corruption of master binlog in Master server. On Main server use :
mysqlbinlog /var/lib/mysql/mysql-bin.000472 >/tmp/mysqlbinlog.txt
Open /tmp/mysqlbinlog.txt and search for certain position (949325236 in example above). Check text on lines above and below that position. If you can see distorted text or cannot find that position at all, problem is caused by master log problem and replication needs to be completely rebuilt.
Use same method to check Slave relay log on Slave server:
mysqlbinlog /var/lib/mysql/slave-relay.001158 >/tmp/mysqlbinlog.txt
You should find distorted text or get error that binlog is corrupted while running the command.
If it happens follow this manual.
On slave server where you see error:
Copy the output "show slave status\G".
Also check content of /var/lib/mysql/master.info . It will show what mysql user and password is used for connection to Master server.
Then:
mysql stop slave; reset slave all; CHANGE MASTER TO MASTER_HOST='111.111.111.111', MASTER_USER='mor', MASTER_PASSWORD='yourpassword', MASTER_LOG_FILE='mysql-bin.000472', MASTER_LOG_POS=949325236; start slave;
IP should be changed to Master server IP, MASTER_USER and MASTER_PASSWORD suppose to be same as it was on master.info.
MASTER_LOG_FILE should be taken from "show slave status\G" output "Relay_Master_Log_File" line.
MASTER_LOG_POS should be taken from "show slave status\G" output "Exec_Master_Log_Pos" line.
If all when fine, "show slave status\G" should show no errors and "Seconds_Behind_Master" line should indicate that data is flowing from Main server.