Introduction and Analysis of Three MySQL Binlog formats

I. Mysql Binlog format Introduction

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

1. Statement: Each SQL Statement that modifies data is recorded in the binlog.

Advantage: you do not need to record changes in each row, which reduces the binlog volume, saves IO, and improves performance. (The performance and log volume can be reduced compared to the row, which depends on the application's SQL condition. The log volume generated by modifying the same record normally or inserting the row format is smaller than the log volume generated by Statement, however, considering the conditional update operations, as well as the entire table deletion and alter TABLE operations, the ROW format will generate a large number of logs, therefore, when considering whether to use the ROW format log, it should be based on the actual situation of the application, the amount of logs generated will increase, and the IO performance problems .)

Disadvantage: Because only execution statements are recorded, in order that these statements can run correctly on slave, some related information of each statement must be recorded during execution, to ensure that all the statements can get the same results as the execution on the master end in slave. In addition, mysql replication, such as some specific function functions, slave can be consistent with the master, there will be many related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) problems ).

The statements using the following functions cannot be copied:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

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

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

2. Row: the context information of the SQL statement is not recorded. Only the record that is saved is modified.

Advantage: binlog does not record the context-related information of the executed SQL statement. You only need to record what the record is modified. Therefore, rowlevel logs Clearly record the details of each row of data modification. In addition, there will be no problems in certain situations where stored procedures, functions, trigger calls and triggers cannot be correctly copied.

Disadvantage: When all statements executed are recorded in the log, they are all recorded with modifications recorded in each line. This may produce a large amount of log content, such as an update statement, if you modify multiple records, each modification to the binlog will be recorded, resulting in a large amount of binlog logs. Especially when you execute statements such as alter table, because of the table structure modification, if each record changes, each record in the table is recorded in the log.

3. mixedlevel: mixed use of the above two levels. For general statement modification, binlog is saved in statment format. For example, if statement cannot complete master-slave replication, binlog is saved in row format, mySQL will differentiate the log format of the Record Based on each specific SQL Statement executed, that is, select one between Statement and Row. the row level mode of the new MySQL squadron is also optimized. Not all modifications are recorded by row level, as if the table structure is changed, it will be recorded in statement mode. For statements that modify data such as update or delete, changes to all rows are recorded.

 

Ii. Binlog basic preparation and format setting

1. Basic preparation

The Mysql BInlog format can be specified through the binlog_format attribute of the mysql my. cnf file. For example:

Binlog_format = MIXED // binlog log format

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

Expire_logs_days = 7 // binlog expiration cleaning time

Max_binlog_size 100 m // binlog size of each log file

2. Binlog log format Selection

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

Due to some special usage, you can consider using ROWED. For example, you can use binlog to synchronize data modification, which saves a lot of related operations. Binlog Data Processing becomes very easy. Compared with mixed, parsing is also very easy (the premise is that the IO overhead caused by the increase of log volume is within the tolerable range ).

3. Select mysqlbinlog format

Mysql's log format selection principle: If the table is operated directly using INSERT, UPDATE, DELETE, etc., the log format is recorded according to the binlog_format setting. If the log format is GRANT, REVOKE, if the set password and other management statements are used, the records are recorded in the SBR mode in any case.

1 2 Next Page