MySQL主库binlog(master-log)与从库relay-log关系代码详解
主库binlog:
#at2420
#17080917:16:20serverid1882073306end_log_pos2451CRC320x58f2db87Xid=32880
COMMIT/*!*/;
#at2451
#17081411:07:18serverid1882073306end_log_pos2528CRC320x40774a4bQuerythread_id=92exec_time=0error_code=0
SETTIMESTAMP=1502680038/*!*/;
BEGIN
/*!*/;
#at2528
#at2560
#17081411:07:18serverid1882073306end_log_pos2560CRC320x7bdf274bIntvar
SETINSERT_ID=107/*!*/;
#17081411:07:18serverid1882073306end_log_pos2669CRC320x68e441c8Querythread_id=92exec_time=0error_code=0
SETTIMESTAMP=1502680038/*!*/;
insertintot2(name)values('a100')
/*!*/;
#at2669
#at2701
#17081411:07:27serverid1882073306end_log_pos2701CRC320xcf89b910Intvar
SETINSERT_ID=108/*!*/;
#17081411:07:27serverid1882073306end_log_pos2810CRC320x78466d7bQuerythread_id=92exec_time=0error_code=0
SETTIMESTAMP=1502680047/*!*/;
insertintot2(name)values('a200')
/*!*/;
#at2810
#at2842
#17081411:07:30serverid1882073306end_log_pos2842CRC320x1e5a0847Intvar
SETINSERT_ID=109/*!*/;
#17081411:07:30serverid1882073306end_log_pos2951CRC320xebeb947cQuerythread_id=92exec_time=0error_code=0
SETTIMESTAMP=1502680050/*!*/;
insertintot2(name)values('a300')
/*!*/;
#at2951
#17081411:07:34serverid1882073306end_log_pos2982CRC320x6436ad60Xid=32934
COMMIT/*!*/;
从库relay-log:
/*!50530SET@@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019SET@@session.max_insert_delayed_threads=0*/;
/*!50003SET@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER/*!*/;
#at4
#17080917:17:24serverid1882083306end_log_pos120CRC320x5df4221cStart:binlogv4,serverv5.6.23-72.1-logcreated17080917:17:24
#at120
#7001018:00:00serverid1882073306end_log_pos0CRC320x0b8a412fRotatetotest-mysql-bin.000116pos:2451
#at172
#17080916:28:12serverid1882073306end_log_pos0CRC320xd0d3bf30Start:binlogv4,serverv5.6.23-72.1-logcreated17080916:28:12
#at288
#17081411:07:18serverid1882073306end_log_pos2528CRC320x40774a4bQuerythread_id=92exec_time=0error_code=0
SETTIMESTAMP=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/*!*/;
/*!\Cutf8*//*!*/;
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
/*!*/;
#at365
#at397
#17081411:07:18serverid1882073306end_log_pos2560CRC320x7bdf274bIntvar
SETINSERT_ID=107/*!*/;
#17081411:07:18serverid1882073306end_log_pos2669CRC320x68e441c8Querythread_id=92exec_time=0error_code=0
use`db1`/*!*/;
SETTIMESTAMP=1502680038/*!*/;
insertintot2(name)values('a100')
/*!*/;
#at506
#at538
#17081411:07:27serverid1882073306end_log_pos2701CRC320xcf89b910Intvar
SETINSERT_ID=108/*!*/;
#17081411:07:27serverid1882073306end_log_pos2810CRC320x78466d7bQuerythread_id=92exec_time=0error_code=0
SETTIMESTAMP=1502680047/*!*/;
insertintot2(name)values('a200')
/*!*/;
#at647
#at679
#17081411:07:30serverid1882073306end_log_pos2842CRC320x1e5a0847Intvar
SETINSERT_ID=109/*!*/;
#17081411:07:30serverid1882073306end_log_pos2951CRC320xebeb947cQuerythread_id=92exec_time=0error_code=0
SETTIMESTAMP=1502680050/*!*/;
insertintot2(name)values('a300')
/*!*/;
#at788
#17081411:07:34serverid1882073306end_log_pos2982CRC320x6436ad60Xid=32934
COMMIT/*!*/;
注意relaylog的这一行：
#700101 8:00:00serverid1882073306 end_log_pos0CRC320x0b8a412f Rotatetotest-mysql-bin.000116 pos:2451
说明此relaylog保存的是主库test-mysql-bin.000116的信息，从position2451开始。
看一个具体的对应关系：
主库的binlog如下：
#at2560
#17081411:07:18serverid1882073306end_log_pos2560CRC320x7bdf274bIntvar
SETINSERT_ID=107/*!*/;
#17081411:07:18serverid1882073306end_log_pos2669CRC320x68e441c8Querythread_id=92exec_time=0error_code=0
SETTIMESTAMP=1502680038/*!*/;
insertintot2(name)values('a100')
/*!*/;
#at2669
对应从库relay-log如下几行：
#at397
#17081411:07:18serverid1882073306end_log_pos2560CRC320x7bdf274bIntvar
SETINSERT_ID=107/*!*/;
#17081411:07:18serverid1882073306end_log_pos2669CRC320x68e441c8Querythread_id=92exec_time=0error_code=0
use`db1`/*!*/;
SETTIMESTAMP=1502680038/*!*/;
insertintot2(name)values('a100')
/*!*/;
#at506
另外注意showslavestatus\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中binlog_format模式与配置详细分析、几个比较重要的MySQL变量、MySQLprepare原理详解等，有什么问题可以随时留言，欢迎大家交流讨论。