BINLOG File Analysis and modification of mysqlbinlog Tool

BINLOG and mysqlbinlog tools

Problem

This article focuses on the following issues:

1. What is BINLOG?

2. What formats does BINLOG have?

3. Analyze a typical BINLOG to describe the information that can be obtained from the BINLOG.

4. How to modify mysqlbinlog so that the last record can be displayed

Here we mainly consider the use of BINLOG and related formats, rather than the time when writing BINLOG.

 

I. Introduction

BINLOG is also called a binary log file. It records all queries that modify Database Data in MySQL in binary format, such as create, insert, drop, and update; (the select operation is not recorded in the BINLOG because it does not modify the database data ). BINLOG is generally stored in the data directory and named mysql-bin. * ** (you can modify my. CNF: Log-bin = mysql-bin, which is the prefix of the file name. mysqld adds a digital extension after each BINLOG name. Each time you start the server or refresh the log, the file size is greater than max_binlog_size, and a transaction will not be split ).

BINLOG is mainly used to ensure data integrity, such as master-slave backup. The same operation is performed on the salve machine by reading from the BINLOG file to ensure master-slave backup, of course, it is impossible to redo from the start point every time, so each record has a time to cut the timestamp.

 

Ii. simple use of BINLOG

Show binary logs; # display BINLOG files

Purge binary logsto 'mysql-bin. ** '# Delete to ** File

Bin/mysqlbinlog binlogfile # parse the BINLOG File

 

Use BINLOG to restore data:

Bin/mysqlbinlog -- start-datetime = '2017-7-7 20:07:13 '-- stop-datetime = '2017-7-7' Data/mysql-bin.000008 | mysql-u Root

 

Iii. Type

There are three BINLOG formats, which also reflect the MySQL replication technology: SQL statement-based replication (SBR), row-based replication (Row-based replication, (RBR), hybrid replication (MBR ). Correspondingly, there are three BINLOG formats: Statement, row, and mixed.

Mysql> showvariables like 'binlog _ format' # view the BINLOG format

 

BINLOG parsed using mysqlbinlog:

Mixed (statement ):

# At 193 (start position)

#110708 10:03:06 (Time cut) server ID (service ID that generates this event) 1 end_log_pos (end position of the log) 280 query (Event Type) thread_id = 10 exec_time = 0 error_code = 0

Settimestamp = 1310090586 /*! */;

Insert into tvalues (17)

/*! */;

 

Row mode:

Begin

/*! */;

# At 174

# At 214

#110708 10: 49: 22 server Id 1 end_log_pos 214 table_map: 'test'. 't'ed ed to number 14

#110708 10: 49: 22 server Id 1 end_log_pos 248 write_rows: Table ID 14 flags: stmt_end_f

 

Binlog'

Mnawthmbaaaakaaaanyaaaaaaa4aaaaaeabhrlc3qaaxqaaqmaaq =

Mnawthcbaaaaigaaapgaaaaaaaaa4aaaaaeaaf/+ mgaaaa =

'/*! */;

# At 248

#110708 10: 49: 22 server Id 1 end_log_pos 317 query thread_id = 1 exec_time = 0 error_code = 0

Settimestamp = 1310093362 /*! */;

Commit

 

Statement is a SQL statement-level BINLOG. Each SQL statement that modifies data is saved to the BINLOG. row is a row-level SQL statement that records changes in each row, the modification of each row is recorded in the BINLOG. The record is very detailed, but the SQL statement is not in the BINLOG, in replication, data inconsistency between master and slave is not caused by stored procedure triggers. However, there is a fatal drawback that the log volume is large. to record data changes in each row, when the where condition is not followed by the update statement or the alter table statement is executed, the generated log volume is quite large. Mixed: It is statement by default, but it will switch to the row State in some cases, such as when a DML updates An NDB engine table or a function related to the time user. In the case of Master/Slave, if the statement mode is used on the host, BINLOG writes now () directly. However, if so, the time for the slave to perform operations is as follows, also execute now (), but obviously these two times won't be the same, so in this case, you must change the statement mode to the row mode, because the row mode directly writes values instead of writing statements (this case is incorrect. Even in the statement mode, you can use the now () function. The specific cause will be analyzed later ). Similarly, the row mode can reduce the number of calculations related to the slave machine. For example, when there are statistics writes and other operations in the host, the slave machine can avoid this calculation and directly write the value to the slave machine.

 

Iv. BINLOG records

Each BINLOG starts with a magic number consisting of four bytes: Fe 62 69 6e (the last three bytes are bin ).

Next, the content of a record includes: Common-header. The size of this part is different for different versions, and more than 4.0 is 19 bytes. After this is the body.

Common-Header Format: (unit: bytes)

Timestamp (4)

Type (1)

Server_id (4)

Total_size (4)

End_log_pos (4)

Flag (2)

Timestamp: Starting from 1970

Type: the type of this log event, such as format_description_event, query, and load_event. the type of the first record of each BINLOG is format_description_event, which records information about this BINLOG, such as the version, this information is useful for post-Order Analysis of BINLOG records. Therefore, tools for tasks to read BINLOG content must first read the first record. Query includes frequently-used operations such as create, drop, update, and insert.

Server_id: the server ID of the event. Prevent the master and slave nodes from being written from the slave node. The master's server ID (is preserved in therelay log; used to prevent from infinite loops in circular replication ).

Total_size: the size of the record, including common_header and body.

End_log_pos: Start position of the next record. It is also the last byte at the end of the record.

Flag: flag.

 

Query type record:

In addition to the starting common-header, a query-type record starts with a post-header, and is followed by the actual body content.

Query post-header (in bytes)

Thread_id (4)

Exec_time (4)

Db_len (1)

Error_code (2)

Status_var_len (2)

Thread_id: is used to distinguish temporary tables that belong to differentclients.

Exec_time: The time from whenthe query started to when it was logged in the BINLOG, in seconds. query to the time interval when the BINLOG event is generated.

Db_len: The name length of the current database.

Error_code: Error Code returned when an error occurs.

 

5. Use mysqlbinlog to display the last log

1. Use a script: (the script displays the last n records)

#! /Bin/sh # Access to the binlog's last n records # If don't set-N, show the last record.   Num = "1"   Function last_logs () { # Get the total records Local rec_acc = './bin/mysqlbinlog $1 | grep-C' ^ # At [0-9] [0-9] * $'' Rec_acc = 'expr $ rec_acc-$ num' # Skip the first n entries. ./Bin/mysqlbinlog-o $ rec_acc $1 }   If [$ #-LT 1] | [$ #-GT 3] Then Echo "Usage: mysqlbinlog [-n0-9] filename" Exit Elif [$ #-EQ 2] Then If echo $1 | grep-Q '^-N [0-9] [0-9] * $' Then Num = 'echo $1 | cut-d "N"-F 2' Last_logs $2 Else Echo "Usage: mysqlbinlog [-n0-9] filename" Fi Else Last_logs $1 Fi

In essence, mysqlbinlog binlogfile is used to print all records, and then the regular expression (^ # At [0-9] [0-9] * $) is used to determine the number of records M. Finally, the last n entries are displayed using the mysqlbinlog-O M-N binlogfile.

Script usage:./last_logs-N3 binlogfile # displays the last three items, excluding the first format_description_event.

 

2. directly modify mysqlbinlog. CC

The preceding script must scan the BINLOG file twice, which may consume a large number of files. The modified tool mainly uses the end_log_pos of the last record as the file size. Use this condition to determine whether parsing and printing are required. The following is the main code:

/* Begin: Xiangzhong. wxd */ Int is_last_flag (Int & argc, char ** argv) { If (argc = 3 &&(! Strcmp (argv [1], "-l") |! Strcmp (argv [2], "-l "))) { Last_flag = 1; If (! Strcmp (argv [1], "-l ")) { Char * temp = argv [1]; Argv [1] = argv [2]; Argv [2] = temp; } // Free (argv [2]); // Argv [2] = '\ 0 '; Argc --; Return 0; } Return-1; } /* End: Xiangzhong. wxd */   /* Begin: Xiangzhong. wxd at: 2011-7-9 */ Struct stat file_buf; Stat (LOGNAME, & file_buf ); Unsigned long file_size = (unsigned long) file_buf.st_size; If (ev_type! = Format_description_event) & last_flag & (ev-> log_pos! = File_size )) Goto end; /* End: Xiangzhong. wxd at: 2011-7-9 */

The Int is_last_flag function is used to determine whether to use the new function. If this function is used, add a-L to the command line and use only one binlogfile as the parameter, that is, it is invalid to use it with other parameters such as start-position.

Usage:./bin/mysqlbinlog-l binlogfile

Note: No matter whether you use either of the above two types, # at 4 is printed, that is, the first record. Therefore, we have printed two records in total. (For the reason, see format_description_event ).

Main involved source files: mysqlbinlog. CC, log_event.cc, log_event.h.

In summary, the BINLOG file is actually like a stream file. Each record has no obvious start and end signs. It uses the length to determine the end position of a record, therefore, when analyzing BINLOG files, you must always start from the beginning and then read them one by one.