Mysql binary Log

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: There is no need to record changes in each row, reducing the Binlog log volume, saving IO and improving 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 slave, it is also necessary to record some context information for each statement at the time of execution to ensure that all statements can be slave and executed at the master end. The 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: The SQL statement context-sensitive information is not logged, and only which record is saved is modified .

Advantages: Binlog can not record the context-sensitive information of the executed SQL statement, 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

binlog-do-db= the database name that needs to be backed up, if you back up multiple databases, repeat this option to binlog-ignore-db= the database that does not need to be backed up poor, if you back up multiple databases, set this option repeatedly

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.

---------------------------------------------------------------------------

The binary log records all the operations that make changes to the database, and the binary has the following two functions:

1, restore (recovery)2, copy (replication)

Binary log Startup: Configuration parameter Log-bin[=name], if you do not specify name, the default binary log file name is the hostname, the suffix is the sequence number of the two-level log, and the path is the directory where the database resides.

file with index suffix is the index file of the binary log, which is used to store the binary logs produced in the past.

Parameters associated with the binary log:

Max_binlog_size, Binlog_cache_size, Sync_binlog, Binlog-do-db, Binlog-ignore-db, Log-slave-update, Binlog_format

Max_binlog_size, which specifies the maximum value of a single binary log file, if the value is exceeded, a new binary log file is generated, the suffix name is +1, and the . index file is logged, from Mysql5.0 The starting default value is 1073741824, which represents 1G.

default value for Mysql5.5

When using the transaction's storage engineInnoDB, all uncommitted transactions are logged to a cache, and when a transaction is committed, the binary log in the buffer is written directly to the binary log file, and the size of the buffer is determined by theBinlog_cache_sizeDecision, the default size isbinlog_cache_size automatically assigns a size of binlog_cache_size the cache, so the worth setting needs to be quite careful, can be View , binlog_cache_disk_use the settings are appropriate.

The default size of Binlog_cache_size is 32KB

parameters sync_binlog=[n] set to 1 That means to write binary logs in a synchronous write disk, which is important, which is also mentioned later. It is worth noting that in the innodb_support_xa1innodb synchronization of the storage engine data files.

The parameters binlog-do-db and binlog-ignore-db indicate the log to which libraries are written or ignored, and the default value is null, which means that the logs of all libraries are synchronized to the binary log.

Log-slave-update This parameter needs to be configured when building the Master=>slave=>slave architecture.

The Binlog_format parameter is also particularly important. From the mysql5.1 version of the introduction of this parameter, the parameter can be set to the value of statement\,ROW,MIXED;

(1) The STATEMENT format is the same as the previous MySQL version, and the binary log file records the log's logical loss SQL statement.

(2) in the row format, binary logging is no longer a simple SQL statement, but rather a record of row changes in the table, at which point the transaction isolation of the InnoDB is basically set to READ COMMITTED For better concurrency.

(3) in MIXED format,MySQL defaults to the STATEMENT format for binary log files, but in some cases the ROW format is used , and possible scenarios include:

1) The table's storage engine is NDB, and DML operations for the table are logged in ROW format

2) The use of the Current_User (), User (), the (), the Found_rows (), row_count ( ) and other uncertain functions

3) using the INSERT DELAY statement

4) User-defined functions are used

5) A temporary table is used

Mysql binary Log