Shulou(Shulou.com)06/01 Report--

I. introduction to Mysql Binlog format

Mysql binlog logs are available in three formats, Statement,MiXED and ROW!

1.Statement: every sql that modifies the data is recorded in the binlog.

Advantages: no need to record the changes of each line, reduce the number of binlog logs, save IO, and improve performance. (how much performance and log volume can be saved compared with row, which depends on the SQL of the application. The log volume generated by modifying or inserting the row format of the same record is still less than that generated by Statement, but considering that if the conditional update operation, as well as the whole table deletion, alter table and other operations, ROW format will generate a large number of logs, so when considering whether to use ROW format logs should be based on the actual situation of the application. How much more logs will be generated, and the resulting IO performance problems.)

Disadvantages: since only execution statements are recorded, in order for these statements to run correctly on the slave, it is also necessary to record some information about the execution of each statement to ensure that all statements get the same results in slave as they are executed on the masterside. In addition, the replication of mysql, like some specific functions, slave can be consistent with the master will have a lot of related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) will have problems).

Statements that use the following functions cannot be copied either:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

* SYSDATE () (unless the-- sysdate-is-now option is enabled at startup)

At the same time, INSERT. SELECT produces more row-level locks than RBR.

2.Row: no sql statement context-sensitive information is recorded, only which record is modified.

Pros: binlog does not record context-sensitive information about executed sql statements, but only needs to record what that record has been modified to. So the log content of rowlevel will clearly record the details of each line of data modification. And there will be no problems that stored procedures, or function, and the calls and triggers of trigger can not be copied correctly in certain cases.

Disadvantages: when all executed statements are recorded in the log, they will be recorded as changes in each row, which may result in a large amount of log content, such as a update statement. If multiple records are modified, each change in binlog will be recorded, resulting in a large number of binlog logs, especially when executing statements such as alter table, each record will be changed due to table structure changes. Then each record in the table is recorded in the log.

3.Mixedlevel: is the mixed use of the above two kinds of level. General statement modifications use statment format to save binlog. For example, if some functions, statement cannot complete the master-slave copy operation, saving binlog,MySQL in row format will distinguish the log form of records according to each specific sql statement executed, that is, choose one between Statement and Row. The new version of MySQL Squadron row level mode is also optimized. Not all changes are recorded in row level, such as statement mode in the event of table structure changes. Statements that modify data, such as update or delete, still record changes to all rows.

II. Basic configuration and format setting of Binlog

1. Basic preparation

The Mysql BInlog log format can be specified through the property binlog_format of mysql's my.cnf file. As follows:

Binlog_format = MIXED / / binlog log format

Log_bin = directory / mysql-bin.log / / binlog log name

Expire_logs_days = 7 / / binlog expiration cleanup time

Max_binlog_size 100m / / binlog each log file size

2.Binlog log format selection

Mysql uses Statement log format by default, and MIXED is recommended.

Due to some special uses, you can consider using ROWED, such as synchronizing data changes through binlog logs, which will save a lot of related operations. Binlog data processing will be very easy, and parsing will be easy compared to mixed (of course, provided that the IO overhead caused by the increased log volume is tolerated).

3.mysqlbinlog format selection

Mysql's principle for selecting the log format: if the table is directly manipulated by INSERT,UPDATE,DELETE, the log format is recorded according to the setting of binlog_format, and if it is done by management statements such as GRANT,REVOKE,SET PASSWORD, then the SBR mode is used anyway.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.