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

This article will explain in detail what are the advantages and disadvantages of the three formats of binlog in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

There are three MySQL binlog formats, SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR)

Mixed mode replication (mixed-based replication, MBR). Accordingly, there are three formats for binlog: STATEMENT,ROW,MIXED. It is mainly used in mysql replication technology.

STATEMENT is a binlog based on sql statement level, and every sql that modifies data is saved to binlog; ROW is based on row-level, and it records the changes of each row, that is, it records the changes of each line.

In binlog, the record is very detailed, but the sql statement is not in binlog, and in replication, it will not cause Master-Slave data inconsistency because of stored procedure triggers, but there is a fatal disadvantage.

The log volume is relatively large. Because the data changes of each row are recorded, the amount of log generated is quite large when no where condition is added to the update statement or when alter table is executed. MIXED: by default is

Statement, but switch to the row state in some cases, such as when a DML updates a ndb engine table, or functions related to time users, etc.

Advantages of SBR:

Has a long history and mature skills.

The binlog file is small

Binlog contains all the database modification 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 may also be problematic when invoking UDF with uncertainties

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 produces more row-level locks than RBR

When replicating a UPDATE that requires a full table scan (indexes are not applied in the WHERE statement), 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, the resource consumption on the slave server will be more serious, while in RBR mode, it will only affect that changed record.

The storage function (not the storage process) executes the NOW () function once when it is called, 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 replication skills as 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 queries in binlog.

Large BLOB values produced by UDF cause replication to slow down

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

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 guidelines for handling changes in 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 administrative statements such as GRANT,REVOKE,SET PASSWORD are used to do this, then record in SBR mode anyway.

MBR combines the advantages of two recording modes. Therefore, it is recommended to use MBR mode.

View the binlog format method:

Mysql > show variables like 'binlog_format'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | STATEMENT |

+-+ +

1 row in set (0.00 sec)

How to change the binlog format to MBR mode:

Do not restart the modification method (valid for this login session only, re-login is invalid)

Mysql > SET SESSION binlog_format = 'MIXED'

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'binlog_format'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | MIXED |

+-+ +

1 row in set (0.00 sec)

Restart the MySQL service to modify the method:

Edit the / etc/my.cnf file and add the following settings:

Binlog_format=mixed

It will take effect permanently after restarting the service.

This is the end of the article on "what are the advantages and disadvantages of the three binlog formats in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it out for more people to see.

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.