MySQL in Binlog_format mode and configuration detailed

There are three main ways of MySQL replication: SQL statement-based replication (statement-based replication, SBR), row-based replication (row-based replication, RBR), mixed-mode replication (mixed-based Replication, MBR). The corresponding, Binlog format also has three kinds: statement,row,mixed.

①statement Mode (SBR)

Each SQL statement that modifies the data is recorded in the Binlog. The advantage is that you do not need to record every SQL statement and each row of data changes, reduce the Binlog log volume, save IO, improve performance. The disadvantage is that in some cases the data in Master-slave is inconsistent (such as the Sleep () function, last_insert_id (), and user-defined Functions (UDF), and so on)

②row Mode (RBR)

Do not log the context information for each SQL statement, just record which data has been modified and what is changed. There are no stored procedures, or function, or trigger calls and triggering problems that cannot be replicated correctly in certain situations. The disadvantage is that a large number of logs are generated, especially when ALTER TABLE causes the log to skyrocket.

③mixed Mode (MBR)

Mixed use of the above two modes, general replication using the statement mode to save the Binlog, for statement mode cannot replicate operations using row mode save Binlog,mysql will choose the log save method according to the SQL statement executed.

Binlog Replication Configuration

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

The code is as follows	Copy Code
Binlog_format = MIXED//binlog log format, MySQL defaults to statement, recommended to use MIXED Log-bin =/data/mysql/mysql-bin.log//binlog log file Expire_logs_days = 7//binlog Expired cleanup time Max_binlog_size = 100m//binlog per log file size Binlog_cache_size = 4m//binlog Cache size Max_binlog_cache_size = 512m//MAX Binlog Cache Size

Three mixed instructions

For a time function that contains now () in the executed SQL statement, the corresponding unix_timestamp () *1000 time string is generated in the log, slave when the synchronization is completed, the time taken for the sqlevent to be used 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 above specified similar to the UDF function, resulting in slave can not be known, the row format will be stored in these binlog, to ensure that the resulting binlog can be slave to complete the data synchronization.

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

Advantages of SBR:

long history, Mature technology
Binlog files are small
Binlog contains all the database change information, which can be used to audit the security of the database, etc.
Binlog can be used for real-time restores, not just for replication
Master-slave version can be different from server version can be higher than the primary server version

Disadvantages of SBR:

Not all UPDATE statements can be duplicated, especially if they contain indeterminate operations.
Replication may also be problematic when invoking a UDF with an indeterminate factor
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
Replication requires more row-level locks than RBR when an UPDATE requiring a full table scan (where the index is not used in the WHERE statement)
For InnoDB tables with auto_increment fields, INSERT statements block other INSERT statements
For some complex statements, the consumption of resources from the server will be more serious, and RBR mode will only affect the changed records.
A stored function (not a stored procedure) executes the now () function once it is called, which can be a bad thing or a good thing.
The identified UDF also needs to be executed from the server
The data table must be almost consistent with the primary server, or it may cause replication to fail.
Executing complex statements consumes more resources if something goes wrong.

Advantages of RBR:

Any situation can be replicated, which is the safest and most reliable for replication
As with most other database system replication technologies
In most cases, replication will be much faster if you have a primary key from a table on the server.
There are fewer row locks when copying the following statements:
* INSERT ... SELECT
* INSERT containing the auto_increment field
* UPDATE or DELETE statements with no strings attached or changes to many records
Fewer locks when executing insert,update,delete statements
Possible to perform replication from a server with multithreading

Disadvantages of RBR:

Binlog a lot bigger.
Complex rollback with a large amount of data in the Binlog
When an UPDATE statement is executed on the primary server, all changed records are written to Binlog, and SBR is written only once, which can cause frequent binlog of concurrent write problems
Large BLOB values produced by UDFs can cause replication to slow
I can't see what statements are being copied from the Binlog.
When executing a stacked SQL statement on a non-transactional table, it is best to use SBR mode, otherwise it can easily result in data inconsistency of the master-slave server.

In addition, the following rules are processed for changes to tables in the system library MySQL:
If you are using the Insert,update,delete Direct Action table, the log format is recorded according to the Binlog_format setting
If it is done with management statements such as Grant,revoke,set PASSWORD, then the SBR mode is used to record it anyway.
Note: After using RBR mode, it can solve many original key duplication problems.

MySQL in Binlog_format mode and configuration detailed