show slave status\G中的Read_Master_Log_Pos和Relay_Log_Pos的(大小)关系

本文详细解析了MySQL主从复制中的三个关键位置坐标:主库的I/O线程读取位置、从库的SQL线程执行位置及相对主库的位置,并通过实际日志对比验证了一致性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Just to clarify, there are three sets of file/position coordinates in SHOW SLAVE STATUS:

1) The position, ON THE MASTER, from which the I/O thread is reading:Master_Log_File/Read_Master_Log_Pos. -----相对于主库,从库读取主库的二进制日志的位置,是IO线程

2) The position, IN THE RELAY LOGS, at which the SQL thread is executing:Relay_Log_File/Relay_Log_Pos ----相对于从库,是从库的sql线程执行到的位置

3) The position, ON THE MASTER, at which the SQL thread is executing:Relay_Master_Log_File/Exec_Master_Log_Pos ----相对于主库,是从库的sql线程执行到的位置

Numbers 2) and 3) are the same thing, but one is on the slave and the other is on the master.

mysql > show slave status \G

Master_Log_File: mysql-bin-m.000329

Read_Master_Log_Pos: 863952156 ----上面二行代表IO线程,相对于主库的二进制文件

 

Relay_Log_File: mysql-relay.003990

Relay_Log_Pos: 25077069 ----上面二行代表了sql线程,相对于从库的中继日志文件

Relay_Master_Log_File: mysql-bin-m.000329

.....

Exec_Master_Log_Pos: 863936961---上面二行代表了sql线程,相对主库

(为了方便演示,我把上面这行提前了.)

Relay_Log_Space: 25092264---当前relay-log文件的大小

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

从上面可以看到,read_master_log_pos 始终会大于exec_master_log_pos的值(也有可能相等):因为一个值是代表io线程,一个值代表sql线程;sql线程肯定在io线程之后.(当然,io线程和sql线程要读写同一个文件,否则比较就失去意义了) .

在binlog中,Xid代表了提交的事务号.现在我们分别去主从库看看,验证一下,在主库的mysql-bin-m.000329文件的863936961处是否与从库的mysql-relay.003990文件的25077069处有相同的sql语句.

先看主库的binlog:

# at 863936961

#100111 20:11:39 server id 115000 end_log_pos 863937234 Query thread_id=515886 exec_time=0 error_code=0

use mall00/*!*/;

UPDATE mall00.t_item_sid88 SET item_end_time = 1263816699, item_is_online = 1, item_status = 1 WHERE iid IN (94322390, 94322428, 94322452, 94322473, 94322506, 94322532, 94322604, 94322641, 94322670, 94322706)/*!*/;

# at 863937234

#100111 20:11:39 server id 115000 end_log_pos 863937261 Xid = 1225244590

COMMIT/*!*/;

# at 863937261

#100111 20:11:39 server id 115000 end_log_pos 863937457 Query thread_id=515886 exec_time=0error_code=0

SET TIMESTAMP=1263211899/*!*/;

再看从库的relaylog:

# at 25077069

#100111 20:11:39 server id 115000 end_log_pos863937234 Query thread_id=515886 exec_time=0 error_code=0

use mall00/*!*/;

UPDATE mall00.t_item_sid88 SET item_end_time = 1263816699, item_is_online = 1, item_status = 1 WHERE iid IN (94322390, 94322428, 94322452, 94322473, 94322506, 94322532, 94322604, 94322641, 94322670, 94322706)/*!*/;

# at 25077342

#100111 20:11:39 server id 115000 end_log_pos 863937261 Xid = 1225244590

COMMIT/*!*/;

从上面的日志中,可以看到binlog与realy-log的内容是相同的,除了开头的at位置处的偏移量.因为偏移量始终是相对于文件自身来说,主库上相对于binlog本身,从库上相对于relay-log本身.还可以看到,在每个query语句过后,都有一个Xid 的event,提交该事务,也表明在mysql中是自动提交的,每条语句执行完毕后,系统都自动提交了.那么在基于行的复制中,可能会看到多条binlog 语句才对应一次commit,自然说明这是基于行的复制.

还有一点,就是主库和从库的对应位置的event大小是相同的,例如上面的:

25077342-25077069(从库上event大小)  =  863937234-863936961(主库上event大小)

否则,说明从库的relay-log丢失了,有可能是操作系统缓存丢失,也可能是mysql 异常crash导致relay-log buffer中的数据丢失.那么这时候就需要重设主从同步了.

system@shenxjdb02-0-247 16:35: [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.246 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mybinlog.001566 Read_Master_Log_Pos: 430905430 Relay_Log_File: shenxjdb02-0-247-relay-bin.000952 Relay_Log_Pos: 430905563 Relay_Master_Log_File: mybinlog.001566 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: 430905430 Relay_Log_Space: 2345396484 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: 3306246 Master_UUID: 3bc472a2-16c9-11ec-8b00-525400e48f9b 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: 3bc472a2-16c9-11ec-8b00-525400e48f9b:395075785-451575754 Executed_Gtid_Set: 3bc472a2-16c9-11ec-8b00-525400e48f9b:1-451575754, 848ec0f4-2ac4-11ef-9d43-34735a9de160:1, 94c1953b-16cb-11ec-adcb-525400de8e5d:1-66234841 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, 1 warning (0.01 sec) 什么意思
最新发布
08-08
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值