Introduction and analysis of three Binlog formats in MySQL

one. Mysql Binlog format introduction    

Mysql binlog log has three formats: Statement, MiXED, and ROW!

1. Statement: Every SQL statement that modifies data will be recorded in the binlog.

Advantages: No need to record changes in each row, reducing the amount of binlog logs, saving IO, and improving performance. (Compared with row, how much performance and log volume can be saved depends on the SQL situation of the application. Normally, the amount of log generated by modifying or inserting the same record in row format is still less than the amount of log generated by statement. However, considering that if there are conditional update operations, whole table deletion, alter table and other operations, ROW format will generate a large amount of logs. Therefore, when considering whether to use ROW format logs, you should consider the actual situation of the application, how much the amount of logs generated will increase, and the IO performance issues it will bring.)

Disadvantages: Since only the executed statements are recorded, in order for these statements to run correctly on the slave, some relevant information of each statement during execution must also be recorded to ensure that all statements can get the same results on the slave as when executed on the master. In addition, MySQL replication, such as some specific functions, the slave can be consistent with the master, which will cause many related problems (such as sleep() function, last_insert_id(), and user-defined functions (udf)).

Statements using the following functions also cannot be copied:

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (unless the --sysdate-is-now option was enabled at startup)

At the same time, INSERT ...SELECT will generate more row-level locks than RBR

2.Row: does not record the context-related information of the SQL statement, only saves which record is modified.

Advantages: Binlog does not need to record the context-related information of the executed SQL statement, but only needs to record what the record has been modified to. Therefore, the rowlevel log content will clearly record the details of each row of data modification. And there will be no problem that the stored procedure, function, or trigger call and trigger cannot be correctly copied in certain situations

Disadvantages: When all executed statements are recorded in the log, they will be recorded as changes to each row of records, which may generate a large amount of log content. For example, if an update statement modifies multiple records, each modification will be recorded in the binlog, which will cause a large amount of binlog logs. Especially when executing statements such as alter table, each record will be changed due to the modification of the table structure, so each record of the table will be recorded in the log.

3.Mixedlevel: It is a mixture of the above two levels. General statement modifications use the statement format to save binlog. For example, some functions and statements cannot complete the master-slave replication operation, so the row format is used to save binlog. MySQL will distinguish the log format to be recorded according to each specific SQL statement executed, that is, choose between Statement and Row. The row level mode of the new version of MySQL has also been optimized. Not all modifications will be recorded at the row level. For example, when the table structure changes, it will be recorded in statement mode. As for statements that modify data, such as update or delete, changes to all rows will still be recorded.

2. Basic configuration and format setting of Binlog

1. Basic preparation

The MySQL BInlog log format can be specified by the attribute binlog_format of the MySQL my.cnf file. Such as the following:

binlog_format = MIXED //binlog log format

log_bin = directory/mysql-bin.log //binlog log name

expire_logs_days = 7 //binlog expiration cleanup time

max_binlog_size 100m //Size of each binlog log file

2. Binlog log format selection

Mysql uses the Statement log format by default, and MIXED is recommended.

Due to some special uses, you can consider using ROWED, such as synchronizing data modifications through binlog logs, which will save a lot of related operations. Binlog data processing becomes very easy, and parsing is also very easy compared to mixed data (of course, the premise is that the IO overhead caused by the increased log volume is within the tolerable range).

3.mysqlbinlog format selection

MySQL's selection principle for log format: If you use INSERT, UPDATE, DELETE and other direct table operations, the log format is recorded according to the setting of binlog_format. If you use management statements such as GRANT, REVOKE, SET PASSWORD, then SBR mode is used for recording anyway.

three. Mysql Binlog log analysis

View the specific mysql log through the MysqlBinlog command, as follows:

///

SET TIMESTAMP=1350355892/*!*/;

BEGIN

/*!*/;

# at 1643330

#121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0

SET TIMESTAMP=1350355892/*!*/;

Insert into T_test….)

/*!*/;

# at 1643885

#121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0

COMMIT /*!*/;

///

1. Time to start things:

SET TIMESTAMP=1350355892/*!*/;

BEGIN

2.sqlevent starting point

#at 1643330: The starting point of the event, starting at 1643330 bytes.

3. The time when sqlevent occurs

#121016 10:51:32: is the time when the event occurred.

4.serverId

server id 1: the serverId of the master

5.sqlevent endpoint and time spent, error code

end_log_pos 1643885: is the end point of the event, ending with 1643885 bytes.

execTime 0: time spent

error_code=0: error code

Xid: event indicates the committed XA transaction

Mixed log description:

During the slave log synchronization process, for time functions such as now, the MIXED log format will generate a corresponding unix_timestamp()*1000 time string in the log. When the slave completes the synchronization, it uses the time when the sqlEvent occurs to ensure data accuracy. In addition, for some functional functions, the slave can complete the corresponding data synchronization. For some UDF functions specified above, which cause the slave to be unaware of the situation, these Binlogs will be stored in ROW format to ensure that the generated Binlog can be used by the slave to complete data synchronization.

The above is some information accumulated during the research before developing a module that completes data synchronization through MySQL binlog. Please correct me if there is anything wrong!

Summarize

This is the end of this article about the introduction and analysis of the three formats of Binlog in MySQL. For more information about the three formats of Binlog in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!