Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about what the binlog synchronization mode is like in mysql5.7 master-slave synchronization. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

In mysql5.7 master-slave synchronization, binlog synchronization mode (binlog_format) has the best efficiency and stability of MIXED mode.

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

I. synchronous mode

1.STATEMENT mode (SBR)

Each sql statement that modifies the data is recorded in binlog. The advantage is that there is no need to record the data changes of every sql statement and every row, which reduces the amount of binlog log, saves IO, and improves performance. The disadvantage is that in some cases, it can lead to data inconsistency in master-slave (such as sleep () function, last_insert_id (), and user-defined functions (udf), etc.)

2.ROW mode (RBR)

Instead of recording the context of each sql statement, you only need to record which piece of data has been modified and how it has been modified. And there is no problem that the calls and triggers of stored procedures, or function, or trigger, in certain cases can not be copied correctly. The disadvantage is that a large number of logs will be generated, especially in the case of alter table.

3.MIXED mode (MBR)

With the mixed use of the above two modes, the general replication uses STATEMENT mode to save binlog, and for operations that cannot be replicated in STATEMENT mode, use ROW mode to save binlog,MySQL will choose the log preservation method according to the SQL statement executed.

II. Binlog replication configuration

In the configuration file my.cnf of mysql, you can configure binglog dependency with the following 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 each log file size

Binlog_cache_size = 4m / / binlog cache size

Max_binlog_cache_size = 512m / / maximum binlog cache size

3. MIXED description

For a time function such as now () in the executed SQL statement, the corresponding time string of unix_timestamp () * 1000 will be generated in the log. When slave completes the synchronization, it uses the time when sqlEvent occurs to ensure the accuracy of the data. In addition, for some functional functions slave can complete the corresponding data synchronization, and for some of the situations specified above are similar to UDF functions, resulting in Slave not knowing, these Binlog will be stored in ROW format to ensure that the resulting Binlog can be used for Slave to complete data synchronization.

Now compare the advantages and disadvantages of the following patterns in SBR and RBR 2:

Advantages of SBR:

Has a long history and mature technology.

The binlog file is small

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

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

The master-slave version 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 copied, especially if they contain uncertain actions.

Replication can also be a problem when calling UDF with uncertainties

Statements that use the following functions cannot be copied either:

* 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

When replicating a UPDATE that requires a full table scan (indexes are not used in the WHERE statement), more row-level locks are required than the RBR request

For InnoDB tables with AUTO_INCREMENT fields, the INSERT statement blocks other INSERT statements

For some complex statements, the resource consumption on the slave server will be more serious, while in RBR mode, it will only affect that changed record.

When a stored function (not a stored procedure) is called, it also executes the NOW () function once, which is either a bad thing or a good thing.

The identified UDF also needs to be executed on the slave server.

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

If something goes wrong in executing complex statements, it will consume more resources

Advantages of RBR:

Any situation can be replicated, which is the safest and most reliable for replication.

The same as the replication technology of most other database systems

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

There are fewer row locks when copying the following statements:

* INSERT... SELECT

* INSERT containing AUTO_INCREMENT field

* UPDATE or DELETE statements that have no strings attached or have not modified many records

Fewer locks when executing INSERT,UPDATE,DELETE statements

It is possible to use multithreading to perform replication from the server

Disadvantages of RBR:

Binlog is much bigger.

Large amounts of data will be contained in the binlog during a complex rollback

When the UPDATE statement is executed on the primary server, all changed records are written to binlog, while SBR is written only once, which leads to frequent concurrent write problems in binlog.

Large BLOB values produced by UDF cause replication to slow down

You can't see what statements are copied from binlog.

When executing a stacked SQL statement on a non-transactional table, it is best to use SBR mode, otherwise it will easily lead to data inconsistency between the master and slave servers.

In addition, the processing rules for changing the tables in the system library mysql are as follows:

If the table is directly manipulated by INSERT,UPDATE,DELETE, the log format is recorded according to the setting of binlog_format

If you use management statements such as GRANT,REVOKE,SET PASSWORD to do it, then use SBR mode to record anyway

Note: after adopting RBR mode, many previous primary key duplication problems can be solved.

After reading the above, do you have any further understanding of the binlog synchronization mode in mysql5.7 master-slave synchronization? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.