mysql--binary log (bin-log) three format introduction and analysis

One, Mysql binlog log has three kinds of formats, respectively isROW, statement, and mixed.

Row level
Binary log is recorded as "the form in which each row of data is modified" and then the same data is modified on the slave side.
If you modify the structure of a table, then the Binlog log records the action of recreating the table, inserting fields, update, and so on, instead of the alter action.

Advantage: In row level mode, binnary log can not record the context-sensitive information of the query statement executed, as long as it records which row has been modified and what it looks like. The row level will detail the details of the changes in each row of data, with no stored procedures, or function, in a particular case, and trigger calls and triggers failing to replicate correctly.

Disadvantage: All executed statements when recorded in the log, will be recorded in each row of changes to record, which may produce a large number of log content, such as an UPDATE statement, modify multiple records, then binlog each modification will have a record, so that the Binlog log volume will be very large, In particular, when executing a statement such as ALTER TABLE, each record in the table is recorded in the log because of changes in its structure.


Statment level
statment level "Every SQL statement that will be modified" is recorded in the binnary of master. When the slave is copied, the SQL thread parses the Sing Woo original master and executes the same SQL statement.

Advantages: First, solve the disadvantage of row level, do not need to record each row of data changes, reduce the binnary log volume, saving IO cost, improve performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )

Disadvantage: Because it is the execution statement of the record, in order for these statements to be executed correctly on the slave side. Then it must also record some information about the execution of each statement, 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 of the results. In addition, due to the rapid development of MySQL, a lot of new features continue to join, making MySQL replication encountered a great challenge, the more complex the design of the copy, the more prone to bugs. At statement level, it has been found that a lot of cases will cause the MySQL replication problem. The main thing is that after modifying the data using certain functions, such as: the Sleep () function is not copied correctly in some versions, the last_insert_id () function is used in the stored procedure, the ID of the slave and master may be inconsistent, and so on.
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)
At the same time in the insert ... SELECT produces more row-level locks than RBR

Mixed level
In mixed mode, it is a mix of the above two levels.
General statement modification using statment format to save Binlog, such as some functions, statement can not complete the operation of master-slave replication, the row format is saved Binlog,mysql will be executed according to each specific SQL statement to distinguish the log form of treatment records, That is, choose between statement and row. The new version of the MySQL Squadron row level mode is also optimized, and not all changes are recorded at the row level, as in the case of table structure changes, which are recorded in statement mode. For statements that modify data such as update or delete, the changes are recorded for all rows. In addition to MySQL's belief that the statement approach could result in inconsistent data between Master and slave during replication. (such as the use of special procedure and funtion, the use of the UUID () function, etc.), it chooses the mode of row to record the change, and uses the statement method.

Ii. binlog Basic formulation and format setting
1. Basic formulation
MySQL binlog log format can be specified through the properties of MySQL my.cnf file Binlog_format. as follows:
Binlog_format = MIXED//binlog log format
Log_bin = Directory/mysql-bin.log//binlog log name
Expire_logs_days = 7//binlog Expired cleanup time
Max_binlog_size 100m//binlog per log file size

2.Binlog Log Format Selection
MySQL default is to use the statement log format, the recommended use of mixed.
Because of some special use, you can consider using rowed, such as yourself through the Binlog log to synchronize data changes, which will save a lot of related operations. For Binlog data processing can be very easy, relative mixed, parsing is also very easy (assuming that the increase in the amount of log volume of the IO cost within the scope of tolerance).

3.mysqlbinlog Format Selection
MySQL for the selection of log format principle: If the use of insert,update,delete, such as the direct operation of the table, the log format according to the Binlog_format settings and records, if the use of Grant,revoke,set PASSWORD such as management statements to do, then in any case, the use of SBR mode record.

mysql--binary log (bin-log) three format introduction and analysis