MySQL three modes of Binlog

Binlog mode can be divided into the following three types (row,statement,mixed)

1.Row

The log is recorded as each row of data is modified form, and then on the slave end of the same data modified, only the data to be modified, only the value, there is no SQL multi-table Association case.

Advantage: In the row mode, Bin-log can not record the execution of the SQL statement context-sensitive information, only need to record that one record has been modified, what to modify, so the log content of row will be very clear record of each row of data modification details, very easy to understand. There are no stored procedures and function in certain situations, and trigger calls and departures cannot be correctly replicated.

Disadvantage: In row mode, all executed statements are recorded with the modification of each row of records as they are recorded in the log, which can result in a large amount of log content.

Mysql> INSERT into username (username) select * from AA;

ERROR 1146 (42S02): Table ' test.username ' doesn ' t exist

mysql> INSERT into User (username) select * from AA;

Query OK, 1 row affected (0.01 sec)

Records:1 duplicates:0 warnings:0

View Binlog

[Email protected]:/vobiledata/mysqllog# mysqlbinlog mysql-bin.000017

BINLOG '

63efubnqaaaalgaaaa8caaaaaa8aaaaaaaeabhrlc3qabhvzzxiaagipai0aaa==

63efubdqaaaajgaaaducaaaaaa8aaaaaaaeaav/8baafymfvexu=

‘/*!*/;

# # INSERT into Test.user

# # SET

# # @1=4/* shortint meta=0 nullable=0 is_null=0 * *

# # # @2= ' Baoyu '/* varstring () meta=45 nullable=0 is_null=0 * *

# at 565

#120806 0:27:39 server ID end_log_pos 592 Xid = 20

commit/*!*/;

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

Thus, the row pattern is data for each row, regardless of the correlation table, which records the corresponding data in the association in log. This results in a large amount of data.

2.statement

Each SQL that modifies the data is recorded in the binlog of master, slave the SQL process resolves sing woo the original master to perform much the same SQL re-execution when copying.

Advantages: In statement mode, the first is to solve the disadvantage of row mode, do not need to record each row of data changes to reduce the Binlog log volume, saving I/O and storage resources, improve performance. Because he only needs to motivate the details of the statements executed on master at the time of the execution of the statement.

Cons: In statement mode, because he is the execution statement of the record, so in order to let these statements in the slave side can also be executed correctly, then he must also record each statement at the time of execution of some relevant information, that is, contextual information, To ensure that all statements are executed at the slave end and are executed at the same time as the master side. In addition, because MySQL is now developing relatively fast, a lot of new features continue to join, so that the replication of MySQL encountered a large challenge, natural replication involves more complex content, bugs will be more prone to appear. In statement, it has been found that a lot of situations can cause MySQL replication problems, mainly when the data is modified using certain functions or functions, such as: Sleep () function in some versions can not be copied correctly, in the stored procedure used Last_ The insert_id () function may cause inconsistent IDs on slave and master, and so on. Because row is recorded on a per-row basis, there is no such thing as a problem.

mysql> INSERT into User (username) values (' Xuebinbin ');

ERROR 1598 (HY000): Binary logging not possible. Message:transaction level ' read-committed ' in InnoDB are not safe for Binlog mode ' STATEMENT '

Mysql> SET SESSION TRANSACTION isolation level repeatable READ

;

Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into User (username) values (' Xuebinbin ');

Query OK, 1 row Affected (0.00 sec)

View Binlog

[Email protected]:/vobiledata/mysqllog# mysqlbinlog mysql-bin.000008

BEGIN

/*!*/;

# at 174

#120806 14:47:35 Server ID end_log_pos 202 Intvar

SET insert_id=2/*!*/;

# at 202

#120806 14:47:35 Server ID end_log_pos 311 Query thread_id=5 exec_time=0 error_code=0

Use test/*!*/;

SET timestamp=1344235655/*!*/;

Insert into user (username) values (' Xuebinbin ')

/*!*/;

# at 311

#120806 14:47:35 Server ID end_log_pos 338 Xid = 20

commit/*!*/;

# at 338

#120806 14:53:18 Server ID end_log_pos 357 Stop

DELIMITER;

# End of log file

ROLLBACK/* Added by Mysqlbinlog */;

/*!50003 SET [email protected]_completion_type*/;

The result is that statement is recorded as a SQL record. In this case, a SQL records only one, reducing the amount of data storage.

3.Mixed

As you can see from the official documentation, the previous MySQL has been only based on the statement copy mode until the 5.1.5 version of MySQL started to support row replication.

MySQL three modes of Binlog