1. First, when the main library stops writing, the position of the main library binlog pos will no longer change.
"root@localhost Fri Nov 3 22:15:53 2023 22:15:53 [test]">show master status;
+ ----------------- + ---------- + -------------- + ----- ------------- + ------------------------------------ ------- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ ----------------- + ---------- + -------------- + ----- ------------- + ------------------------------------ ------- +
| mybinlog.000005 | 1440 | | | c29a1b76-77a0-11ee-a26a-000c2959421a:1-53 |
+ ----------------- + ---------- + -------------- + ----- ------------- + ------------------------------------ ------- +
1 row in set (0.00 sec)
For example this:
(1) Mybinlog.000005 writes to: 1440 and stops.
(2) The current gtid reaches 53;
We check from the backup library:
"root@localhost Fri Nov 3 22:15:24 2023 22:15:24 [(none)]">show slave status\G
*************************** 1. row ********************* *******
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.7
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mybinlog.000005
Read_Master_Log_Pos: 1440
Relay_Log_File: mysql2-relay-bin.000002
Relay_Log_Pos: 1262
Relay_Master_Log_File: mybinlog.000005
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: 1440
Relay_Log_Space: 1470
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: 73306
Master_UUID: c29a1b76-77a0-11ee-a26a-000c2959421a
Master_Info_File: /data/mysql/data/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: c29a1b76-77a0-11ee-a26a-000c2959421a:50-53
Executed_Gtid_Set: 02283b7e-7a50-11ee-bcd0-000c290123bd:1,
c29a1b76-77a0-11ee-a26a-000c2959421a:1-53
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
--Indicates that the slave library has read the position of mybinlog.000005 pos:1440 of the main library.
Master_Log_File: mybinlog.000005
Read_Master_Log_Pos: 1440
--Indicates that the location: 1440 of the main library is applied.
Exec_Master_Log_Pos: 1440
--The deadline for obtaining the GTID is 53;
Retrieved_Gtid_Set: c29a1b76-77a0-11ee-a26a-000c2959421a:50-53
2. Confirm whether the master and slave are synchronized by comparing the binlog and relaylog.
(1) View the final binlog content of the main library. "root@localhost Fri Nov 3 22:18:20 2023 22:18:20 [test]">show binary logs; + ----------------- + ----------- + | Log_name | File_size | + ----------------- + ----------- + | mybinlog.000001 | 177 | | mybinlog.000002 | 711884780 | | mybinlog.000003 | 217 | | mybinlog.000004 | 217 | | mybinlog.000005 | 1440 | + ----------------- + ----------- + 5 rows in set (0.00 sec) "root@localhost Fri Nov 3 22:22:49 2023 22:22:49 [test]">show binlog events in 'mybinlog.000005'; + ----------------- + ------ + ---------------- + ------- ---- + ------------- + ------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + ----------------- + ------ + ---------------- + ------- ---- + ------------- + ------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- + | mybinlog.000005 | 4 | Format_desc | 73306 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | mybinlog.000005 | 123 | Previous_gtids | 73306 | 194 | c29a1b76-77a0-11ee-a26a-000c2959421a:1-48 | | mybinlog.000005 | 194 | Gtid | 73306 | 259 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:49' | | mybinlog.000005 | 259 | Query | 73306 | 497 | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*A424E797037BF97C19A2E88CF7891C5C2038C039' | | mybinlog.000005 | 497 | Gtid | 73306 | 562 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:50' | | mybinlog.000005 | 562 | Query | 73306 | 649 | use `test`; truncate table test1 | | mybinlog.000005 | 649 | Gtid | 73306 | 714 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:51' | | mybinlog.000005 | 714 | Query | 73306 | 786 | BEGIN | | mybinlog.000005 | 786 | Table_map | 73306 | 837 | table_id: 109 (test.test1) | | mybinlog.000005 | 837 | Write_rows | 73306 | 881 | table_id: 109 flags: STMT_END_F | | mybinlog.000005 | 881 | Xid | 73306 | 912 | COMMIT /* xid=44 */ | | mybinlog.000005 | 912 | Gtid | 73306 | 977 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:52' | | mybinlog.000005 | 977 | Query | 73306 | 1049 | BEGIN | | mybinlog.000005 | 1049 | Table_map | 73306 | 1100 | table_id: 109 (test.test1) | | mybinlog.000005 | 1100 | Write_rows | 73306 | 1145 | table_id: 109 flags: STMT_END_F | | mybinlog.000005 | 1145 | Xid | 73306 | 1176 | COMMIT /* xid=55 */ | | mybinlog.000005 | 1176 | Gtid | 73306 | 1241 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:53' | | mybinlog.000005 | 1241 | Query | 73306 | 1313 | BEGIN | | mybinlog.000005 | 1313 | Table_map | 73306 | 1364 | table_id: 109 (test.test1) | | mybinlog.000005 | 1364 | Write_rows | 73306 | 1409 | table_id: 109 flags: STMT_END_F | | mybinlog.000005 | 1409 | Xid | 73306 | 1440 | COMMIT /* xid=63 */ | + ----------------- + ------ + ---------------- + ------- ---- + ------------- + ------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- + 21 rows in set (0.00 sec) --You can find that the main library's cutoff POS=1440; the media's GTID=53; (2) Check from the relaylog of the library. "root@localhost Fri Nov 3 22:29:38 2023 22:29:38 [(none)]">show relaylog events in 'mysql2-relay-bin.000002'; + ------------------------- + ------ + ---------------- + ---------- + ------------- + ----------------------- ------------------------------------------------ + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + ------------------------- + ------ + ---------------- + ---------- + ------------- + ----------------------- ------------------------------------------------ + | mysql2-relay-bin.000002 | 4 | Format_desc | 83306 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | mysql2-relay-bin.000002 | 123 | Previous_gtids | 83306 | 154 | | | mysql2-relay-bin.000002 | 154 | Rotate | 73306 | 0 | mybinlog.000005;pos=497 | | mysql2-relay-bin.000002 | 200 | Format_desc | 73306 | 0 | Server ver: 5.7.34-log, Binlog ver: 4 | | mysql2-relay-bin.000002 | 319 | Gtid | 73306 | 562 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:50' | | mysql2-relay-bin.000002 | 384 | Query | 73306 | 649 | use `test`; truncate table test1 | | mysql2-relay-bin.000002 | 471 | Gtid | 73306 | 714 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:51' | | mysql2-relay-bin.000002 | 536 | Query | 73306 | 786 | BEGIN | | mysql2-relay-bin.000002 | 608 | Table_map | 73306 | 837 | table_id: 109 (test.test1) | | mysql2-relay-bin.000002 | 659 | Write_rows | 73306 | 881 | table_id: 109 flags: STMT_END_F | | mysql2-relay-bin.000002 | 703 | Xid | 73306 | 912 | COMMIT /* xid=44 */ | | mysql2-relay-bin.000002 | 734 | Gtid | 73306 | 977 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:52' | | mysql2-relay-bin.000002 | 799 | Query | 73306 | 1049 | BEGIN | | mysql2-relay-bin.000002 | 871 | Table_map | 73306 | 1100 | table_id: 109 (test.test1) | | mysql2-relay-bin.000002 | 922 | Write_rows | 73306 | 1145 | table_id: 109 flags: STMT_END_F | | mysql2-relay-bin.000002 | 967 | Xid | 73306 | 1176 | COMMIT /* xid=55 */ | | mysql2-relay-bin.000002 | 998 | Gtid | 73306 | 1241 | SET @@SESSION.GTID_NEXT= 'c29a1b76-77a0-11ee-a26a-000c2959421a:53' | | mysql2-relay-bin.000002 | 1063 | Query | 73306 | 1313 | BEGIN | | mysql2-relay-bin.000002 | 1135 | Table_map | 73306 | 1364 | table_id: 109 (test.test1) | | mysql2-relay-bin.000002 | 1186 | Write_rows | 73306 | 1409 | table_id: 109 flags: STMT_END_F | | mysql2-relay-bin.000002 | 1231 | Xid | 73306 | 1440 | COMMIT /* xid=63 */ | + ------------------------- + ------ + ---------------- + ---------- + ------------- + ----------------------- ------------------------------------------------ + 21 rows in set (0.00 sec) You can find the last piece of data in the standby database: (1) Transaction ID: 63; (2)GTID=53 (3)END_LOG_POS=1440 is the same.
3. Summary
The judgment of master-slave synchronization is based on the last transaction of the binlog of the master library: transaction ID, GTID, and END_LOG_POS.
It is the same as the transaction ID, GTID, and END_LOG_POS of the last transaction in the relaylog of the slave library.
The transaction ID will be incremented with the execution of the transaction, and the GTID will also be incremented with the transaction.