MySQL BINLOG format (mixed, statement, row level)

MySQL replication BINLOG logging level

Mixed is recommended. statement is used by default and context-based. 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 will affect the bin-log records on the master end to different forms. Row level: the log records the modified form of each row of data, and then modifies the same data on the slave end. Advantage: in row level mode, Bin-log does not record the context-related information of the executed SQL statement, but only needs to record the record that has been modified, to what it is. Therefore, the row-level log records the details of each row of data modification, which is easy to understand. In addition, stored procedures, functions, and trigger calls and triggers cannot be correctly copied in certain situations. Disadvantage: When all statements executed at row level 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: Update product set owner_member_id = 'B' where owner_member_id = 'A'. After execution, what is recorded in the log is not the event corresponding to this update Statement (MySQL records the bin-log as an event), but the change of each record updated by this statement, in this way, many events are recorded and updated. 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. Statement level: Each SQL statement that modifies data is recorded in the bin-log of the master. When the Server Load balancer replicates, the SQL process parses the same SQL statement that was previously executed on the master to execute it again. Advantages: Statement level first solves the disadvantages of row level. It does not need to record the changes of each row of data, reduces the bin-log volume, saves Io, 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: because it is a recorded execution statement, in order to make these statements run correctly on the slave end, it must also record some information about each statement during execution, that is, the context information to ensure that all the statements can get the same results as those executed on the master node when the slave terminal cup is executed. 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 level, 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 copied in some versions, and the last_insert_id () function is used in the stored procedure, which may lead to an inconsistent ID between the slave and the master. Since the row level records changes based on each row, no similar problems will occur. From the official documents, we can see that MySQL has only been in statement-based replication mode until MySQL 5.1.5 starts to support row-level 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 poses a new challenge to MySQL replication. In addition, according to the official documentation, MySQL has provided the third replication mode except statement level and row level 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 level in the new version is the same as before, and only the statements executed are recorded. The row level mode of the new MySQL squadron has also been optimized. Not all modifications are recorded by row level. For example, when the table structure is changed, it will be recorded in 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. The following nominal value is a read :: -- SQL statement-based replication ), -- Row-based replication (RBR ), -- Mixed replication (MBR ). Correspondingly, there are three BINLOG formats: Statement, row, and mixed. In MBR mode, the default version is "SBR. You can dynamically change the BINLOG format at runtime, except for the following situations: . Intermediate storage process or trigger . NDB Enabled . The RBR mode is used in the current session and the temporary table is enabled. If BINLOG adopts the mixed mode, the BINLOG mode is automatically changed from the SBR mode to the RBR 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 any insert delayed statement is row . When using UDF When RBR must be used in a view, for example, the uuid () function is used to create a view. Set the master-slave replication mode: 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 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 '; Advantages and disadvantages of the two modes: advantages of SBR: long history, mature skills small BINLOG file the BINLOG contains all database modification information, which can be used to audit database security and other conditions BINLOG can be used for real-time restoration, not only can the Master/Slave versions be used for replication, but the slave server version can be higher than the master server version disadvantages of the SBR: not all update statements can be copied, especially when there are uncertain operations. copying when a UDF with uncertainties is called may also have doubts 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) insert... select will generate more row-level locks than RBR When copying an update that requires a full table scan (The where statement is not applied to the index, more ROW-level locks are required than RBR 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 is more serious, while in RBR mode, only the changed record will be affected storage function (not a storage process) the now () function will also be executed when it is called, this can be said to be a bad thing or a good thing the fixed UDF must also be executed on the slave server the data table must be almost consistent with the master server, otherwise, a replication error may occur. If a complex statement is executed incorrectly, more resources are consumed. Advantages of RBR: It 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. When you copy the following statements, there are fewer row locks: * 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 It is possible to use multiple threads to execute replication on the server. Disadvantages of RBR: BINLOG is much larger Complex rollback BINLOG contains a large amount of data. When an update statement is executed on the master server, all the changed records will be written to the BINLOG, while the SBR will only write once, which will lead to frequent concurrent writes of the BINLOG. The large blob value generated by the UDF causes the replication to slow down. 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 SBR mode. Otherwise, it is easy to cause data inconsistency between the master and slave servers. In addition, the processing rules for table changes in the MySQL database are as follows: If insert, update, and delete are used to operate the table directly, the log format is recorded according to the binlog_format setting. If you use management statements such as grant, revoke, and set password, you can record them in the SBR mode in any case. Note: The RBR mode can be used to handle many primary key duplication problems.