MySQL binlog format type and analysis

A Mysql Binlog Format Introduction

Mysql Binlog logs are available in three formats, statement,mixed, respectively, and row!

1.Statement: Every SQL that modifies data is recorded in Binlog.

Advantages: No need to record the change of each line, reduce the Binlog log volume, save IO, improve performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )

Cons: because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, to ensure that all statements can be slave and executed at the master side of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as the Sleep () function, last_insert_id (), and user-defined functions (UDF) can cause problems ).

Statements that use 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 in the insert ... SELECT produces more row-level locks than RBR

2.Row: does not log the SQL statement context-sensitive information, only save which record is modified.

Advantage: Binlog can not record the context-sensitive information of executed SQL statements, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations

disadvantage: All executed statements when logged in the log, will be recorded in each row of changes to record, which may produce a large number of log content, such as an UPDATE statement, modify multiple records, then binlog each change will have a record, This causes the Binlog log volume to be large, especially when executing a statement such as ALTER TABLE, where each record is changed due to the table structure modification, and each record in the table is recorded in the log.

3.Mixedlevel: is the above two levels of mixed use, the general statement modification using statment format to save Binlog, such as some functions, statement can not complete the operation of the master-slave copy, the row format to save Binlog, MySQL differentiates the log form of the treated record according to each specific SQL statement executed, that is, choosing between statement and row. The new version of the MySQL Squadron row level mode is also optimized, and not all changes are recorded at the row level. The statement pattern is recorded when a table structure change is encountered. For statements that modify data such as update or delete, the changes are recorded for all rows.

Two. Binlog basic configuration and format setting

1. Basic formulation

MySQL binlog log format can be specified through the properties of MySQL my.cnf file Binlog_format. as follows:

Binlog_format = MIXED//binlog log format

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

Expire_logs_days = 7//binlog Expired cleanup time

Max_binlog_size 100m//binlog per log file size

2.Binlog Log Format Selection

MySQL default is to use the statement log format, the recommended use of mixed.

Because of some special use, you can consider using rowed, such as yourself through the Binlog log to synchronize data changes, which will save a lot of related operations. For Binlog data processing can be very easy, relative mixed, parsing is also very easy (assuming that the increase in the amount of log volume of the IO cost within the scope of tolerance).

3.mysqlbinlog Format Selection

MySQL for the selection of log format principle: If the use of insert,update,delete, such as the direct operation of the table, the log format according to Binlog_format settings and records, if the use of Grant,revoke,set PASSWORD such as management statements to do, then in any case the use of SBR mode record.

MySQL binlog format type and analysis