Three modes of MySQL Binlog logs

1. Statement level mode

Each SQL that modifies the data is recorded in the Bin-log of master. Slave when replicating, the SQL process parses the same SQL that sing Woo the original master side to execute again.
Advantages: The advantages of statement level, the first is to solve the disadvantage of row level, do not need to record each row of data changes, reduce bin-log log volume, save IO, improve performance. Because he only needs to record the details of the statements executed on master, and the context in which the statements are executed.
Disadvantage: Since it is the execution statement of the record, so that these statements can be executed correctly at the slave end, he must also record some relevant information of each statement at the time of execution, 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 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. At the statement level, there are a number of things that have been discovered that can cause MySQL replication problems, mainly when you use certain functions or functions when you modify data, such as sleep () is not copied correctly in some versions.

2. Rowlevel mode

The log is recorded in the form of each row of data being modified, and then the same data is modified on the slave side
Advantage: 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 change. So the content of the row level log is very clear about the details of each row of data modifications. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations.
Disadvantage: At the row level, all executed statements will be recorded in each row when logged, which may result in a large amount of log content, such as an UPDATE statement: Update product set owner_member_id= ' d ' Where owner_member_id= ' a ', after execution, the log does not record the event corresponding to the UPDATE statement (MySQL logs the Bin-log log in the form of an event), but the change of each record updated by this statement, This records many events that have been updated for many records. Naturally, the amount of Bin-log logs can be very large.

3. Mixed mode
is actually the combination of the first two patterns, in mixed mode, MySQL will be executed according to each specific SQL statement to distinguish the log form of treatment, that is, between statement and row select one. The statement level in the new version is still the same as before, just record the executed statement. In the new version of MySQL, the row level mode is optimized, not all changes are recorded at the row level, as in the case of table structure changes will be recorded in the statement mode, if the SQL statement is actually update or delete When you modify the data, the changes are recorded for all rows.

Adjust Binlog log Mode

Mysql> Show variables like '%binlog_format% '; +---------------+-----------+| variable_name | Value     |+---------------+-----------+| Binlog_format | STATEMENT |+---------------+-----------+1 row in Set (0.00 sec) Set Global Binlog_format = '? ' Originally was statement level, then I changed to row level mode, then cut the Binlog, then do some operations on the database, and then go to the latest Bin-log log with the following statements to view the content Mysqlbinlog-- Base64-output=decode-rows-v mysql-bin.000016

　　

Three modes of MySQL Binlog logs