Detailed explanation of the code between the MySQL master library binlog (master-log) and the slave library relay-log
|
Main library 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 /*!*/;
From the library 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 /*!*/;
Note this line in the relay log: #700101 8:00:00 server id 1882073306 end_log_pos 0 CRC32 0x0b8a412f Rotate to test-mysql-bin.000116 pos: 2451 This indicates that the relay log stores the information of the main database test-mysql-bin.000116, starting from position 2451. Let’s look at a specific correspondence: The binlog of the main library is as follows:
# 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
The corresponding slave library relay-log has the following lines:
# 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
Also note the relationship between the following lines of show slave status\G: Master_Log_File: test-mysql-bin.000117 Read_Master_Log_Pos: 774 The two lines above represent IO threads, relative to the main library Relay_Log_File: relay-log.000038 Relay_Log_Pos: 723 The two lines above represent the sql thread, relative to the slave library Relay_Master_Log_File: test-mysql-bin.000117 Exec_Master_Log_Pos: 555 The two lines above represent the sql thread, relative to the main library The SQL statements corresponding to Relay_Log_Pos: 723 and Exec_Master_Log_Pos: 555 are consistent. Summarize The above is all the content of this article about the detailed code explanation of the relationship between MySQL master library binlog and slave library relay-log. I hope it will be helpful to everyone. Interested friends can refer to: Detailed analysis of binlog_format mode and configuration in MySQL, Several important MySQL variables, Detailed explanation of MySQL prepare principle, etc. If you have any questions, you can leave a message at any time. Everyone is welcome to communicate and discuss. You may also be interested in:
|
<<: JS implements simple example code to control video playback speed
>>: How to block and prohibit web crawlers in Nginx server
Recommend
Detailed explanation of Mysql 5.7.18 installation method and the process of starting MySQL service
MySQL is a very powerful relational database. How...
CSS3 achieves conic-gradient effect
grammar: background-image: conic-gradient(from an...
JS realizes video barrage effect
Use ES6 modular development and observer mode to ...
Solve the problem that await does not work in forEach
1. Introduction A few days ago, I encountered a p...
Detailed explanation of Vue Notepad example
This article example shares the specific code of ...
How to run Hadoop and create images in Docker
Reinventing the wheel, here we use repackaging to...
HTML form component example code
HTML forms are used to collect different types of...
Vue2.x - Example of using anti-shake and throttling
Table of contents utils: Use in vue: explain: Ima...
Several ways to remove the dotted box that appears when clicking a link
Here are a few ways to remove it: Add the link dir...
Linux MySQL root password forgotten solution
When using the MySQL database, if you have not lo...
Example code for implementing photo stacking effect with CSS
Achieve results step 1. Initial index.html To bui...
JavaScript code to implement a simple calculator
This article example shares the specific code of ...
A brief description of the relationship between k8s and Docker
Recently, the project uses kubernetes (hereinafte...
Getting Started Tutorial on Using TS (TypeScript) in Vue Project
Table of contents 1. Introducing Typescript 2. Co...