主庫(kù)mysql/270371.html">binlog:
# at 2420#170809 17:16:20 server id 1882073306 end_log_pos 2451 CRC32 0x58f2db87 Xid = 32880COMMIT/*!*/;# at 2451#170814 11:07:18 server id 1882073306 end_log_pos 2528 CRC32 0x40774a4b Query thread_id=92 exec_time=0 error_code=0SET TIMESTAMP=1502680038/*!*/;BEGIN/*!*/;# at 2528# at 2560#170814 11:07:18 server id 1882073306 end_log_pos 2560 CRC32 0x7bdf274b IntvarSET 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=0SET 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 IntvarSET 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=0SET 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 IntvarSET 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=0SET TIMESTAMP=1502680050/*!*/;insert into t2 (name) values ('a300')/*!*/;# at 2951#170814 11:07:34 server id 1882073306 end_log_pos 2982 CRC32 0x6436ad60 Xid = 32934COMMIT/*!*/;從庫(kù)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=0SET 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 IntvarSET 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=0use `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 IntvarSET 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=0SET 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 IntvarSET 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=0SET TIMESTAMP=1502680050/*!*/;insert into t2 (name) values ('a300')/*!*/;# at 788#170814 11:07:34 server id 1882073306 end_log_pos 2982 CRC32 0x6436ad60 Xid = 32934COMMIT/*!*/;注意relay log的這一行：
#700101 8:00:00 server id 1882073306 end_log_pos 0 CRC32 0x0b8a412f Rotate to test-mysql-bin.000116 pos: 2451
說(shuō)明此relay log保存的是主庫(kù) test-mysql-bin.000116 的信息，從position 2451 開始。
看一個(gè)具體的對(duì)應(yīng)關(guān)系：
主庫(kù)的binlog如下：
# at 2560#170814 11:07:18 server id 1882073306 end_log_pos 2560 CRC32 0x7bdf274b IntvarSET 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=0SET TIMESTAMP=1502680038/*!*/;insert into t2 (name) values ('a100')/*!*/;# at 2669對(duì)應(yīng)從庫(kù)relay-log如下幾行：
# at 397#170814 11:07:18 server id 1882073306 end_log_pos 2560 CRC32 0x7bdf274b IntvarSET 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=0use `db1`/*!*/;SET TIMESTAMP=1502680038/*!*/;insert into t2 (name) values ('a100')/*!*/;# at 506另外注意show slave status/G的以下幾行的關(guān)系：
Master_Log_File: test-mysql-bin.000117Read_Master_Log_Pos: 774
上面二行代表IO線程,相對(duì)于主庫(kù)
Relay_Log_File: relay-log.000038Relay_Log_Pos: 723
上面二行代表了sql線程,相對(duì)于從庫(kù)
Relay_Master_Log_File: test-mysql-bin.000117Exec_Master_Log_Pos: 555
上面二行代表了sql線程,相對(duì)主庫(kù)
其中Relay_Log_Pos: 723 和 Exec_Master_Log_Pos: 555 對(duì)應(yīng)的sql語(yǔ)句一致。
總結(jié)
以上就是本文關(guān)于MySQL主庫(kù)binlog與從庫(kù)relay-log關(guān)系代碼詳解的全部?jī)?nèi)容，希望對(duì)大家有所幫助。有什么問(wèn)題可以隨時(shí)留言，歡迎大家交流討論。
新聞熱點(diǎn)
疑難解答
圖片精選
