MySQL主库binlog(master-log)与从库relay-log关系代码详解
网络编程 2021-07-05 14:36www.168986.cn编程入门
这篇文章主要介绍了MySQL主库binlog与从库relay-log关系的相关内容，涉及部分代码，需要的朋友可以参考。
主库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.automit=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关系代码详解的全部内容，希望对大家有所帮助。感兴趣的朋友可以参阅、、等，有什么问题可以随时留言，欢迎大家交流讨论。
编程语言
- 如何快速学会编程 如何快速学会ug编程
- 免费学编程的app 推荐12个免费学编程的好网站
- 电脑怎么编程：电脑怎么编程网咯游戏菜单图标
- 如何写代码新手教学 如何写代码新手教学手机
- 基础编程入门教程视频 基础编程入门教程视频华
- 编程演示：编程演示浦丰投针过程
- 乐高编程加盟 乐高积木编程加盟
- 跟我学plc编程 plc编程自学入门视频教程
- ug编程成航林总 ug编程实战视频
- 孩子学编程的好处和坏处
- 初学者学编程该从哪里开始 新手学编程从哪里入
- 慢走丝编程 慢走丝编程难学吗
- 国内十强少儿编程机构 中国少儿编程机构十强有
- 成人计算机速成培训班 成人计算机速成培训班办
- 孩子学编程网上课程哪家好 儿童学编程比较好的
- 代码编程教学入门软件 代码编程教程