Several replication forms of binlog _ MySQL

Several binlog replication formats: bitsCN.com

Several replication modes of binlog

MySQL 5.5 has three different binary log formats: Mixed, Statement, and Row. the default format is Statement. Summarize the advantages and disadvantages of these three formats of logs.

MySQL Replication can be based on a Statement (Statement Level) or a Row Level. you can set this Level in MySQL configuration parameters, the setting of different replication levels affects the bin-log format on the Master end.

1. Row

The log records each row of data in the modified form, and then modifies the same data on the slave side.

Advantage: in row mode, bin-log does not record the context-related information of the executed SQL statement. you only need to record which record has been modified and changed to what kind of information. Therefore, the log content of the row records the details of each row's data modification, which is easy to understand. In addition, there will be no problems in certain situations where stored procedures or functions, trigger calls and triggers cannot be correctly copied.

Disadvantage: in row mode, when all statements executed are recorded in logs, they are all recorded with modifications recorded in each line. This may produce a large amount of log content, for example, there is 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 corresponding to this update statement (MySQL records the bin-log as an event ), it is the change of each record updated by this statement, which records many events updated by many records. Naturally, the amount of bin-log logs will be large. Especially when statements such as alter table are executed, the log volume generated is astonishing. Because MySQL treats alter table and other table structure change statements in a way that each record of the entire table needs to be changed. In fact, the whole table is rebuilt. Each record of the table is recorded in the log.

2. Statement

Each SQL statement that modifies the data is recorded in the bin-log of the master. When the server load balancer instance is copied, the SQL process will parse the same SQL statement that was previously executed on the master end and execute it again.

Advantage: in the statement mode, the disadvantage of the row mode is solved first. you do not need to record the changes of each row of data, which reduces the bin-log volume, saves I/O and storage resources and improves performance. Because it only needs to record the details of the statements executed on the master and the context information during statement execution.

Disadvantage: in statement mode, because it is a recorded execution statement, in order to make these statements run correctly on the slave end, then, he must record some information about each statement during execution, that is, context information, to ensure that all the statements in the slave terminal Cup can get the same results as those in the master.

In addition, because MySQL is developing rapidly and many new features are constantly added, MySQL replication has encountered great challenges. the more complex the content involved during natural replication, the more common the bug is. In statement, many cases have been found to cause MySQL Replication problems, mainly when some specific functions or functions are used for data modification, for example, the sleep () function cannot be correctly copied in some versions. when the last_insert_id () function is used in the stored procedure, the slave and master may get different IDs. Because the row records the changes based on each row, there is no similar problem.

3. mixed

From the official documentation, we can see that MySQL has only been in statement-based replication mode until MySQL 5.1.5 starts to support row replication. Since MySQL 5.0, MySQL replication has solved the problem that many old versions cannot be copied correctly. However, the emergence of stored procedures has brought more new challenges to MySQL Replication.

In addition, according to the official documentation, MySQL has provided the third replication mode except Statement and Row since version 5.1.8: Mixed, which is actually the combination of the first two modes. In Mixed mode, 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 statment in the new version is the same as before, and only records the statements executed. In the new version of MySQL, the row mode is also optimized. not all modifications are recorded in the row MODE. for example, when the table structure is changed, it is recorded in the statement mode, if the SQL statement is indeed a statement that modifies data such as update or delete, changes to all rows will be recorded.

Other references

In addition to the following situations, you can dynamically change the binlog format during runtime:
· Intermediate storage process or trigger;
· NDB is enabled;
· The current session uses the row mode and a temporary table has been opened;

If the binlog adopts the Mixed mode, the binlog mode is automatically changed from the statement mode to the row mode in the following situations:
· When the DML statement updates an NDB table;
· When the function contains UUID;
· When two or more tables with AUTO_INCREMENT fields are updated;
· When executing the insert delayed statement;
· When using UDF;
· Row must be used in a view. for example, the UUID () function is used to create a view;

Set the master-slave replication mode:

1 2 3 4

Log-bin = mysql-bin # Binlog_format = "STATEMENT" # Binlog_format = "ROW" Binlog_format = "MIXED"

You can also dynamically modify the binlog format at runtime. For example:

1 2 3 4 5 6

Mysql> set session binlog_format = 'statement '; Mysql> set session binlog_format = 'row '; Mysql> set session binlog_format = 'mixed '; Mysql> set global binlog_format = 'statement '; Mysql> set global binlog_format = 'row '; Mysql> set global binlog_format = 'mixed ';

Comparison of the two modes:
Statement advantages

· Long history and mature technology;

· The binlog file generated is small;

· Binlog contains all database modification information, which can be used to audit database security and other information;
· Binlog can be used for real-time restoration, not just for replication;
· The master/slave versions can be different. the slave server version can be higher than the master server version;

Disadvantages of Statement:
· Not all UPDATE statements can be copied, especially when there are uncertain operations;
· Copying a UDF with uncertainties may also cause problems;
· 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)
· INSERT... SELECT will generate more row-level locks than RBR;
· To perform a full table scan (the index is not used in the WHERE statement) for replication, more row-level locks are required than row requests;
· For InnoDB tables with AUTO_INCREMENT fields, the INSERT statement blocks other INSERT statements;
· For some complex statements, resource consumption on the slave server will be more serious, while in the row mode, only the changed records will be affected;
· When a stored function (not a stored procedure) is called, it will also execute the NOW () function. this can be said to be a bad thing or a good thing;
· The identified UDF must also be executed on the slave server;
· Data tables must be basically the same as those on the master server; otherwise, replication errors may occur;
· Execution of complex statements consumes more resources if an error occurs;

Row advantages
· Can be copied in any situation, which is the most secure and reliable for replication;
· Similar to the replication skills of most other database systems;
· In most cases, if a table on the slave server has a primary key, replication will be much faster;
· Fewer row locks are allowed when the following statements are copied:
* INSERT... SELECT
* INSERT containing the AUTO_INCREMENT field
* The UPDATE or DELETE statements for many records are not attached with conditions or modified.
· Less locks when executing INSERT, UPDATE, and DELETE statements;
· Use multiple threads on the server to execute replication;

Row disadvantage
· The binlog generated is much larger;
· Complex rollback binlog contains a large amount of data;
· When the master server executes the UPDATE statement, all the changed records will be written to the binlog, and the statement will only be written once, which will lead to frequent binlog write concurrent requests;
· The large BLOB value generated by the UDF will lead to slow replication;
· What statements have been copied (encrypted) cannot be seen from binlog );
· When executing a stacked SQL statement on a non-transaction table, it is best to use the statement mode. otherwise, data inconsistency between the master and slave servers may easily occur;

In addition, the processing rules for table changes in the MySQL database are as follows:
· If the table is operated directly using INSERT, UPDATE, and DELETE operations, the log format is recorded according to the binlog_format setting;
· If you use management statements such as GRANT, REVOKE, and set password, you must use the statement mode record in any case;
· The statement mode can be used to handle many primary key duplication problems;

BitsCN.com