MySQL DBA system learning (6) binary log bis

MySQL 5.5 has 3 different formats for binary log (Binlog): Mixed,statement,row, the default format is Statement. Summarize the pros and cons of these three format logs.

MySQL Replication replication can be based on a single statement (Statement level), or it can be based on a record (Row levels), which can be set in MySQL configuration parameters, and the settings for different replication levels affect the B on the Master side. In-log log format.

1. Row

The log is recorded as a modified form of each row of data, and then the same data is modified at the slave end.

Advantage: In row mode, Bin-log can not record the contextual information of the executed SQL statement, only the record is modified and changed. So the log contents of row will be very clear record the details of each row of data modification, very easy to understand. There are no stored procedures or function in particular cases, and trigger calls and triggers that cannot be replicated correctly.

Disadvantage: In row mode, all executed statements will be recorded in each row as they are recorded in the log, which may result in a large amount of log content, such as an UPDATE statement:

UPDATE product SET owner_member_id = ' B ' WHERE owner_member_id = ' a '

After execution, the log does not record the event that corresponds to this UPDATE statement (MySQL records the Bin-log log as an event), but rather the change of each record that is updated by the statement, which records many events that have been updated on many records. Naturally, the amount of bin-log log will be very large. Especially when executing statements such as ALTER TABLE, the amount of log generated is staggering. Because the way MySQL handles table structure change statements such as ALTER TABLE is that every record of the entire table needs to be changed, in effect rebuilding the entire table. Then each record of the table is recorded in the log.

2. Statement

Every SQL that modifies the data is logged into Master's bin-log. Slave the SQL process resolves the same SQL that was executed sing Woo the original master at the time of replication.

Advantages: In statement mode, the first is to solve the disadvantage of row mode, do not need to record the changes in each row of data, reduce the amount of bin-log log, save I/O and storage resources, improve performance. Because he only needs to record the details of the statements executed on master, and the information about the context at which the statement was executed.

Disadvantage: In statement mode, because he is a record of the execution of the statement, so in order to make these statements in the slave can also be executed correctly, then he must also record each statement at the time of execution of some relevant information, that is, contextual information to ensure that all statements in the slave The end Cup can be executed with the same results as at the master end. In addition, because MySQL is now relatively fast development, a lot of new features continue to join, so that the replication of MySQL encountered a large number of challenges, natural replication involves the more complex content, the more easily the bug will appear. In the statement, at present, there are many cases have been found to cause MySQL replication problems, mainly to modify the data when the use of certain functions or functions of the time will appear, such as: Sleep () function in some versions can not be copied correctly, in the stored procedures used The last_insert_id () function may cause inconsistent IDs on slave and master, and so on. Because the row is recorded on a per-row basis, there is no similar problem.

3. Mixed

As you can see from the official document, MySQL has only been based on statement replication mode until 5.1.5 version of MySQL began to support row replication. Since 5.0, MySQL replication has solved the problem of incorrectly replicating in a number of older versions. However, due to the emergence of stored procedures, the MySQL Replication has brought greater new challenges. In addition, the official document says that, starting with version 5.1.8, MySQL provides a third mode of replication except Statement and Row: Mixed is actually a combination of the first two. In Mixed mode, MySQL distinguishes the log form of a record against each specific SQL statement that is executed, i.e. to choose between statement and row. The statment in the new version is still the same as before, just record the executed statement. The new version of MySQL in the row mode has also been optimized, not all of the changes will be in row mode to record, such as the encounter table structure changes will be in the statement mode to record, if the SQL statement is really update or delete, such as modify the data statement, Then the changes to all rows are logged.

Additional reference information

You can dynamically change the format of Binlog at run time, except in the following situations:

. The middle of a storage process or trigger;

. The NDB is enabled;

. The current session uses row mode and a temporary table is open;

If Binlog uses the Mixed mode, the Binlog mode is automatically changed from statement mode to row mode in the following situations:

. When the DML statement updates a NDB table;

. When the function contains a UUID ();

. 2 and above tables containing auto_increment fields are updated;

. When the INSERT delayed statement is executed;

. When using a UDF;

. The view must require the use of row, for example, when the view is set up using the UUID () function;

Set master-slave copy mode:

Log-bin=mysql-bin

#binlog_format = "STATEMENT"

#binlog_format = "ROW"

binlog_format= "MIXED"

You can also dynamically modify the format of Binlog at run time. For example:

Mysql>setsession Binlog_format = ' STATEMENT ';

Mysql>setsession Binlog_format = ' ROW ';

Mysql>setsession Binlog_format = ' MIXED ';

Mysql>set GLOBAL binlog_format = ' STATEMENT ';

Mysql>set GLOBAL binlog_format = ' ROW ';

Mysql>set GLOBAL binlog_format = ' MIXED ';