Slave's relay log relay-log is corrupted
When slave goes down unexpectedly, it is possible to damage the relay log relay-log, and when synchronous replication is turned on again, an error occurs.
Solution: find the synchronized binlog log and POS point, and then synchronize again so that you can have a new relay log.
Take a look at an example below, which simulates the damage to the relay log. The information you can see is as follows:
Click (here) to collapse or open
Mysql > show slave status\ G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: test
* * 1. Row *
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.10.200
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 220
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000004
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: 1032
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
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 2251
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: 1032
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
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 4adfcd1d-4059-11e7-9532-080027d597f9
Master_Info_File: mysql.slave_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: 170808 11:45:26
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0 where several important parameters are involved:
Slave_IO_Running: accept the information of master's binlog
Master_Log_file: reading binlog log name on master
Read_master_Log_Pos: reading the current binlog log POS point on master.
Slave_SQL_Running: perform write operation
Relay_master_Log_File: synchronizing the binlog log name on master.
Exec_master_Log_Pos: synchronizing the POS point of the current binlog log.
Based on the Relay_master_Log_File parameter value and the Exec_master_Log_Pos parameter value.
Relay_Log_File: mysql-relay-bin.000010
Relay_Log_Pos: 283
Next, start resetting master-slave replication:
Click (here) to collapse or open
Mysql > stop slave
Query OK, 0 rows affected (0.04 sec)
Mysql > change master to master_log_file='mysql-relay-bin.000010',master_log_pos=283
Query OK, 0 rows affected (0.06 sec)
Mysql > start slave
Query OK, 0 rows affected (0. 12 sec) can fix the relay log in this way, but after the MySQL5.5 version, you can add relay_log_recovery=1 to slave's my.cnf configuration file.
