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 amount 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 update operation with stripes, 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 follow 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 is no problem that the calls and triggers of stored procedures, or function, and 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. For example, if a update statement modifies multiple records, each change in binlog will be recorded, resulting in a large amount 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 a 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 = / 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 defaults to Statement log format, 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.MySQL binlog 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.

III. MySQL binlog log analysis

View the specific mysql log through the MySQL binlog directive, as follows:

/

SET timestamp 1350355892

BEGIN

/ *! * /

# at 1643330

# 121016 10:51:32 server id 1 end_log_pos 1643885 Query thread_id=272571 exec_time=0 error_code=0

SET timestamp 1350355892

Insert into T_test... (.)

/ *! * /

# at 1643885

# 121016 10:51:32 server id 1 end_log_pos 1643912 Xid = 0

Com _ MIT _ blank /

/

1. The time to start something:

SET timestamp 1350355892

BEGIN

2.sql event starting point

# at 1643330: as the starting point of the event, it starts with 1643330 bytes.

The point in time when 3.sqlevent occurred

# 121016 10:51:32: is the time when the event occurred

4.server Id

Server id 1: server Id for master

5.sql event end point and time spent, error code

End_log_pos 1643885: the end of the event, ending with 1643885 bytes.

ExecTime 0: time spent

Error_code=0: error cod

Xid: the event indicates the committed XA transaction

Mixed log description:

In the process of slave log synchronization, for the use of time functions such as now, the MIXED log format will generate a corresponding time string of unix_timestamp () * 1000 in the log. When slave completes the synchronization, it uses the time when sqlEvent occurs to ensure the accuracy of the data. In addition, for some functional functions slave can complete the corresponding data synchronization, and for some of the situations specified above are similar to UDF functions, resulting in Slave not knowing, these Binlog will be stored in ROW format to ensure that the resulting Binlog can be used for Slave to complete data synchronization.

The above in the development of data synchronization through MySQL binlog module before the accumulation of research, if there is something wrong, please correct!

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.