Analysis of MySQL binary log _ MySQL

Source: Internet
Author: User
Analysis of MySQL binary log
Analysis of MySQL binary logs is generally used for database synchronization. because binary logs record all changes in the database, SLAVE can perform the same update, in fact, binary logs can be used for writing and playing back the database, so they can also be used for other purposes such as statistics or instant recovery. Binary logs only contain statements that may change the database. it is easy to understand this. However, statements that have not changed and may change the database are also recorded, for example, drop table if exists or UPDATE and DELETE statements with WHERE conditions. 1. binary log structure binary logs are a series of binary log events (also called binlog events). they are actually binary logs composed of many files, including a series of log files and a log index file, each log file is called a binlog file. each log file is composed of multiple log events. each log file starts with the Format_description event and ends with the log rotation event Rotate as the file, for example: [SQL] mysql> show binlog events in 'Master-bin.000003 '; + ------------------- + ----- + ------------- + ----------- + Region + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +- ------------------ + ----- + ------------- + ----------- + ------------- + Master-bin.000003 + | 4 | Format_desc | 1 | 106 | Server ver: 5.1.34-log, Binlog ver: 4 | master-bin.000003 | 106 | Rotate | 1 | 150 | master-bin.000004; pos = 4 | + ------------------- + ----- + ------------- + ----------- + ------------- + rows + 2 rows in set (0.00 sec The Format_description event contains the server information of the log file and the log file format. The Rotate event contains the file name of the next log file and the location where it starts to be read. In addition to these two events, all other events in the log file are divided into one group and one group. in the transaction storage engine, each group corresponds to one transaction, the other may be a statement. In short, the event in the log file is either a single statement or a transaction composed of multiple statements. There are many types of events, that is, the above Event_type has multiple values in actual use, but it can be summarized as each log event consists of three parts: the general header. This part of information is the information of all events, including some basic information, such as the event type and event size. for example, you can calculate the statement size from Pos and End_log_pos. Submission header. This information is related to specific event types. Event body. This part of information stores the main data of an event, which varies with the event type. for example, when an event is a Query, the Query statement is stored. As follows: [SQL] | master-bin.000004 | 180 | Query | 1 | 297 | use 'DB _ info'; insert into I _node (name, value) values ("SQL", @ value) II. record statements traditional MySQL uses statement-based replication to write the actual statements and some execution-related information into the binary log, and then re-execute these statements on the slave database. Because binary logs are written into data by multiple threads, it is very important for synchronization to avoid simultaneous updates by two threads. Therefore, a mutex lock must be obtained before the event is written to binary logs, then release the lock after the event is completed. Next we will discuss which data will be written into binary log 2.1. this is usually the DELETE, INSERT, and UPDATE statements. When these statements are executed, they usually write binary logs when the execution statement has a write lock, and then release the lock after the log write operation is complete, this ensures that the update information caused by binary logs and statements is consistent. 2.2 Data definition languages such as create table and alter table. 2.3 The Query statement type is a Query event, which is also the most common event. it is used to store statements executed on the master database. in addition to actually executed statements, this event also contains additional information. For example, if the field AUTO_INCREMENT is included in a row of data, run the following command to write the data and see what events are added to the log event: run the following statement: [SQL] insert into I _node (name, value) values ("SQL", "copy "); we can get two more log events [SQL] | master-bin.000004 | 451 | Intvar | 1 | 479 | INSERT_ID = 12 | master-bin.000004 | 479 | Query | 1 | 596 | use 'DB _ info '; insert into I _node (name, value) values ("SQL", "copy") | + ------------------- + ----- + --------------- + ----------- + ------------- + ---------------- ------------------------------------------------------------- + In addition, there are other context information that will affect the results of the current execution. these are the implicit information that needs to be known during MySQL execution. For example, the current database. We can see that when I execute insert, the use db_info statement is not executed, but it is also recorded by log events. Because I initially executed the statement, MYSQL uses the current database to execute the statement later. User-defined variable value. For example, after I run the following two statements, [SQL] mysql> set @ value = 'copy-on-write'; Query OK, 0 rows affected (0.00 sec) mysql> insert into I _node (name, value) values ("SQL", @ value); Query OK, 1 row affected (0.00 sec), you can see the following log events: [SQL] | master-bin.000004 | 596 | Intvar | 1 | 624 | INSERT_ID = 13 | master-bin.000004 | 624 | User var | 1 | 675 | @ 'value' = _ latin1 limit COLLATE latin1_swedish_ci | maste R-bin.000004 | 675 | Query | 1 | 792 | use 'DB _ info'; insert into I _node (name, value) values ("SQL", @ value) | adds a variable value assignment operation. the type is the seed of the User var RAND () function. When a random number is executed, the random number is not recorded, and the number of seeds is recorded. Current time. Insert value of the AUTO_INCREMENT field, which is a context because it is related to the previous row. LAST_INSERT_ID function. Thread ID. call the client_id function.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.