MySQL row log format view Binlog SQL statements

Sometimes we need to use row as the Binlog log format, where the configuration file uses the binlog_format= row parameter

Since then, when we look at the database Binlog content, we can not see the specific statement of additions and deletions, when the database recovery

It is not good for us to find recovery data points.

Log using the row log format:

Can see is a paragraph like encrypted string, do not worry, in fact, the SQL Real statement here, just

After 64-bit encoding, we can see the specific SQL content using the mysqlbinlog corresponding parameters:

Mysqlbinlog--base64-output=decode-rows-v--start-date="2017-08-12 15:00:19" -- stop-date="2017-08-12 15:30:19" mysql-bin. 000055

Description:--base64-ouput=decode-rows represents decoding

-V means that these statements are displayed in line break, and if you do not have a-V you still don't see the exact statement

--start-date on behalf of you to get the start time of the log

--stop-date means you want to get the end data for the log.

Note: You can also use the POS location to query a certain stage of the day content

Results:

Can see a lot of # # #开头的数据, these are specific SQL statements, but not in a row, but the branch display, has not delayed our review.

Binlog Log Tutorial:

binlog Format

Binlog is available in three formats: Statement, row, and mixed.

– SQL statement-based replication (statement-based replication,sbr),
– Row-based replication (row-based REPLICATION,RBR),
– Mixed Mode replication (mixed-based replication,mbr).

2.1 Statement
Each SQL that modifies the data is recorded in Binlog.

Advantages: No need to record the change of each line, reduce the Binlog log volume, save IO, improve performance.

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.

PS: compared to row can save much performance and log volume, this depends on the SQL case of the application, the normal record is modified or inserted in the row format is less than the amount of log generated by statement, but given the condition of the update operation, and the whole table is deleted, 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.

2.2 Row

The 5.1.5 version of MySQL only starts to support the row level replication, which does not log the SQL statement context-sensitive information and only saves 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.

Cons: All executed statements are recorded in the log when they are recorded, which can result in a large amount of log content.

PS: The new version of MySQL in the row level mode has also been optimized, not all changes will be at the row level record, like when the table structure changes in the statement mode to record, If the SQL statement is really a statement that modifies data such as update or delete, then all rows are changed .

2.3 Mixed

Starting with version 5.1.8, MySQL provides the mixed format, which is actually a combination of statement and row.

In mixed mode, the general statement modification using 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.

MySQL row log format view Binlog SQL statements