MySQL log format Binlog_format

There are 3 different formats for binary logs (Binlog) in MySQL 5.5: Mixed,statement,row, the default format is Statement. Summarize the pros and cons of these three format logs.

MySQL Replication replication can be based on a single statement (Statement level), or based on a record (Row level), you can set the replication levels in MySQL configuration parameters, different replication level settings will affect the Master side of the B In-log log format.

1. Row
The log is recorded in the form of each row of data being modified, and then the same data is modified on the slave side.

Advantage: in row mode, Bin-log can not record the context-sensitive information of the executed SQL statement, just need to record that one record has been modified, what to change. So the log content of row will be very clear to record the details of each row of data modification, very easy to understand. There are no stored procedures or function in certain situations, and trigger calls and triggers cannot be copied correctly.

disadvantage: in row mode, all executed statements are recorded in the log when logged, which may result in a large amount of log content, such as an UPDATE statement:

[SQL]View Plaincopy

1. UPDATE product SET owner_member_id = ' B ' WHERE owner_member_id = ' A '

After execution, the log does not record the event that corresponds to the UPDATE statement (MySQL logs the Bin-log log as an event), but rather the change of each record that is updated by the statement, which records many events that are updated by many records. Naturally, the amount of Bin-log logs will be very large. Especially when executing statements such as ALTER TABLE, the amount of log generated is staggering. Because MySQL handles table structure change statements such as ALTER TABLE, each record in the entire table needs to be changed, in effect rebuilding the entire table. Then each record of the table is recorded in the log.

2. Statement
Each SQL that modifies the data is recorded in the Bin-log of master. Slave when replicating, the SQL process parses the same SQL that was executed sing Woo the original master side.

Advantages:In statement mode, the first is to solve the disadvantage of row mode, do not need to record each row of data changes, reduce the Bin-log log volume, save I/O and storage resources, improve performance. Because he only needs to record the details of the statements executed on master, and the context in which the statements are executed.

Disadvantages:In statement mode, since he is the execution statement of the record, in order for these statements to execute correctly at the slave end, he must also record some pertinent information about each statement at the time of execution, that is, contextual information, to ensure that all statements are in the slave The end Cup is executed with the same results as when it is executed on the master side. In addition, because MySQL is now developing relatively fast, a lot of new features continue to join, so that the replication of MySQL encountered a large challenge, natural replication involves more complex content, bugs will be more prone to appear. In statement, there are a number of things that have been found to cause MySQL replication problems, mainly when modifying the data when using some specific functions or functions, such as: Sleep () function in some versions can not be copied correctly, in the stored procedure used The last_insert_id () function may cause inconsistent IDs on slave and master, and so on. Because row is recorded on a per-row basis, a similar problem does not occur.

3. Mixed

Starting with version 5.1.8, MySQL provides a third replication mode except Statement and Row: Mixed, which is actually a combination of the first two modes.

In Mixed mode, MySQL distinguishes between the log forms of treated records based on each specific SQL statement executed, that is, choosing between statement and row.

The statment in the new version is still the same as before, recording only the statements executed. The new version of MySQL in the row mode is also optimized, not all changes will be in the row mode to record, such as when the table structure changes will be recorded in the statement mode, if the SQL statement is actually update or delete and other modified data statements, Then the changes to all rows are recorded.

Additional reference information

In addition to the following scenarios, the Binlog format can be dynamically changed at run time:
1. The middle of the storage process or trigger;
2. The NDB is enabled;
3. The current session uses row mode, and a temporary table has been opened;

If Binlog uses Mixed mode, the Binlog mode is automatically changed from statement mode to row mode in the following cases:
1. When a DML statement updates a NDB table;
2. When the function contains a UUID ();
3. When 2 or more tables containing the Auto_increment field are updated;
4. When executing the INSERT DELAYED statement;
5. When using UDF;
6. The view must require the use of the row, such as the UUID () function when establishing the view;

http://blog.csdn.net/mycwq/article/details/17136997

MySQL log format Binlog_format