MySQL DBA system learning (6) binary Log Binlog II

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:

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.

cons : In statement mode, because he is the execution statement of the record, so, in order for these statements to be executed correctly at the slave end, he must also record some relevant information about each statement at the time of execution, that is, contextual information, to ensure that all statements 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
As you can see from the official documentation, the previous MySQL has been only based on the statement copy mode until the 5.1.5 version of MySQL started to support row replication. Starting with 5.0, MySQL replication has resolved issues that are not correctly replicated in a large number of older versions. However, due to the emergence of stored procedures, MySQL Replication has brought more new challenges. In addition, the official documentation says that, starting with version 5.1.8, MySQL provides a third replication mode except for 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 :
. The middle of a stored procedure or trigger;
. Enabled the NDB;
. 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 :
. When a DML statement updates a NDB table;
. When the function contains a UUID ();
. 2 or more tables containing the Auto_increment field are updated;
. When executing the INSERT DELAYED statement;
. When using UDFs;
. The view must require the use of the row, such as the UUID () function when establishing the view;

To set the master-slave replication mode :

1234	log-bin=mysql-bin#binlog_format= "STATEMENT" #binlog_format = "ROW" binlog_format= "MIXED"

You can also dynamically modify the format of the Binlog at run time. For example :

123456

Mysql>setsession Binlog_format = ' STATEMENT '; mysql>setsession binlog_format = ' ROW '; mysql>setsession binlog_ format = ' MIXED '; mysql>set global binlog_format = ' STATEMENT '; mysql>set global binlog_format = ' ROW '; mysql>set GLOBAL Binlog_format = ' MIXED ';

comparison of two modes :
Statement Advantages
long history, mature technology;
The resulting binlog file is small;
The binlog contains all the database modification information, which can be used to audit the database security and so on.
Binlog can be used for real-time restores, not just for replication;
Master-slave version can be different from the server version can be higher than the main server version;

Statement Disadvantages :
Not all UPDATE statements can be copied, especially when there is an indeterminate operation;
Replication may also occur when invoking a UDF with uncertainties;
Statements that use the following functions cannot be duplicated:
* Load_file ()
* UUID ()
* USER ()
* Found_rows ()
* Sysdate () (unless the –sysdate-is-now option is enabled at startup)
INSERT ... SELECT produces more row-level locks than RBR;
Replication requires more row-level locks than row requests when performing a full-table scan (where the index is not used in the) UPDATE;
For InnoDB tables with auto_increment fields, the INSERT statement blocks other INSERT statements;
For some complex statements, the consumption of resources from the server will be more serious, and in row mode, only the change of the record will have an impact;
A stored function (not a stored procedure) executes the now () function at the same time it is called, which can be said to be bad or good;
The identified UDF also needs to be executed from the server;
The data table must be almost consistent with the primary server, or it may cause replication errors;
Executing complex statements can consume more resources if there is an error;

Row Advantages
Any situation can be copied, which is the most safe and reliable for replication;
As with most other database systems, as well as replication skills;
In most cases, the copy will be much faster if there is a primary key from the table on the server.
There are fewer row locks when copying the following statements:
* INSERT ... SELECT
* INSERT containing the auto_increment field
* UPDATE or DELETE statements with no strings attached or changes to many records
Fewer locks when executing insert,update,delete statements;
It is possible to perform replication from a server with multithreading;

Row disadvantage
The generated Binlog log volume is much larger;
A complex rollback will contain a large amount of data in the Binlog;
When an UPDATE statement is executed on the primary server, all changed records are written to Binlog, and statement is only written once, which results in frequent binlog write concurrent requests;
Large BLOB values produced by UDFs can cause replication to become slower;
Can not see from the Binlog to copy what statements (encrypted);
When executing a stack of SQL statements on a non-transactional table, it is best to adopt statement mode, otherwise it is easy to cause the data inconsistency of the master-slave server;
In addition, for the system library MySQL inside the table changes when the processing criteria are as follows:
If the use of Insert,update,delete direct operation of the table, the log format according to the Binlog_format set up records;
If the use of Grant,revoke,set PASSWORD and other management statements to do, then in any case to use the statement mode record;
After using statement mode, it can deal with many original key duplication problems.

MySQL DBA system learning (6) binary Log Binlog II