1.首先当主库停止写入时,主库binlog pos的位置不再改变。
"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)
例如这个:
(1)mybinlog.000005 写入到:1440停止。
(2)当前gtid达到53;
我们从备库查看:
"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)
--说明从库已经读取到了主库的mybinlog.000005 pos:1440的位置。
Master_Log_File: mybinlog.000005
Read_Master_Log_Pos: 1440
--表示应用了主库的:1440这个位置。
Exec_Master_Log_Pos: 1440
--获取的GTID的截止时间是53;
Retrieved_Gtid_Set: c29a1b76-77a0-11ee-a26a-000c2959421a:50-53
2.通过binlog和relaylog的对比,确认主备是否同步。
(1)查看主库最后的binlog内容。
"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)
--可以发现主库截止的POS=1440;介质的GTID=53;
(2)从库的relaylog查看。
"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)
可以发现备库的最后一条数据:
(1)事务ID:63;
(2)GTID=53
(3)END_LOG_POS=1440 相同。
3.总结
主从同步的判断依据是主库的binlog的最后一个事务:事务ID,GTID,END_LOG_POS 这三个值
和从库的relaylog 的最后一个事务的:事务ID,GTID,END_LOG_POS相同。
事务ID会随着事务的执行递增,GTID也会随着事务递增。