欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL主库binlog(master-log)与从库relay-log关系代码详解

程序员文章站 2022-11-16 21:49:17
主库binlog: # at 2420 #170809 17:16:20 server id 1882073306 end_log_pos 2451 crc...

主库binlog:

# at 2420
#170809 17:16:20 server id 1882073306 end_log_pos 2451 crc32 0x58f2db87  xid = 32880
commit/*!*/;
# at 2451
#170814 11:07:18 server id 1882073306 end_log_pos 2528 crc32 0x40774a4b  query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680038/*!*/;
begin
/*!*/;
# at 2528
# at 2560
#170814 11:07:18 server id 1882073306 end_log_pos 2560 crc32 0x7bdf274b  intvar
set insert_id=107/*!*/;
#170814 11:07:18 server id 1882073306 end_log_pos 2669 crc32 0x68e441c8  query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680038/*!*/;
insert into t2 (name) values ('a100')
/*!*/;
# at 2669
# at 2701
#170814 11:07:27 server id 1882073306 end_log_pos 2701 crc32 0xcf89b910  intvar
set insert_id=108/*!*/;
#170814 11:07:27 server id 1882073306 end_log_pos 2810 crc32 0x78466d7b  query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680047/*!*/;
insert into t2 (name) values ('a200')
/*!*/;
# at 2810
# at 2842
#170814 11:07:30 server id 1882073306 end_log_pos 2842 crc32 0x1e5a0847  intvar
set insert_id=109/*!*/;
#170814 11:07:30 server id 1882073306 end_log_pos 2951 crc32 0xebeb947c  query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680050/*!*/;
insert into t2 (name) values ('a300')
/*!*/;
# at 2951
#170814 11:07:34 server id 1882073306 end_log_pos 2982 crc32 0x6436ad60  xid = 32934
commit/*!*/;

从库relay-log:

/*!50530 set @@session.pseudo_slave_mode=1*/;
/*!40019 set @@session.max_insert_delayed_threads=0*/;
/*!50003 set @old_completion_type=@@completion_type,completion_type=0*/;
delimiter /*!*/;
# at 4
#170809 17:17:24 server id 1882083306 end_log_pos 120 crc32 0x5df4221c  start: binlog v 4, server v 5.6.23-72.1-log created 170809 17:17:24
# at 120
#700101 8:00:00 server id 1882073306 end_log_pos 0 crc32 0x0b8a412f rotate to test-mysql-bin.000116 pos: 2451
# at 172
#170809 16:28:12 server id 1882073306 end_log_pos 0 crc32 0xd0d3bf30 start: binlog v 4, server v 5.6.23-72.1-log created 170809 16:28:12
# at 288
#170814 11:07:18 server id 1882073306 end_log_pos 2528 crc32 0x40774a4b  query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680038/*!*/;
set @@session.pseudo_thread_id=92/*!*/;
set @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
set @@session.sql_mode=1073741824/*!*/;
set @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\c utf8 *//*!*/;
set @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
set @@session.lc_time_names=0/*!*/;
set @@session.collation_database=default/*!*/;
begin
/*!*/;
# at 365
# at 397
#170814 11:07:18 server id 1882073306 end_log_pos 2560 crc32 0x7bdf274b  intvar
set insert_id=107/*!*/;
#170814 11:07:18 server id 1882073306 end_log_pos 2669 crc32 0x68e441c8  query thread_id=92 exec_time=0 error_code=0
use `db1`/*!*/;
set timestamp=1502680038/*!*/;
insert into t2 (name) values ('a100')
/*!*/;
# at 506
# at 538
#170814 11:07:27 server id 1882073306 end_log_pos 2701 crc32 0xcf89b910  intvar
set insert_id=108/*!*/;
#170814 11:07:27 server id 1882073306 end_log_pos 2810 crc32 0x78466d7b  query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680047/*!*/;
insert into t2 (name) values ('a200')
/*!*/;
# at 647
# at 679
#170814 11:07:30 server id 1882073306 end_log_pos 2842 crc32 0x1e5a0847  intvar
set insert_id=109/*!*/;
#170814 11:07:30 server id 1882073306 end_log_pos 2951 crc32 0xebeb947c  query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680050/*!*/;
insert into t2 (name) values ('a300')
/*!*/;
# at 788
#170814 11:07:34 server id 1882073306 end_log_pos 2982 crc32 0x6436ad60  xid = 32934
commit/*!*/;

注意relay log的这一行:

#700101  8:00:00 server id 1882073306  end_log_pos 0 crc32 0x0b8a412f  rotate to test-mysql-bin.000116  pos: 2451

说明此relay log保存的是主库 test-mysql-bin.000116 的信息,从position 2451 开始。

看一个具体的对应关系:

主库的binlog如下:

# at 2560
#170814 11:07:18 server id 1882073306 end_log_pos 2560 crc32 0x7bdf274b intvar
set insert_id=107/*!*/;
#170814 11:07:18 server id 1882073306 end_log_pos 2669 crc32 0x68e441c8 query thread_id=92 exec_time=0 error_code=0
set timestamp=1502680038/*!*/;
insert into t2 (name) values ('a100')
/*!*/;
# at 2669

对应从库relay-log如下几行:

# at 397
#170814 11:07:18 server id 1882073306 end_log_pos 2560 crc32 0x7bdf274b  intvar
set insert_id=107/*!*/;
#170814 11:07:18 server id 1882073306 end_log_pos 2669 crc32 0x68e441c8  query thread_id=92 exec_time=0 error_code=0
use `db1`/*!*/;
set timestamp=1502680038/*!*/;
insert into t2 (name) values ('a100')
/*!*/;
# at 506

另外注意show slave status\g的以下几行的关系:

master_log_file: test-mysql-bin.000117
read_master_log_pos: 774

上面二行代表io线程,相对于主库

relay_log_file: relay-log.000038
relay_log_pos: 723

上面二行代表了sql线程,相对于从库

relay_master_log_file: test-mysql-bin.000117
exec_master_log_pos: 555

上面二行代表了sql线程,相对主库

其中relay_log_pos: 723 和 exec_master_log_pos: 555 对应的sql语句一致。

总结

以上就是本文关于mysql主库binlog与从库relay-log关系代码详解的全部内容,希望对大家有所帮助。感兴趣的朋友可以参阅:、几个比较重要的mysql变量mysql prepare原理详解等,有什么问题可以随时留言,欢迎大家交流讨论。