Shulou(Shulou.com)05/31 Report--

This article will explain in detail what will happen if relay_log_recovery is set to 0 in mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

| | background |

In the MySQL5.7 version, when parallel replication is enabled, set relay-log-recovery=1, and occasionally report an error after booting from the library, similar to https://bugs.mysql.com/bug.php?id=83713. So try setting relay-log-recovery to 0 and see what happens.

| | Environment |

MySQL5.7.22

GTID mode

Relay-log-recovery=0

Relay_log_purge=off

Sync_relay_log = 10000

Relay_log_info_repository = TABLE

Do not enable parallel replication

| | simulate relay-log without full Retrieved_Gtid_Set without sync |

< Executed_Gtid_Set 停掉slave，查看相关信息 [root@shadow:/root 5.7.22-log_Instance1 root@localhost:(none) 17:20:34]>

Stop slave;Query OK, 0 rows affected (0.01 sec) [root@shadow:/root 5.7.22-log_Instance1 root@localhost: (none) 17:20:37] > select * from mysql.slave_relay_log_info Show slave status\ Gao MusicaMui + -+-- +-+ | 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 | 1 | | +-+- -- +- +-+-+ 1 row in set (0.00 sec) * * 1. Row * * Slave_IO_State: Master_Host: 10.10.30.18 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 4941307 Relay_Log_File: mysql-relay-bin.000017 Relay_Log_Pos: 186094 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: No 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: 4941307 Relay_Log_Space: 4315819 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: NULLMaster_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: 330618 Master_UUID: 2662c965-fdb2-11e8-8157-0cc47a3aae0d 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: 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101 Executed_Gtid_Set: 2662c965-fdb2-11e8-8157 -0cc47a3aae0d:1-11822 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set

Shut down the database stopmysql

$stopmysql

Check that the GTID of the last transaction in the last relaylog is 2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822

$mysqlbinlog-vvv mysql-relay-bin.000017# at 185676 "181214 17:20:36 server id 330618 end_log_pos 4940954 CRC32 0xc2acb86d GTID last_committed=11821 sequence_number=11822 rbrushed onlyzed words @ @ SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0cc47a3aae0dazo 11822" at 185741 "181214 17:20:36 server id 330618 end_log_pos 4941046 CRC32 0x516997b4 Query thread_id=60654 exec_time=0 error_code=0SET timestamp 1544779236 Begin _ blank # at 185833#181214 17:20:36 server id 330618 end_log_pos 4941139 CRC32 0x65ea73eb Rows_query# update qdata_mysql_heartbeat set ts=now() where server_id=server_id# at 185926#181214 17:20:36 server id 330618 end_log_pos 4941212 CRC32 0xb88fd1db Table_map: `qdata_mysql`.`qdata_mysql_heartbeat` mapped to number 108# at 185999#181214 17:20:36 server id 330618 end_log_pos 4941276 CRC32 0x67572f7a Update_rows: table id 108 flags: STMT_END_FBINLOG '5HUTXB16CwUAXQAAAFNlSwCAAEV1cGRhdGUgcWRhdGFfbXlzcWxfaGVhcnRiZWF0IHNldCB0cz1ub3coKSB3aGVyZSBzZXJ2ZXJfaWQ9QEBzZXJ2ZXJfaWTrc+pl5HUTXBN6CwUASQAAAJxlSwAAAGwAAAAAAAEAC3FkYXRhX215c3FsABVxZGF0YV9teXNxbF9oZWFydGJlYXQAAggSAQAA29GPuA==5HUTXB96CwUAQAAAANxlSwAAAGwAAAAAAAEAAgAC///8egsFAAAAAACZoZ0VI/x6CwUAAAAAAJmhnRUkei9XZw=='/*!*/; # UPDATE `qdata_ mysql`.`qdata _ mysql_ heartbeat` # WHERE### @ 1mm 330618 / * LONGINT meta=0 nullable=0 is_null=0 * / # @ 2mm meta=0 nullable=0 is_null=0 * / # at 186063'/ * meta=0 nullable=0 is_null=0 * / # at 186063 # 181214 17:20:36 server id 330618 end_log_pos 4941307 CRC32 0x78f64c71 Xid = 776909COMMIT # at 186094 / 181214 17:21:45 server id 330619 end_log_pos 186117 CRC32 0x014899bc StopSET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filewise; # End of log filewise "50003 SET completion types"

Simulate the loss of relaylog with the following command so that the Retrieved_Gtid_Set collection initialized when mysql is restarted is smaller than the Executed_Gtid_Set collection

$dd if=mysql-relay-bin.000017 of=/tmp/mysql-relay-bin.000017 bs=1 count=185090$ mv / tmp/mysql-relay-bin.000017. / $chown-R mysql:mysql * # 185090 corresponds to the 2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820 transaction, so the transaction whose GTID is 2662c965-fdb2-11e8-8157-0cc47a3aae0d:11820 is truncated 2662c965-fdb2-11e8-8157-0cc47a3aae0d:11821, 2662c965-fdb2-11e8-8157-0cc47a3aae0d:11822 erased # at 184840 / 181214 17:20:34 server id 330618 end_log_pos 4940118 CRC32 0xf6f3aed0 GTID last_committed=11819 sequence_number=11820 rbrushonlyzes / / SET @ @ SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0cc47a3aae0d purge 11820 peg / at 184905 / 181214 17:20:34 server id 330618 end_log_pos 4940210 CRC32 0x22409fbf Query thread_id=60652 exec_time=0 error_code=0SET timespace 1544779234 / # at 184997 / 181214 17:20:34 server id 330618 end_log_pos 4940303 CRC32 0x436ed81d Rows_query# update qdata_mysql_heartbeat set ts=now () where server_id=@@server_id# at 185090 / 181214 17:20:34 server id 330618 end_log_pos 4940376 CRC32 0xaadcb170 Table_map: `qdata_ mysql`.`qdata _ mysql_ heartbeat`mapped to number 10 percent at 185163181214 17:20:34 server id 330618 end_log_pos 4940440 CRC32 0xabc65d06 Update_rows: table id flags: STMT_END_F

Start the database startmysql

$startmysql

Check to see if the replication status is normal and the replication is normal.

Also see that the binlog is pulled from 2662c965-fdb2-11e8-8157-0cc47a3aae0d:11823, because the GTID collection of UNION is 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-11822.

[root@shadow:/root 5.7.22-log_Instance1 root@localhost: (none) 17:35:33] > select * from mysql.slave_relay_log_info Show slave status\ Gao MusicaMui + -+-- +-+ | 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 | 1 | | +-+- -- +- +-+-+ 1 row in set (0.00 sec) * * 1. Row * * Slave_IO_State: Waiting for Master to send event Master_Host: 10.10.30.18 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 5312491 Relay_Log_File: mysql-relay-bin.000019 Relay_Log_Pos: 371686 Relay_Master_Log_File: mysql- Bin.000001 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: 5312491 Relay_Log_Space: 4686796 Until_Condition: None Until_Log_File: Until_Log_Pos: 0Master_SSL _ Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_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: 330618 Master_UUID: 2662c965-fdb2-11e8-8157-0cc47a3aae0d 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: 2662c965-fdb2-11e8-8157-0cc47a3aae0dParade 1-101Parade 1509-2079VOULl1823-12710 Executed_Gtid_Set: 2662c965-fdb2-11e8-8157-0cc47a3aae0d1- 12710 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec)

View error.log related information

2018-12-14T17:34:42.471162+08:00 1 [Note] Slave I connected to master O thread for channel': connected to master 'repl@10.10.30.18:3306' Replication started in log' mysql-bin.000001' at position 49413072018-12-14T17:34:42.474786+08:00 0 [Note] Event Scheduler: Loaded 0 events2018-12-14T17:34:42.474921+08:00 0 [Note] / usr/local/mysql/bin/mysqld: ready for connections.Version: '5.7.22 socket:' / home/mysql/data/mysqldata1/sock/mysql.sock' port: 3306 MySQL Community Server (GPL) 2018-12-14T17:34:42.476711+08: 00 2 [Note] Slave SQL thread for channel''initialized Starting replication inlog 'mysql-bin.000001' at position 4941307, relaylog' / home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000017' position: 1860942018-12-14T17:34:43.965844+08:00 5 [Note] Start binlog_dump to master_thread_id (5) slave_server (330618), pos (, 4)

View the next relaylog information

$mysqlbinlog-vvv Myql SET SET bin.000018 take 50530 SET @ @ SESSION.PSEUDOAs SLAVEMETDE1 / * / # at 4#181214 17:34:42 server id 330619 end_log_pos 123 CRC32 0x0996b423 Start: binlog v 4 Server v 5.7.22-log created 181214 17 This Format_description_event appears in a relay log and was generated by the slave thread.# at 34 CRC32 0x1a4ec323 Rotate to mysql-relay-bin.000019 pos 17:34:42 server id 330619 end_log_pos 242CRC32 0x8885f92a Previous-GTIDs# 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101 CRC32 0x1a4ec323 Rotate to mysql-relay-bin.000019 pos 1509-207914 181214 17:34:42 server id 330619 end_log_pos 295 CRC32 0x1a4ec323 Rotate to mysql-relay-bin.000019 pos: 4SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * /! * / DELIMITER; # End of log filebinder "50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/" SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Check the next relaylog information and find that the log is pulled from 2662c965-fdb2-11e8-8157-0cc47a3aae0d:11823.

$mysqlbinlog-vvv MySQL SET SET bin.000019 etc. 50530 SET @ @ SESSION.PSEUDOZHOSLAVEVEMODEN1Universe 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4#181214 17:34:42 server id 330619 end_log_pos 123 CRC32 0x0996b423 Start: binlog v 4 Server v 5.7.22-log created 181214 17 This Format_description_event appears in a relay log and was generated by the slave thread.# at 34 CRC32 0x073c3e73 Rotate to mysql-bin.000001 pos 17:34:42 server id 330619 end_log_pos 242CRC32 0x8885f92a Previous-GTIDs# 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101 CRC32 0x073c3e73 Rotate to mysql-bin.000001 pos 1509-2079 This Format_description_event appears in a relay log and was generated by the slave thread.# at 2082-118 "at 242" 700101 8:00:00 server id 330618 0 CRC32 0x073c3e73 Rotate to mysql-bin.000001 pos # 181214 13:52:24 server id 330618 end_log_pos 123 CRC32 0x0180f777 Start: binlog v 4 Server v 5.7.22-log created 181214 13:52:24BINLOG 'GEUTXA96CwUAdwAAHsAAAAAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 408 "181214 17:34:42 server id 0 end_log_pos 455 CRC32 0x3dfb4b92 Rotate to mysql-bin.000001 pos: 15" at 455 "181214 17:34:42 server id 0 end_log_pos 502 CRC32 0x1ff57b1c Rotate to mysql-bin.000001 pos: 494130" at 502 "181214 17:20:37 server id 330618 end_log_pos 4941372 CRC32 0xa2232924 GTID last_committed=11822 sequence_number=11823 rbrachionlyzes SET @ @ SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0cc47a3aae0dmonger 11823 peg paddle, @ at 567mm 181214 17:20:37 server id 330618 end_log_pos 4941464 CRC32 0xdecc7ed2 Query thread_id=60655 exec_time=0 error_code=0SET timestampin 1544779237 Universe set @ @ session.pseudoplastics thread readable IDN 60655 Universe session.sql_auto_is_null=0 @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.autothread SET @ @ session.sqlhammer modewords 1436549152, please set @ session.auto_increment_increment=2, @ @ session.autovariation increments off setbacks! * /; SET @ @ session.session utf8 * / / *! * /; SET @ @ session.session. SET @ @ session.collationalization databases default CRC32 0x17e36176 Rows_query# update qdata_mysql_heartbeat set ts=now SET @ @ session.collationalization databases database server id @ session.collationalization databases database server id @ session.collationalization databases database server id 330618 end_log_pos 4941557 CRC32 0x17e36176 Rows_query# update qdata_mysql_heartbeat set ts=now () where server_id=@@server_id# at 752

Check the contents of mysql-relay-bin.000017 again, and finally record as follows, with more information about WARNING.

$mysqlbinlog-vvv MySQL end_log_pos relayAfter bin.000017BEGINBEINGUBEINGTUBINGUBINGUBEINGUBING: vvv 184997mm 181214 17:20:34 server id 330618 end_log_pos 4940303 CRC32 0x436ed81d Rows_query# update qdata_mysql_heartbeat set ts=now () where server_id=@@server_idWARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set. This might be because the last statement was not fully written to the log, or because you are using a-stop-position or-stop-datetime that refers to an event in the middle of a statement. The event (s) from the partial statement have not been written to output.ROLLBACK / * added by mysqlbinlog * / / *! * /; SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! * /; DELIMITER; # End of log filewise; # End of log filewise @ @ SESSION.PSEUDO_SLAVE_MODE=0*/;Retrieved_Gtid_Set > Executed_Gtid_Set

Stop the slave SQL thread and simulate Retrieved_Gtid_Set > Executed_Gtid_Set, as follows:

[root@shadow:/root 5.7.22-log_Instance1 root@localhost: (none) 16:08:48] > stop slave sql_thread;Query OK, 0 rows affected (0.00 sec) [root@shadow:/root 5.7.22-log_Instance1 root@localhost: (none) 16:08:57] > select * from mysql.slave_relay_log_info Show slave status\ Gao MusicaMui + -+-- +-+ | 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 | 1 | | +-+- -- +- +-+-+ 1 row in set (0.00 sec) * * 1. Row * * Slave_IO_State: Waiting for Master to send event Master_Host: 10.10.30.18 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 3171077 Relay_Log_File: mysql-relay-bin.000011 Relay_Log_Pos: 1787845 Relay_Master_Log_File: mysql- Bin.000001 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: 3158537 Relay_Log_Space: 2542538 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: NULLMaster_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: 330618 Master_UUID: 2662c965-fdb2-11e8-8157-0cc47a3aae0d 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: 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101 Para104-1504-1509-2079-7587 Executed_Gtid_Set: 2662c965- Fdb2-11e8-8157-0cc47a3aae0d:1-7557 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set

Shut down the database stopmysql

$stopmysql

Check the last transaction of mysql-relay-bin.000011 GTID is 2662c965-fdb2-11e8-8157-0cc47a3aae0d:7604

$mysqlbinlog-vvv Myql Murelayun bin.000011COMMITGUBING account [at 1807073] 181214 16:09:40 server id 330618 end_log_pos 3177830 CRC32 0xe6febde8 GTID last_committed=7603 sequence_number=7604 RMITTEDUBERTERMAGUBITY words "50718 SET TRANSACTION ISOLATION LEVEL READ COMITTEDTERMAGUBER", "set @ @ SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0cc47a3aae0dVOdGUBUTHAEHAEAE0dGUAE0DUBUBUBUTY" Begin _ blank # at 1807230#181214 16:09:40 server id 330618 end_log_pos 3178015 CRC32 0x904b0728 Rows_query# update qdata_mysql_heartbeat set ts=now() where server_id=server_id# at 1807323#181214 16:09:40 server id 330618 end_log_pos 3178088 CRC32 0xd4354200 Table_map: `qdata_mysql`.`qdata_mysql_heartbeat` mapped to number 108# at 1807396#181214 16:09:40 server id 330618 end_log_pos 3178152 CRC32 0xc545f0a4 Update_rows: table id 108 flags: STMT_END_FBINLOG 'RGUTXB16CwUAXQAAAB9+MACAAEV1cGRhdGUgcWRhdGFfbXlzcWxfaGVhcnRiZWF0IHNldCB0cz1ub3coKSB3aGVyZSBzZXJ2ZXJfaWQ9QEBzZXJ2ZXJfaWQoB0uQRGUTXBN6CwUASQAAAGh+MAAAAGwAAAAAAAEAC3FkYXRhX215c3FsABVxZGF0YV9teXNxbF9oZWFydGJlYXQAAggSAQAAAEI11A==RGUTXB96CwUAQAAAAKh+MAAAAGwAAAAAAAEAAgAC///8egsFAAAAAACZoZ0CZ/x6CwUAAAAAAJmhnQJopPBFxQ=='/*!*/; # UPDATE `qdata mysql`.`qdata _ mysql_ heartbeat` # WHERE### @ 1mm 330618 / * LONGINT meta=0 nullable=0 is_null=0 * / # @ 2mm meta=0 nullable=0 is_null=0 * / # at 1807460'/ * DATETIME (0) meta=0 nullable=0 is_null=0 * / # at 1807460 # 181214 16:09:40 server id 330618 end_log_pos 3178183 CRC32 0x6a5edec2 Xid = 764012COMMIT # at 1807491 / 181214 16:09:51 server id 330619 end_log_pos 1807514 CRC32 0xed4f17f3 StopSET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * / *! *; DELIMITER; # End of log filewise; # End of log filewise "50003 SET completion types"

Execute the following command to cut off half of the transactions whose GTID is 2662c965-fdb2-11e8-8157-0cc47a3aae0d:7600, and erase all 2662c965-fdb2-11e8-8157-0cc47a3aae0d:7601, 2662c965-fdb2-11e8-8157-0cc47a3aae0d:7602, 2662c965-fdb2-11e8-8157-0cc47a3aae0d:7603, 2662c965-fdb2-11e8-8157-0cc47a3aae0d:7604.

$dd if=mysql-relay-bin.000011 of=/tmp/mysql-relay-bin.000011 bs=1 count=1805788$ mv / tmp/mysql-relay-bin.000011. / $chown-R mysql:mysql * # # 1805788 the GTID for the corresponding transaction at this location is 760 "at 1805401" 181214 16:09:36 server id 330618 end_log_pos 3176158 CRC32 0xeb1649f2 GTID last_committed=7599 sequence_number=7600 rbraries onlylyzed espact, "50718 SET TRANSACTION ISOLATION LEVEL READ committedwords,"set @ SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0c47a3aae0dvil 7600" # at 1805466 / 181214 16:09:36 server id 330618 end_log_pos 3176250 CRC32 0x9e2a31e5 Query thread_id=56358 exec_time=0 error_code=0SET timestamp 1544774976Compact / # at 1805558#181214 16:09:36 server id 330618 end_log_pos 3176343 CRC32 0x920329a0 Rows_query# update qdata_mysql_heartbeat set ts=now() where server_id=server_id# at 1805651#181214 16:09:36 server id 330618 end_log_pos 3176416 CRC32 0x2204d593 Table_map: `qdata_mysql`.`qdata_mysql_heartbeat` mapped to number 108# at 1805724#181214 16:09:36 server id 330618 end_log_pos 3176480 CRC32 0x2f91a93a Update_rows: table id 108 flags: STMT_END_FBINLOG 'QGUTXB16CwUAXQAAAJd3MACAAEV1cGRhdGUgcWRhdGFfbXlzcWxfaGVhcnRiZWF0IHNldCB0cz1ub3coKSB3aGVyZSBzZXJ2ZXJfaWQ9QEBzZXJ2ZXJfaWSgKQOSQGUTXBN6CwUASQAAAOB3MAAAAGwAAAAAAAEAC3FkYXRhX215c3FsABVxZGF0YV9teXNxbF9oZWFydGJlYXQAAggSAQAAk9UEIg==QGUTXB96CwUAQAAAACB4MAAAAGwAAAAAAAEAAgAC///8egsFAAAAAACZoZ0CY/x6CwUAAAAAAJmhnQJkOqmRLw=='/*!*/; # UPDATE `qdata mysql`.`qdata _ mysql_ heartbeat` # WHERE### @ 1mm 330618 / * LONGINT meta=0 nullable=0 is_null=0 * / # @ 2mm meta=0 nullable=0 is_null=0 * / # at 1805788'/ * DATETIME (0) meta=0 nullable=0 is_null=0 * / # at 1805788 # 181214 16:09:36 server id 330618 end_log_pos 3176511 CRC32 0xa6103d84 Xid = 764000COMMIT

Start the database startmysql

$startmysql

Check to see if the replication status is normal and the display is normal.

[root@shadow:/root 5.7.22-log_Instance1 root@localhost: (none) 16:19:05] > select * from mysql.slave_relay_log_info Show slave status\ Gao MusicaMui + -+-- +-+ | 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 | 1 | | +-+- -- +- +-+-+ 1 row in set (0.00 sec) * * 1. Row * * Slave_IO_State: Waiting for Master to send event Master_Host: 10.10.30.18 Master_User: repl Master_Port: 3306 Connect_Retry: 10 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 3412263 Relay_Log_File: mysql-relay-bin.000013 Relay_Log_Pos: 236672 Relay_Master_Log_File: mysql- Bin.000001 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: 3412263 Relay_Log_Space: 2784908 Until_Condition: None Until_Log_File: Until_Log_Pos: 0Master_SSL _ Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_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: 330618 Master_UUID: 2662c965-fdb2-11e8-8157-0cc47a3aae0d 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: 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101 Executed_Gtid_Set: 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-8164 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:1 row in set (0.00 sec)

View error.log

2018-12-14T16:17:37.822661+08:00 1 [Note] Slave SQL thread for channel''initialized, starting replication in log' mysql-bin.000001' at position 3158537, relaylog'/ home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000011' position: 17878452018-12-14T16:17:37.822915+08:00 2 [Note] Slave I am O thread for channel': connected to master 'repl@10.10.30.18:3306' Replication started in log' mysql-bin.000001' at position 27646132018-12-14T16:17:37.826982+08:00 0 [Note] Event Scheduler: Loaded 0 events2018-12-14T16:17:37.827132+08:00 0 [Note] / usr/local/mysql/bin/mysqld: ready for connections.Version: '5.7.22 socket:' / home/mysql/data/mysqldata1/sock/mysql.sock' port: 3306 MySQL Community Server (GPL) 2018-12-14T16:17:38.020740+08: 00 1 [Note] Slave SQL for channel': Rolling back unfinished transaction (no COMMIT or ROLLBACK in relay log). A probable cause is partial transaction left on relay log because of restarting IO thread with auto-positioning protocol. Error_code: 02018-12-14T16:17:39.639144+08:00 4 [Note] Start binlog_dump to master_thread_id (4) slave_server (330618), pos (4)

Check the information of the next relaylog

$mysqlbinlog-vvv Myql SET SET bin.000012 etc. 50530 SET @ @ SESSION.PSEUDONG SLAVEMETDEN1Universe 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * # at 4#181214 16:17:37 server id 330619 end_log_pos 123 CRC32 0x32f87201 Start: binlog v 4 Server v 5.7.22-log created 181214 16 This Format_description_event appears in a relay log and was generated by the slave thread.# at 1714 16:17:37 server id 330619 end_log_pos 242CRC32 0x524f2179 Previous-GTIDs# 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101 CRC32 0xfe1b4a1f Rotate to mysql-relay-bin.000013 pos: 4SET @ 1509-2079Switzerland 2082-759The at 242 181214 16:17:37 server id 330619 end_log_pos 295 CRC32 0xfe1b4a1f Rotate to mysql-relay-bin.000013 pos: 4SET @ @ SESSION.GTID_NEXT= 'AUTOMATIC' / * added by mysqlbinlog * /! * / DELIMITER; # End of log filebinder "50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/" SET @ @ SESSION.PSEUDO_SLAVE_MODE=0*/

Check the information of the next relaylog and find that the log corresponding to the GTID of 2662c965-fdb2-11e8-8157-0cc47a3aae0d:7600 will indeed be pulled again.

$mysqlbinlog-vvv Myql SET SET bin.000013 lead 50530 SET @ @ SESSION.PSEUDOAs SLAVEMETDE1 / 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;DELIMITER / * / # at 4#181214 16:17:37 server id 330619 end_log_pos 123 CRC32 0x32f87201 Start: binlog v 4 Server v 5.7.22-log created 181214 16 This Format_description_event appears in a relay log and was generated by the slave thread.# at 1714 16:17:37 server id 330619 end_log_pos 242CRC32 0x524f2179 Previous-GTIDs# 2662c965-fdb2-11e8-8157-0cc47a3aae0d:1-101 CRC32 0x073c3e73 Rotate to mysql-bin.000001 pos 1509-2079 This Format_description_event appears in a relay log and was generated by the slave thread.# at 2082-759 "at 242" 700101 8:00:00 server id 330618 end_log_pos 0 CRC32 0x073c3e73 Rotate to mysql-bin.000001 pos # 181214 13:52:24 server id 330618 end_log_pos 123 CRC32 0x0180f777 Start: binlog v 4 Server v 5.7.22-log created 181214 13:52:24BINLOG 'GEUTXA96CwUAdwAAHsAAAAAAQANS43LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # at 408 "181214 16:17:37 server id 0 end_log_pos 455 CRC32 0x340c6196 Rotate to mysql-bin.000001 pos: 15" at 455 "181214 16:17:37 server id 0 end_log_pos 502 CRC32 0x62bfc8f3 Rotate to mysql-bin.000001 pos: 317609" at 502 "181214 16:09:36 server id 330618 end_log_pos 3176158 CRC32 0xeb1649f2 GTID last_committed=7599 sequence_number=7600 rbrachians on laws SET @ @ SESSION.GTID_NEXT= '2662c965-fdb2-11e8-8157-0cc47a3aae0dVR 7600 pedestal, @ at 5671181214 16:09:36 server id 330618 end_log_pos 3176250 CRC32 0x9e2a31e5 Query thread_id=56358 exec_time=0 error_code=0SET timestempany 1544774976 pact set @ @ session.pseudoplastics thread readable IDD 56358plicatrix raceme racer cross set @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.autoprinter SET @ @ session.sqlhammer modewords 1436549152, please set @ session.auto_increment_increment=2, @ @ session.autovariation increments off setbacks! * /; SET @ @ session.session utf8 * / / *! * /; SET @ @ session.session. SET @ @ session.collationalization databases default CRC32 0x920329a0 Rows_query# update qdata_mysql_heartbeat set ts=now SET @ @ session.collationalization databases database server id @ session.collationalization databases database server id @ session.collationalization databases database server id 330618 end_log_pos 3176343 CRC32 0x920329a0 Rows_query# update qdata_mysql_heartbeat set ts=now () where server_id=@@server_id# at 752

| | Summary |

You can see from error.log that after MySQL restarts, Slave SQL thread will go to starting replication according to the location information recorded in the mysql.slave_relay_log_ info table.

[root@shadow:/root 5.7.22-log_Instance1 root@localhost: (none) 16:08:57] > select * from mysql.slave_relay_log_info Show slave status\ Gao MusicaMui + -+-- +-+ | 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 | 1 | | +-+- -- +- +-+-+ 1 row in set (0.00 sec) 2018-12-14T16:17:37.822661+08:00 1 [Note] Slave SQL thread for channel''initialized Starting replication in log 'mysql-bin.000001' at position 3158537, relaylog' / home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000011' position: 1787845

Observe the contents of the error.log log in two cases and find that:

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 >

The Retrieved_Gtid_Set,UNION collection is Executed_Gtid_Set, so the first GTID pulled in the newly generated relaylog is the normal playback of Executed_Gtid_Set 's next GTID,SQL thread.

Under Retrieved_Gtid_Set > Executed_Gtid_Set, the following information is found in error.log. Because when the Executed_Gtid_Set is less than Retrieved_Gtid_Set, the truncated GTID has not been executed, so the SQL thread will try to execute it. If the transaction is found to be incomplete, the SQL thread will perform rollback. And after MySQL starts, the incomplete transaction will be placed in the last_received_GTID, so that the MySQL will pull the incomplete transaction again according to the collection of UNION (@ @ global.gtid_executed, Retrieved_gtid_set-last_received_GTID), and the IO thread will pull the incomplete transaction again. After pulling, the transaction is complete and the SQL thread plays back normally.

2018-12-14T16:17:38.020740+08:00 1 [Note] Slave SQL for channel': Rolling back unfinished transaction (no COMMIT or ROLLBACK in relay log). A probable cause is partial transaction left on relay log because of restarting IO thread with auto-positioning protocol. Error_code: 0 this is the end of the article on "what will happen if relay_log_recovery is set to 0 in mysql". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.