MySQL Binlog Log three modes of selection and configuration

Before you know the Binlog log three modes, first understand the Binlog log to resolve the command workers Mysqlbinlog. The role of the Mysqlbinlog tool is to parse the MySQL binary binlog log content and parse the binary log into SQL statements that can be executed in the MySQL database. Binlog log raw data exists in binary form and needs to be converted to SQL statement using the Mysqlbinlog tool.

MySQL binlog log function is used to record MySQL internal additions and deletions to the MySQL database has updated content (operation of the database changes), the database query statements such as Show,select the beginning of the statement, will not be binlog log records, Mainly used for the master-slave replication and incremental recovery of the database.

Case:

When the database is scheduled to backup, can only back to a point in time, if it is fully prepared at 0 o'clock in the morning, but at noon 12 o'clock failure to recover data, using 0 points of the full provision can only restore to 0 point of time data, is 0 to 12 points of data can only be lost?

This is the time to reflect the importance of the Binlog log, you need to binlog log timed push (one minute or five minutes, time frequency depending on the business scenario) to complete the incremental backup. When a failure occurs, the data can be recovered to the point of failure using scheduled and incremental backups. The specific recovery plan, here does not do the brief, later writes the article to explain.

Binlog logs three modes

ROW level

The record is done in a row, that is, if the data is modified in bulk, the SQL statement events that are not bulk modified are recorded, but each record is changed, so the Binlog log file for row mode becomes "heavy".

Advantage: The Binlog log content of row level will clearly record the details of each row of data being modified. There are no problems with stored procedures or function in certain situations, and trigger calls and triggers cannot be copied correctly.

Disadvantage: At the row level, all executed statements are recorded as per-row records as they are recorded in the log, which can generate a large amount of log content, resulting in an astonishing amount of binlog logs. Batch modify millions of data, then record millions of lines ...

Statement level (default)

A SQL statement that records every modification of the data (when bulk modification is not a single SQL statement, but a bulk modified SQL statement event). Look at the diagram above to understand the difference between the row level and statement level two modes.

Pros: Statement mode records the changed SQ statement event, not every change record, so significantly reduces the Binlog log volume, saves disk IO, and improves performance.

Cons: statement level is not very good for some special functions, such as: function, copy of the stored procedure. Because the row level is recorded based on the change of each row, there is no similar problem

Mixed

is actually the combination of the first two patterns. In mixed mode, MySQL distinguishes between the log forms of treated records based on each specific SQL statement executed, that is, choosing between statement and row.

How to choose Binlog Mode for enterprise scenario

1, if the use of MySQL in the production of special features relatively few (stored procedures, triggers, functions). Select the default statement mode, Statement level.

2, if the production of the use of MySQL special features more, you can choose Mixed mode.

3, if the use of MySQL in the production of special functions, but also want to maximize data consistency, at this time the best row level mode, but note that the mode of Binlog is very "heavy."

View Binlog Mode

Mysql> Show global variables like "%binlog_format%";   +---------------+-----------+  | variable_name | Value    |  +---------------+-----------+  | binlog_format | STATEMENT |  

Configuring Binlog Log Mode

Vim MY.CNF (configured in [Mysqld] module)

Log-bin =/data/3306/mysql-bin  binlog_format= "STATEMENT"  #binlog_format= "ROW"   #binlog_format

Do not restart to make the configuration effective in MSYQL

SET global binlog_format= ' STATEMENT ';

MySQL Binlog Log three modes of selection and configuration