Mysql Binlog Three formats introduction and analysis "Turn"

A Mysql Binlog Format Introduction

Mysql Binlog logs are available in three formats, statement,mixed, respectively, and row!

1.Statement: Every SQL that modifies data is recorded in Binlog.

Advantages: No need to record the change of each line, reduce the Binlog log volume, save IO, 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. )

Cons: because the records only execute statements, in order for these statements to run correctly on the slave, it is also necessary to record some information about each statement at the time of execution, to ensure that all statements can be slave and executed at the master side of the same result. In addition to MySQL replication, like some specific function functions, slave can be consistent with master on a number of related issues (such as the Sleep () function, last_insert_id (), and user-defined functions (UDF) can cause problems ).

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

2.Row: does not log the SQL statement context-sensitive information, only save which record is modified.

Advantage: Binlog can not record the context-sensitive information of executed SQL statements, only need to record what the record was modified to. So the Rowlevel log content will be very clear to record the details of each row of data modification. There are no stored procedures, or function, and trigger calls and triggers that cannot be copied correctly in certain situations

disadvantage: All executed statements when logged 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 change will have a record, This causes the Binlog log volume to be large, especially when executing a statement such as ALTER TABLE, where each record is changed due to the table structure modification, and each record in the table is recorded in the log.

3.Mixedlevel: is a mixture of the above two levels, the general statement modification using the statment format to save Binlog, such as some functions, statement can not complete the operation of the master-slave copy, the row format to save Binlog, MySQL differentiates the log form of the treated record according to each specific SQL statement executed, that is, choosing 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. The statement pattern is recorded when a table structure change is encountered. For statements that modify data such as update or delete, the changes are recorded for all rows.

Two. Binlog basic configuration 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 Binlog_format settings and records, if the use of Grant,revoke,set PASSWORD If the management statement is done, then the SBR mode is used to record it anyway.

Three Mysql Binlog Log Analysis

View the specific MySQL log via the Mysqlbinlog command, 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

commit/*!*/;

/////////////////////////////////////////////////////////////////////////////////////////////////////////////// ////////////

1. Start the time of the thing:

SET timestamp=1350355892/*!*/;

BEGIN

2.sqlevent Beginnings

#at 1643330: For the beginning of the event, is starting with 1643330 bytes.

3.sqlevent occurrence point in time

#121016 10:51:32: Is the time when the event occurred,

4.serverId

Server ID 1: ServerID for Master

5.sqlevent end point and time spent, error code

End_log_pos 1643885: The end of the event, which ends at 1643885 bytes.

Exectime 0: The time spent

Error_code=0: Error code

Xid: Event indicates a committed XA transaction

Mixed Log Description:

During slave log synchronization, for time functions such as now, the mixed log format produces the corresponding unix_timestamp () *1000 time string in the log, slave when the synchronization is complete, The sqlevent takes place 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 above specified similar to the UDF function, resulting in slave can not be known, the row format will be stored in these Binlog, To ensure that the resulting binlog can be slave to complete the data synchronization.

Turn from

Mysql binlog Three format introduction and analysis-arun_yh-Blog Park

Https://www.cnblogs.com/itcomputer/articles/5005602.html

Mysql Binlog Three formats introduction and analysis "Turn"