Analysis of MySQL binary logs

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. Www.2cto.com 1. binary log structure binary log is a series of binary log events (also known as binlog events). In fact, many files, including a series of log files and a log index file, are combined to form binary logs, 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_po S | 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 The set (0.00 sec) www.2cto.com Format_description event contains the server information for writing log files and the log file format. The Rotate event contains the file name of the next log file and the location where it starts reading. 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) www.2cto.com 2. Record statement traditional MySQL adopts statement-based replication to write the actually executed statements and some execution-related information into the binary log together, then execute these statements again 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 www.2cto.com [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) | assign a value for another variable. The type is the seed of the User var www.2cto.com 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.