MYSQL replication Modes

In MySQL 5.1, the new replication technology is introduced, namely row-based replication.

MYSQL replication Modes

In MySQL 5.1, the new replication technology is introduced, namely row-based replication.
In short, this new technology focuses on the changes in the table, rather than the previous binlog mode.
From MySQL 5.1.12, you can use the following three modes:
-- 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:
In the middle of the stored procedure 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.

The method for setting the master-slave replication mode is very simple. You only need to add a parameter based on the previous replication Configuration:
Binlog_format = "STATEMENT"
# Binlog_format = "ROW"
# Binlog_format = "MIXED"
Of course, 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 and mature technology
The binlog file is small.
The binlog contains all database change information, which can be used to review database security and other information.
Binlog can be used for real-time restoration, not just for replication.
The Master/Slave versions can be different. The slave server version can be higher than the master server version.

Disadvantages of SBR:
Not all UPDATE statements can be copied, especially when there are uncertain operations.
Replication may also fail when calling udfs with uncertainties.
The statements using 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 a full table scan (index is not used in the WHERE statement) is required for replication, 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.
When a stored function (not a Stored Procedure) is called, it also executes the NOW () function. This can be said to be a bad thing or a good thing.
The determined UDF must also be executed on the slave server.
Data Tables must be basically the same as those on the master server. Otherwise, a replication error may occur.
Execution of complex statements consumes more resources if an error occurs.

Advantages of RBR:
It can be copied in any situation, which is the most secure and reliable for replication.
Similar to the replication technology 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 causes frequent concurrent write 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 solve many primary key duplication problems.

Instance:
For the insert into db_allot_ids select * From db_allot_ids statement:
In binlog_format = statement mode:
BINLOG log information is:
-----------------------------------------
Begin
/*! */;
# At 173
#090612 16:05:42 server Id 1 end_log_pos 288 query thread_id = 4 exec_time = 0 error_code = 0
Set timestamp = 1244793942 /*! */;
Insert into db_allot_ids select * From db_allot_ids
/*! */;
-----------------------------------------

In binlog_format = row mode:
BINLOG log information is:
-----------------------------------------
Binlog'
Bytes
Ha0yshcbaaaanqaaabubaaaqaa8aaaaaeaav/8aqeaaad8aqeaaad8aqeaaad8aqeaaaa =
'/*! */;
-----------------------------------------