Detailed analysis of binlog_format mode and configuration in MySQL

There are three main ways of MySQL replication: SQL statement-based replication (SBR), row-based replication (RBR), and mixed-based replication (MBR). Correspondingly, there are three formats of binlog: STATEMENT, ROW, and MIXED .

① STATEMENT mode (SBR)

Every SQL statement that modifies data will be recorded in binlog. The advantage is that it does not need to record every SQL statement and every row of data changes, which reduces the amount of binlog logs, saves IO, and improves performance. The disadvantage is that in some cases it may cause inconsistent data in the master-slave (such as problems with the sleep() function, last_insert_id(), and user-defined functions (udf)).

② ROW mode (RBR)

Do not record the context information of each SQL statement, only record which data has been modified and how it has been modified. Furthermore, there will be no problem that the call and triggering of stored procedures, functions, or triggers cannot be correctly replicated in certain specific situations. The disadvantage is that a large amount of logs will be generated, especially when altering a table, which will cause the log to explode.

③ MIXED mode (MBR)

The above two modes are used in combination. For general replication, the STATEMENT mode is used to save binlogs. For operations that cannot be replicated in the STATEMENT mode, the ROW mode is used to save binlogs. MySQL will select the log saving method based on the executed SQL statement.

Binlog replication configuration

In the MySQL configuration file my.cnf, you can configure binglog related options

binlog_format = MIXED //binlog log format, MySQL uses statement by default, and mixed is recommended
log-bin = /data/mysql/mysql-bin.log //binlog log file expire_logs_days = 7 //binlog expiration cleanup time max_binlog_size = 100m //binlog log file size binlog_cache_size = 4m //binlog cache size max_binlog_cache_size = 512m //maximum binlog cache size

Three MIXED description

If the executed SQL statement contains a time function such as now(), a corresponding time string of unix_timestamp()*1000 will be generated in the log. When the slave completes the synchronization, it uses the time when the sqlEvent occurs to ensure the accuracy of the data. In addition, for some functional functions, the slave can complete the corresponding data synchronization. For some UDF functions specified above, which cause the slave to be unaware of the situation, these Binlogs will be stored in ROW format to ensure that the generated Binlog can be used by the slave to complete data synchronization.

Now let's compare the advantages and disadvantages of the following modes in SBR and RBR 2:

Advantages of SBR:

Long history and mature technology

The binlog file is small

The binlog contains all database change information, which can be used to audit the security of the database.

Binlog can be used for real-time restoration, not just replication

The master and slave versions can be different, and the slave server version can be higher than the master server version

Disadvantages of SBR:

Not all UPDATE statements can be replicated, especially those that contain indeterminate operations.

Replication may also fail when calling UDFs with non-deterministic elements

Statements using the following functions also cannot be copied:

* LOAD_FILE()

* UUID()

* USER()

* FOUND_ROWS()

* SYSDATE() (unless the --sysdate-is-now option was enabled at startup)

INSERT ... SELECT generates more row-level locks than RBR

When replicating an UPDATE that requires a full table scan (no index is used in the WHERE clause), more row-level locks are requested than with RBR.

For InnoDB tables with AUTO_INCREMENT fields, INSERT statements block other INSERT statements.

For some complex statements, the resource consumption on the slave server will be more serious. In RBR mode, only the changed record will be affected. The stored function (not the stored procedure) will also execute the NOW() function once when it is called. This can be a bad thing or a good thing.

The determined UDF also needs to be executed on the slave server

The data table must be almost consistent with the primary server, otherwise replication errors may occur.

If errors occur during the execution of complex statements, more resources will be consumed

Advantages of RBR:

Any situation can be copied, which is the safest and most reliable for copying

Like most other database systems, replication technology

In most cases, replication will be much faster if the table on the slave has a primary key.

Fewer row locks are used when replicating the following statements:

* INSERT ... SELECT

* INSERT containing AUTO_INCREMENT fields

* UPDATE or DELETE statements that do not have conditions or do not modify many records

Fewer locks when executing INSERT, UPDATE, and DELETE statements

It is possible to use multiple threads to perform replication from the server

Disadvantages of RBR:

Binlog is much bigger

The binlog contains a lot of data during complex rollbacks

When an UPDATE statement is executed on the master server, all changed records are written to the binlog, while SBR only writes once, which will cause frequent concurrent write problems in the binlog.

Large BLOB values ​​generated by UDFs can cause replication to be slow

It is not possible to see from the binlog what statements were copied

When executing a stacked SQL statement on a non-transactional 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 changes in the tables in the system library mysql are as follows:

If you use INSERT, UPDATE, or DELETE to directly operate the table, the log format is recorded according to the setting of binlog_format.

If you use management statements such as GRANT, REVOKE, SET PASSWORD, etc., then SBR mode logging is used anyway.

Note: After adopting the RBR mode, many problems of duplicate primary keys that occurred previously can be solved.

Summarize

The above is all the content of this article about the detailed analysis of binlog_format mode and configuration in MySQL. I hope it will be helpful to everyone. Interested friends can refer to: Several important MySQL variables, Detailed explanation of MySQL prepare principles, MySQL variable declaration and stored procedure analysis, etc. If you have any questions, please leave a message, everyone is welcome to communicate and discuss.