这篇文章将为大家详细讲解有关mysql中如果将relay_log_recovery设置为0会出现什么后果，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

在MySQL5.7版本中，开启并行复制后，设置relay-log-recovery=1，从库启动后偶尔会报错，类似于https://bugs.mysql.com/bug.php?id=83713。所以尝试将relay-log-recovery设置为0，观察会发生什么。

MySQL5.7.22

GTID模式

relay-log-recovery=0

relay_log_purge=off

sync_relay_log = 10000

relay_log_info_repository = TABLE

不开启并行复制

停掉slave，查看相关信息

[root@shadow:/root5.7.22-log_Instance1root@localhost:(none)17:20:34]>stopslave;QueryOK,0rowsaffected(0.01sec)[root@shadow:/root5.7.22-log_Instance1root@localhost:(none)17:20:37]>select*frommysql.slave_relay_log_info;showslavestatus\G+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|Number_of_lines|Relay_log_name|Relay_log_pos|Master_log_name|Master_log_pos|Sql_delay|Number_of_workers|Id|Channel_name|+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|7|/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000017|186094|mysql-bin.000001|4941307|0|0|1||+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+1rowinset(0.00sec)***************************1.row***************************Slave_IO_State:Master_Host:10.10.30.18Master_User:replMaster_Port:3306Connect_Retry:10Master_Log_File:mysql-bin.000001Read_Master_Log_Pos:4941307Relay_Log_File:mysql-relay-bin.000017Relay_Log_Pos:186094Relay_Master_Log_File:mysql-bin.000001Slave_IO_Running:NoSlave_SQL_Running:NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:4941307Relay_Log_Space:4315819Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:330618Master_UUID:2662c965-fdb2-11e8-8157-0cc47a3aae0dMaster_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-11822Executed_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-11822Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

关闭数据库stopmysql

$stopmysql

查看最后一个relaylog中的最后一个事务的GTID为2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822

$mysqlbinlog-vvvmysql-relay-bin.000017#at185676#18121417:20:36serverid330618end_log_pos4940954CRC320xc2acb86dGTIDlast_committed=11821sequence_number=11822rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822'/*!*/;#at185741#18121417:20:36serverid330618end_log_pos4941046CRC320x516997b4Querythread_id=60654exec_time=0error_code=0SETTIMESTAMP=1544779236/*!*/;BEGIN/*!*/;#at185833#18121417:20:36serverid330618end_log_pos4941139CRC320x65ea73ebRows_query#updateqdata_mysql_heartbeatsetts=now()whereserver_id=@@server_id#at185926#18121417:20:36serverid330618end_log_pos4941212CRC320xb88fd1dbTable_map:`qdata_mysql`.`qdata_mysql_heartbeat`mappedtonumber108#at185999#18121417:20:36serverid330618end_log_pos4941276CRC320x67572f7aUpdate_rows:tableid108flags:STMT_END_FBINLOG'5HUTXB16CwUAXQAAAFNlSwCAAEV1cGRhdGUgcWRhdGFfbXlzcWxfaGVhcnRiZWF0IHNldCB0cz1ub3coKSB3aGVyZSBzZXJ2ZXJfaWQ9QEBzZXJ2ZXJfaWTrc+pl5HUTXBN6CwUASQAAAJxlSwAAAGwAAAAAAAEAC3FkYXRhX215c3FsABVxZGF0YV9teXNxbF9oZWFydGJlYXQAAggSAQAA29GPuA==5HUTXB96CwUAQAAAANxlSwAAAGwAAAAAAAEAAgAC///8egsFAAAAAACZoZ0VI/x6CwUAAAAAAJmhnRUkei9XZw=='/*!*/;###UPDATE`qdata_mysql`.`qdata_mysql_heartbeat`###WHERE###@1=330618/*LONGINTmeta=0nullable=0is_null=0*/###@2='2018-12-1417:20:35'/*DATETIME(0)meta=0nullable=0is_null=0*/###SET###@1=330618/*LONGINTmeta=0nullable=0is_null=0*/###@2='2018-12-1417:20:36'/*DATETIME(0)meta=0nullable=0is_null=0*/#at186063#18121417:20:36serverid330618end_log_pos4941307CRC320x78f64c71Xid=776909COMMIT/*!*/;#at186094#18121417:21:45serverid330619end_log_pos186117CRC320x014899bcStopSET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

用以下命令模拟relaylog丢失，这样mysql重新启动的时候初始化的Retrieved_Gtid_Set集合会比Executed_Gtid_Set集合小

$ddif=mysql-relay-bin.000017of=/tmp/mysql-relay-bin.000017bs=1count=185090$mv/tmp/mysql-relay-bin.000017./$chown-Rmysql:mysql*##185090处对应的是2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820这个事务，所以GTID为2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820的事务被截断，2662c965-fdb2-11e8-8157-0cc47a3aae0d:11821、2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822被抹掉#at184840#18121417:20:34serverid330618end_log_pos4940118CRC320xf6f3aed0GTIDlast_committed=11819sequence_number=11820rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820'/*!*/;#at184905#18121417:20:34serverid330618end_log_pos4940210CRC320x22409fbfQuerythread_id=60652exec_time=0error_code=0SETTIMESTAMP=1544779234/*!*/;BEGIN/*!*/;#at184997#18121417:20:34serverid330618end_log_pos4940303CRC320x436ed81dRows_query#updateqdata_mysql_heartbeatsetts=now()whereserver_id=@@server_id#at185090#18121417:20:34serverid330618end_log_pos4940376CRC320xaadcb170Table_map:`qdata_mysql`.`qdata_mysql_heartbeat`mappedtonumber108#at185163#18121417:20:34serverid330618end_log_pos4940440CRC320xabc65d06Update_rows:tableid108flags:STMT_END_F

启动数据库startmysql

$startmysql

查看复制状态是否正常，复制正常。
且看到是从2662c965-fdb2-11e8-8157-0cc47a3aae0d:11823开始拉取binlog，因为UNION的GTID集合为2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-11822。

[root@shadow:/root5.7.22-log_Instance1root@localhost:(none)17:35:33]>select*frommysql.slave_relay_log_info;showslavestatus\G+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|Number_of_lines|Relay_log_name|Relay_log_pos|Master_log_name|Master_log_pos|Sql_delay|Number_of_workers|Id|Channel_name|+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|7|/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000019|371686|mysql-bin.000001|5312491|0|0|1||+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+1rowinset(0.00sec)***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:10.10.30.18Master_User:replMaster_Port:3306Connect_Retry:10Master_Log_File:mysql-bin.000001Read_Master_Log_Pos:5312491Relay_Log_File:mysql-relay-bin.000019Relay_Log_Pos:371686Relay_Master_Log_File:mysql-bin.000001Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:5312491Relay_Log_Space:4686796Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:330618Master_UUID:2662c965-fdb2-11e8-8157-0cc47a3aae0dMaster_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-11819:11823-12710Executed_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-12710Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

查看error.log相关信息

2018-12-14T17:34:42.471162+08:001[Note]SlaveI/Othreadforchannel'':connectedtomaster'repl@10.10.30.18:3306',replicationstartedinlog'mysql-bin.000001'atposition49413072018-12-14T17:34:42.474786+08:000[Note]EventScheduler:Loaded0events2018-12-14T17:34:42.474921+08:000[Note]/usr/local/mysql/bin/mysqld:readyforconnections.Version:'5.7.22-log'socket:'/home/mysql/data/mysqldata1/sock/mysql.sock'port:3306MySQLCommunityServer(GPL)2018-12-14T17:34:42.476711+08:002[Note]SlaveSQLthreadforchannel''initialized,startingreplicationinlog'mysql-bin.000001'atposition4941307,relaylog'/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000017'position:1860942018-12-14T17:34:43.965844+08:005[Note]Startbinlog_dumptomaster_thread_id(5)slave_server(330618),pos(,4)

查看下个relaylog信息

$mysqlbinlog-vvvmysql-relay-bin.000018/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#18121417:34:42serverid330619end_log_pos123CRC320x0996b423Start:binlogv4,serverv5.7.22-logcreated18121417:34:42#ThisFormat_description_eventappearsinarelaylogandwasgeneratedbytheslavethread.#at123#18121417:34:42serverid330619end_log_pos242CRC320x8885f92aPrevious-GTIDs#2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-11819#at242#18121417:34:42serverid330619end_log_pos295CRC320x1a4ec323Rotatetomysql-relay-bin.000019pos:4SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看再下个relaylog信息，发现是从2662c965-fdb2-11e8-8157-0cc47a3aae0d:11823开始拉取日志

$mysqlbinlog-vvvmysql-relay-bin.000019/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#18121417:34:42serverid330619end_log_pos123CRC320x0996b423Start:binlogv4,serverv5.7.22-logcreated18121417:34:42#ThisFormat_description_eventappearsinarelaylogandwasgeneratedbytheslavethread.#at123#18121417:34:42serverid330619end_log_pos242CRC320x8885f92aPrevious-GTIDs#2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-11819#at242#7001018:00:00serverid330618end_log_pos0CRC320x073c3e73Rotatetomysql-bin.000001pos:4#at289#18121413:52:24serverid330618end_log_pos123CRC320x0180f777Start:binlogv4,serverv5.7.22-logcreated18121413:52:24BINLOG'GEUTXA96CwUAdwAAAHsAAAAAAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAXf3gAE='/*!*/;#at408#18121417:34:42serverid0end_log_pos455CRC320x3dfb4b92Rotatetomysql-bin.000001pos:154#at455#18121417:34:42serverid0end_log_pos502CRC320x1ff57b1cRotatetomysql-bin.000001pos:4941307#at502#18121417:20:37serverid330618end_log_pos4941372CRC320xa2232924GTIDlast_committed=11822sequence_number=11823rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='2662c965-fdb2-11e8-8157-0cc47a3aae0d:11823'/*!*/;#at567#18121417:20:37serverid330618end_log_pos4941464CRC320xdecc7ed2Querythread_id=60655exec_time=0error_code=0SETTIMESTAMP=1544779237/*!*/;SET@@session.pseudo_thread_id=60655/*!*/;SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;SET@@session.sql_mode=1436549152/*!*/;SET@@session.auto_increment_increment=2,@@session.auto_increment_offset=1/*!*/;/*!\Cutf8*//*!*/;SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;SET@@session.time_zone='+08:00'/*!*/;SET@@session.lc_time_names=0/*!*/;SET@@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;#at659#18121417:20:37serverid330618end_log_pos4941557CRC320x17e36176Rows_query#updateqdata_mysql_heartbeatsetts=now()whereserver_id=@@server_id#at752

再次查看mysql-relay-bin.000017中的内容，最后记录如下，多了WARNING的信息

$mysqlbinlog-vvvmysql-relay-bin.000017BEGIN/*!*/;#at184997#18121417:20:34serverid330618end_log_pos4940303CRC320x436ed81dRows_query#updateqdata_mysql_heartbeatsetts=now()whereserver_id=@@server_idWARNING:TherangeofprintedeventsendswitharoweventoratablemapeventthatdoesnothavetheSTMT_END_Fflagset.Thismightbebecausethelaststatementwasnotfullywrittentothelog,orbecauseyouareusinga--stop-positionor--stop-datetimethatreferstoaneventinthemiddleofastatement.Theevent(s)fromthepartialstatementhavenotbeenwrittentooutput.ROLLBACK/*addedbymysqlbinlog*//*!*/;SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/; Retrieved_Gtid_Set > Executed_Gtid_Set

停掉slave的sql线程，模拟Retrieved_Gtid_Set > Executed_Gtid_Set，如下：

[root@shadow:/root5.7.22-log_Instance1root@localhost:(none)16:08:48]>stopslavesql_thread;QueryOK,0rowsaffected(0.00sec)[root@shadow:/root5.7.22-log_Instance1root@localhost:(none)16:08:57]>select*frommysql.slave_relay_log_info;showslavestatus\G+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|Number_of_lines|Relay_log_name|Relay_log_pos|Master_log_name|Master_log_pos|Sql_delay|Number_of_workers|Id|Channel_name|+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|7|/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000011|1787845|mysql-bin.000001|3158537|0|0|1||+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+1rowinset(0.00sec)***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:10.10.30.18Master_User:replMaster_Port:3306Connect_Retry:10Master_Log_File:mysql-bin.000001Read_Master_Log_Pos:3171077Relay_Log_File:mysql-relay-bin.000011Relay_Log_Pos:1787845Relay_Master_Log_File:mysql-bin.000001Slave_IO_Running:YesSlave_SQL_Running:NoReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:3158537Relay_Log_Space:2542538Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:NULLMaster_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:330618Master_UUID:2662c965-fdb2-11e8-8157-0cc47a3aae0dMaster_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Master_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-7587Executed_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-7557Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

关闭数据库stopmysql

$stopmysql

查看mysql-relay-bin.000011最后一个事务GTID为2662c965-fdb2-11e8-8157-0cc47a3aae0d:7604

$mysqlbinlog-vvvmysql-relay-bin.000011COMMIT/*!*/;#at1807073#18121416:09:40serverid330618end_log_pos3177830CRC320xe6febde8GTIDlast_committed=7603sequence_number=7604rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='2662c965-fdb2-11e8-8157-0cc47a3aae0d:7604'/*!*/;#at1807138#18121416:09:40serverid330618end_log_pos3177922CRC320x83a67d51Querythread_id=56362exec_time=0error_code=0SETTIMESTAMP=1544774980/*!*/;BEGIN/*!*/;#at1807230#18121416:09:40serverid330618end_log_pos3178015CRC320x904b0728Rows_query#updateqdata_mysql_heartbeatsetts=now()whereserver_id=@@server_id#at1807323#18121416:09:40serverid330618end_log_pos3178088CRC320xd4354200Table_map:`qdata_mysql`.`qdata_mysql_heartbeat`mappedtonumber108#at1807396#18121416:09:40serverid330618end_log_pos3178152CRC320xc545f0a4Update_rows:tableid108flags:STMT_END_FBINLOG'RGUTXB16CwUAXQAAAB9+MACAAEV1cGRhdGUgcWRhdGFfbXlzcWxfaGVhcnRiZWF0IHNldCB0cz1ub3coKSB3aGVyZSBzZXJ2ZXJfaWQ9QEBzZXJ2ZXJfaWQoB0uQRGUTXBN6CwUASQAAAGh+MAAAAGwAAAAAAAEAC3FkYXRhX215c3FsABVxZGF0YV9teXNxbF9oZWFydGJlYXQAAggSAQAAAEI11A==RGUTXB96CwUAQAAAAKh+MAAAAGwAAAAAAAEAAgAC///8egsFAAAAAACZoZ0CZ/x6CwUAAAAAAJmhnQJopPBFxQ=='/*!*/;###UPDATE`qdata_mysql`.`qdata_mysql_heartbeat`###WHERE###@1=330618/*LONGINTmeta=0nullable=0is_null=0*/###@2='2018-12-1416:09:39'/*DATETIME(0)meta=0nullable=0is_null=0*/###SET###@1=330618/*LONGINTmeta=0nullable=0is_null=0*/###@2='2018-12-1416:09:40'/*DATETIME(0)meta=0nullable=0is_null=0*/#at1807460#18121416:09:40serverid330618end_log_pos3178183CRC320x6a5edec2Xid=764012COMMIT/*!*/;#at1807491#18121416:09:51serverid330619end_log_pos1807514CRC320xed4f17f3StopSET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

执行以下命令将GTID为2662c965-fdb2-11e8-8157-0cc47a3aae0d:7600的事务截掉一半，2662c965-fdb2-11e8-8157-0cc47a3aae0d:7601、2662c965-fdb2-11e8-8157-0cc47a3aae0d:7602、2662c965-fdb2-11e8-8157-0cc47a3aae0d:7603、2662c965-fdb2-11e8-8157-0cc47a3aae0d:7604全部抹掉

$ddif=mysql-relay-bin.000011of=/tmp/mysql-relay-bin.000011bs=1count=1805788$mv/tmp/mysql-relay-bin.000011./$chown-Rmysql:mysql*##1805788这个位点对应事务的GTID为7600#at1805401#18121416:09:36serverid330618end_log_pos3176158CRC320xeb1649f2GTIDlast_committed=7599sequence_number=7600rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='2662c965-fdb2-11e8-8157-0cc47a3aae0d:7600'/*!*/;#at1805466#18121416:09:36serverid330618end_log_pos3176250CRC320x9e2a31e5Querythread_id=56358exec_time=0error_code=0SETTIMESTAMP=1544774976/*!*/;BEGIN/*!*/;#at1805558#18121416:09:36serverid330618end_log_pos3176343CRC320x920329a0Rows_query#updateqdata_mysql_heartbeatsetts=now()whereserver_id=@@server_id#at1805651#18121416:09:36serverid330618end_log_pos3176416CRC320x2204d593Table_map:`qdata_mysql`.`qdata_mysql_heartbeat`mappedtonumber108#at1805724#18121416:09:36serverid330618end_log_pos3176480CRC320x2f91a93aUpdate_rows:tableid108flags:STMT_END_FBINLOG'QGUTXB16CwUAXQAAAJd3MACAAEV1cGRhdGUgcWRhdGFfbXlzcWxfaGVhcnRiZWF0IHNldCB0cz1ub3coKSB3aGVyZSBzZXJ2ZXJfaWQ9QEBzZXJ2ZXJfaWSgKQOSQGUTXBN6CwUASQAAAOB3MAAAAGwAAAAAAAEAC3FkYXRhX215c3FsABVxZGF0YV9teXNxbF9oZWFydGJlYXQAAggSAQAAk9UEIg==QGUTXB96CwUAQAAAACB4MAAAAGwAAAAAAAEAAgAC///8egsFAAAAAACZoZ0CY/x6CwUAAAAAAJmhnQJkOqmRLw=='/*!*/;###UPDATE`qdata_mysql`.`qdata_mysql_heartbeat`###WHERE###@1=330618/*LONGINTmeta=0nullable=0is_null=0*/###@2='2018-12-1416:09:35'/*DATETIME(0)meta=0nullable=0is_null=0*/###SET###@1=330618/*LONGINTmeta=0nullable=0is_null=0*/###@2='2018-12-1416:09:36'/*DATETIME(0)meta=0nullable=0is_null=0*/#at1805788#18121416:09:36serverid330618end_log_pos3176511CRC320xa6103d84Xid=764000COMMIT/*!*/;

启动数据库startmysql

$startmysql

查看复制状态是否正常，显示正常。

[root@shadow:/root5.7.22-log_Instance1root@localhost:(none)16:19:05]>select*frommysql.slave_relay_log_info;showslavestatus\G+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|Number_of_lines|Relay_log_name|Relay_log_pos|Master_log_name|Master_log_pos|Sql_delay|Number_of_workers|Id|Channel_name|+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|7|/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000013|236672|mysql-bin.000001|3412263|0|0|1||+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+1rowinset(0.00sec)***************************1.row***************************Slave_IO_State:WaitingformastertosendeventMaster_Host:10.10.30.18Master_User:replMaster_Port:3306Connect_Retry:10Master_Log_File:mysql-bin.000001Read_Master_Log_Pos:3412263Relay_Log_File:mysql-relay-bin.000013Relay_Log_Pos:236672Relay_Master_Log_File:mysql-bin.000001Slave_IO_Running:YesSlave_SQL_Running:YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno:0Last_Error:Skip_Counter:0Exec_Master_Log_Pos:3412263Relay_Log_Space:2784908Until_Condition:NoneUntil_Log_File:Until_Log_Pos:0Master_SSL_Allowed:NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master:0Master_SSL_Verify_Server_Cert:NoLast_IO_Errno:0Last_IO_Error:Last_SQL_Errno:0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id:330618Master_UUID:2662c965-fdb2-11e8-8157-0cc47a3aae0dMaster_Info_File:mysql.slave_master_infoSQL_Delay:0SQL_Remaining_Delay:NULLSlave_SQL_Running_State:Slavehasreadallrelaylog;waitingformoreupdatesMaster_Retry_Count:86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-8164Executed_Gtid_Set:2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-8164Auto_Position:1Replicate_Rewrite_DB:Channel_Name:Master_TLS_Version:1rowinset(0.00sec)

查看error.log

2018-12-14T16:17:37.822661+08:001[Note]SlaveSQLthreadforchannel''initialized,startingreplicationinlog'mysql-bin.000001'atposition3158537,relaylog'/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000011'position:17878452018-12-14T16:17:37.822915+08:002[Note]SlaveI/Othreadforchannel'':connectedtomaster'repl@10.10.30.18:3306',replicationstartedinlog'mysql-bin.000001'atposition27646132018-12-14T16:17:37.826982+08:000[Note]EventScheduler:Loaded0events2018-12-14T16:17:37.827132+08:000[Note]/usr/local/mysql/bin/mysqld:readyforconnections.Version:'5.7.22-log'socket:'/home/mysql/data/mysqldata1/sock/mysql.sock'port:3306MySQLCommunityServer(GPL)2018-12-14T16:17:38.020740+08:001[Note]SlaveSQLforchannel'':Rollingbackunfinishedtransaction(noCOMMITorROLLBACKinrelaylog).AprobablecauseispartialtransactionleftonrelaylogbecauseofrestartingIOthreadwithauto-positioningprotocol.Error_code:02018-12-14T16:17:39.639144+08:004[Note]Startbinlog_dumptomaster_thread_id(4)slave_server(330618),pos(,4)

查看下一个relaylog的信息

$mysqlbinlog-vvvmysql-relay-bin.000012/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#18121416:17:37serverid330619end_log_pos123CRC320x32f87201Start:binlogv4,serverv5.7.22-logcreated18121416:17:37#ThisFormat_description_eventappearsinarelaylogandwasgeneratedbytheslavethread.#at123#18121416:17:37serverid330619end_log_pos242CRC320x524f2179Previous-GTIDs#2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-7599#at242#18121416:17:37serverid330619end_log_pos295CRC320xfe1b4a1fRotatetomysql-relay-bin.000013pos:4SET@@SESSION.GTID_NEXT='AUTOMATIC'/*addedbymysqlbinlog*//*!*/;DELIMITER;#Endoflogfile/*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看再下一个relaylog的信息，发现确实会重新去拉取2662c965-fdb2-11e8-8157-0cc47a3aae0d:7600这个GTID对应的日志

$mysqlbinlog-vvvmysql-relay-bin.000013/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER/*!*/;#at4#18121416:17:37serverid330619end_log_pos123CRC320x32f87201Start:binlogv4,serverv5.7.22-logcreated18121416:17:37#ThisFormat_description_eventappearsinarelaylogandwasgeneratedbytheslavethread.#at123#18121416:17:37serverid330619end_log_pos242CRC320x524f2179Previous-GTIDs#2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101:104-1504:1509-2079:2082-7599#at242#7001018:00:00serverid330618end_log_pos0CRC320x073c3e73Rotatetomysql-bin.000001pos:4#at289#18121413:52:24serverid330618end_log_pos123CRC320x0180f777Start:binlogv4,serverv5.7.22-logcreated18121413:52:24BINLOG'GEUTXA96CwUAdwAAAHsAAAAAAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQAAXf3gAE='/*!*/;#at408#18121416:17:37serverid0end_log_pos455CRC320x340c6196Rotatetomysql-bin.000001pos:154#at455#18121416:17:37serverid0end_log_pos502CRC320x62bfc8f3Rotatetomysql-bin.000001pos:3176093#at502#18121416:09:36serverid330618end_log_pos3176158CRC320xeb1649f2GTIDlast_committed=7599sequence_number=7600rbr_only=yes/*!50718SETTRANSACTIONISOLATIONLEVELREADCOMMITTED*//*!*/;SET@@SESSION.GTID_NEXT='2662c965-fdb2-11e8-8157-0cc47a3aae0d:7600'/*!*/;#at567#18121416:09:36serverid330618end_log_pos3176250CRC320x9e2a31e5Querythread_id=56358exec_time=0error_code=0SETTIMESTAMP=1544774976/*!*/;SET@@session.pseudo_thread_id=56358/*!*/;SET@@session.foreign_key_checks=1,@@session.sql_auto_is_null=0,@@session.unique_checks=1,@@session.autocommit=1/*!*/;SET@@session.sql_mode=1436549152/*!*/;SET@@session.auto_increment_increment=2,@@session.auto_increment_offset=1/*!*/;/*!\Cutf8*//*!*/;SET@@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;SET@@session.time_zone='+08:00'/*!*/;SET@@session.lc_time_names=0/*!*/;SET@@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;#at659#18121416:09:36serverid330618end_log_pos3176343CRC320x920329a0Rows_query#updateqdata_mysql_heartbeatsetts=now()whereserver_id=@@server_id#at752

|总结

从error.log中能看到，MySQL重启后，Slave SQL thread会根据mysql.slave_relay_log_info表中记录的位置点信息去starting replication

[root@shadow:/root5.7.22-log_Instance1root@localhost:(none)16:08:57]>select*frommysql.slave_relay_log_info;showslavestatus\G+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|Number_of_lines|Relay_log_name|Relay_log_pos|Master_log_name|Master_log_pos|Sql_delay|Number_of_workers|Id|Channel_name|+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+|7|/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000011|1787845|mysql-bin.000001|3158537|0|0|1||+-----------------+-------------------------------------------------------------+---------------+------------------+----------------+-----------+-------------------+----+--------------+1rowinset(0.00sec)2018-12-14T16:17:37.822661+08:001[Note]SlaveSQLthreadforchannel''initialized,startingreplicationinlog'mysql-bin.000001'atposition3158537,relaylog'/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000011'position:1787845

观察两种情况下error.log日志中内容，发现：

Retrieved_Gtid_Set < Executed_Gtid_Set下，SQL线程按mysql.slave_relay_log_info表中记录的位置点信息（mysql-relay-bin.000017，186094）去starting replication时，发现对应的日志信息不存在（mysql-relay-bin.000017日志被我截断到185090），就继续执行下一个relaylog。因为Executed_Gtid_Set > Retrieved_Gtid_Set，UNION集合就是Executed_Gtid_Set，所以新生成的relaylog里拉取的第一个GTID是Executed_Gtid_Set的下一个GTID，SQL线程正常回放。

Retrieved_Gtid_Set > Executed_Gtid_Set下，error.log中有如下信息。因为在Executed_Gtid_Set小于Retrieved_Gtid_Set情况下，被截断的GTID还未被执行，所以SQL线程会尝试去执行，执行了发现事务不完整，会进行rollback；且MySQL启动后，这个不完整的事务会被放到last_received_GTID中，这样MySQL会根据UNION(@@global.gtid_executed, Retrieved_gtid_set - last_received_GTID)集合，IO线程会重新拉取这个不完整的事务，此时拉取后事务完整，SQL线程正常回放。

2018-12-14T16:17:38.020740+08:001[Note]SlaveSQLforchannel'':Rollingbackunfinishedtransaction(noCOMMITorROLLBACKinrelaylog).AprobablecauseispartialtransactionleftonrelaylogbecauseofrestartingIOthreadwithauto-positioningprotocol.Error_code:0

关于“mysql中如果将relay_log_recovery设置为0会出现什么后果”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。