参数设置
在从库中将relay_log_recovery不设置或者设置为off，如果当从库意外宕机后，同时从库的relay log也一起损坏了，从库会丢失那些没有应用的日志，主从会不一致。
在从库中将relay_log_recovery设置为on，假如果碰到上面的情形，从库会自动放弃所有未执行的relay log，重新生成一个relay log，并将从库的io线程的position重新指向新的relay log。并将sql线程的position退回到跟io线程的position保持一致，重新开始同步，这样在从库中事务不会丢失。这个参数建议开启。
当relay_log_recovery不设置或者设置为off时
mysql> show variables like 'relay_log_recovery'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | relay_log_recovery | OFF | +--------------------+-------+ 1 row in set (0.00 sec) mysql> stop slave sql_thread; Query OK, 0 rows affected (0.01 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: root Master_Port: 13306 Connect_Retry: 60 Master_Log_File: monitor.000012 Read_Master_Log_Pos: 740643253 Relay_Log_File: scutech-relay-bin.000038 Relay_Log_Pos: 5437008 Relay_Master_Log_File: monitor.000012 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: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 739135472 Relay_Log_Space: 6945040 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: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 123456789 Master_UUID: 41dbce5e-a478-11ea-b751-fa163e0ec694 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: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 41dbce5e-a478-11ea-b751-fa163e0ec694:6705940-6728354 Executed_Gtid_Set: 41dbce5e-a478-11ea-b751-fa163e0ec694:1-6727119 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.00 sec) mysql>
重新启动后再看slave的状态
mysql> restart; Query OK, 0 rows affected (0.02 sec) mysql> show slave status \G ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 31 Current database: *** NONE *** *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: root Master_Port: 13306 Connect_Retry: 60 Master_Log_File: monitor.000012 Read_Master_Log_Pos: 742720669 Relay_Log_File: scutech-relay-bin.000038 Relay_Log_Pos: 4 Relay_Master_Log_File: monitor.000012 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: 739135472 Relay_Log_Space: 3586096 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 123456789 Master_UUID: 41dbce5e-a478-11ea-b751-fa163e0ec694 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: waiting for handler commit Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 41dbce5e-a478-11ea-b751-fa163e0ec694:6705940-6722726:6727120-6729968 Executed_Gtid_Set: 41dbce5e-a478-11ea-b751-fa163e0ec694:1-6727119 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.07 sec) mysql>
Retrieved_Gtid_Set这个状态中间用gap，一段gtid丢了！
当relay_log_recovery设置为1时
把参数改成1后，接着上面的重新启动mysqld：
mysql> restart; Query OK, 0 rows affected (0.05 sec) mysql> show slave status \G ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 41 Current database: *** NONE *** *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: root Master_Port: 13306 Connect_Retry: 60 Master_Log_File: monitor.000012 Read_Master_Log_Pos: 755415674 Relay_Log_File: scutech-relay-bin.000041 Relay_Log_Pos: 16280614 Relay_Master_Log_File: monitor.000012 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: 755415674 Relay_Log_Space: 16281076 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 123456789 Master_UUID: 41dbce5e-a478-11ea-b751-fa163e0ec694 Master_Info_File: mysql.slave_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: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 41dbce5e-a478-11ea-b751-fa163e0ec694:6727120-6740033 Executed_Gtid_Set: 41dbce5e-a478-11ea-b751-fa163e0ec694:1-6740033 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set (0.01 sec) mysql>
我们看到Retrieved_Gtid_Set是从上次Executed_Gtid_Set之后开始，前面的都丢了，有重新到主库去取日志了。
