Binlog section _ MySQL

MySQL uses binlog to record the entire data change process. Therefore, we only need MySQL binlog to completely restore the database. There are three different methods for MySQLbinlog logging: STATEMENT, MIXED, and ROW. For different log modes, the binlog generated by bin MySQL records the entire data change process. Therefore, you only need to have MySQL binlog to completely restore the database. There are three different methods for MySQL binlog logging: STATEMENT, MIXED, and ROW. For different log modes, the generated binlog has different recording methods. For MIXED (some SQL statements) and ROW modes, the record is in base-64 mode and starts with BINLOG, which is a pseudo SQL, and we can suppress the display using base64-output parameters. This article describes and demonstrates this.

For usage of mysqlbinlog, see use mysqlbinlog to extract binary logs.

Base64-output parameters of mysqlbinlog

-- Base64-output =Value

This option determines when events shoshould be displayed encoded as base-64 strings usingBINLOGStatements. The option has these permissible values (not case sensitive ):

AUTO("Automatic") or UNSPEC("Unspecified") displays BINLOGStatements automatically when necessary (that is, for format description events and row events). If no -- base64-output option is given, the effect is the same as -- base64-output = AUTO. NEVERCauses BINLOGStatements not to be displayed. MysqlbinlogExits with an error if a row event is found that must be displayed using BINLOG. DECODE-ROWSSpecifies MysqlbinlogThat you intend for row events to be decoded and displayed as commented SQL statements by also specifying the -- verbose option. Like NEVER, DECODE-ROWSSuppresses display BINLOGStatements, but unlike NEVER, It does not exit with an error if a row event is found.

The preceding description specifies the binlog part in the BINLOG.DECODE-ROWSAnd -- verbose options.

The SQL statements produced by -- verbose for row events are much more readable than the correspondingBINLOGStatements. However, they do not correspond exactly to the original SQL statements that generated the events. The following limitations apply:

The -- verbose option can obtain more readable information, but it is not an original SQL statement (similar ).

· The original column names are lost and replaced@N, WhereNIs a column number.

· Character set information is not available in the binary log, which affects string column display:

There is no distinction made between corresponding binary and nonbinary string types ( BINARYAnd CHAR, VARBINARYAnd VARCHAR, BLOBAnd TEXT). The output uses a data type STRINGFor fixed-length strings and VARSTRINGFor variable-length strings. for multibyte character sets, the maximum number of bytes per character is not present in the binary log, so the length for string types is displayed in bytes rather than in characters. for example, STRING (4)Will be used as the data type for values from either of these column types:

CHAR (4) character set latin1

CHAR (2) character set ucs2

Due to the storage format for events of type UPDATE_ROWS_EVENT, UPDATEStatements are displayed with WHEREClause preceding SETClause.

Proper interpretation of row events requires the information from the format description event at the beginning of the binary log. BecauseMysqlbinlogDoes not know in advance whether the rest of the log contains row events, by default it displays the format description event usingBINLOGStatement in the initial part of the output.

If the binary log is known not to contain any events requiringBINLOGStatement (that is, no row events), the -- base64-output = NEVER option can be used to prevent this header from being written.

2. demonstrate binlog generation

-- Environment mysql> show variables like 'version '; + --------------- + ------------ + | Variable_name | Value | + --------------- + ------------ + | version | 5.6.12-log | + ------------- + ------------ + -- query binlog as the row record mode mysql> show variables like 'binlog _ for % '; + --------------- + ------- + | Variable_name | Value | + --------------- + ------- + | binlog_format | ROW | + --------------- + ------- + mysql> reset master; Query OK, 0 rows affected (0.01 sec) mysql> show master status; + quota + ---------- + -------------- + ---------------- + quota + | File | Position | Binlog_Do_DB | bytes | + quota + ---------- + -------------- + ------------------ + quota + | APP01bin. 000001 | 120 | + ----------------- + ---------- + -------------- + ------------------ + ------------------- + mysql> use test; Database changed -- create table t1mysql> create table t1 (id smallint, val varchar (20); Query OK, 0 rows affected (0.01 sec) -- insert a single record mysql> insert into t1 values (1, 'Robin '); Query OK, 1 row affected (0.00 sec) -- clear table mysql> truncate table t1; Query OK, 0 rows affected (0.01 sec) -- View binlog eventsmysql> show binlog events; + Region + ----- + ------------- + ----------- + Region + | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | + Region + ----- + ------------- + ----------- + Region + | APP01bin. 000001 | 4 | Format_desc | 11 | 120 | Server ver: 5.6.12-log, Binlog ver: 4 | APP01bin. 000001 | 120 | Query | 11 | 238 | use 'test'; create table t1 (id smallint, val varchar (20) | APP01bin. 000001 | 238 | Query | 11 | 310 | BEGIN | APP01bin. 000001 | 310 | Table_map | 11 | 358 | table_id: 74 (test. t1) | APP01bin. 000001 | 358 | Write_rows | 11 | 402 | table_id: 74 flags: STMT_END_F | APP01bin. 000001 | 402 | Xid | 11 | 433 | COMMIT/* xid = 30 */| APP01bin. 000001 | 433 | Query | 11 | 517 | use 'test'; truncate table t1 | + ----------------- + ----- + ------------- + ----------- + ------------- + rows + 7 rows in set (0.00 sec) -- obtain the binlog location mysql> show variables like 'log _ bin_basename '; + ------------------ + bytes + | Variable_name | Value | + ------------------ + bytes + | log_bin_basename |/opt/data/APP01bin | + ------------------ +

3. demonstrate how to extract binlog logs

Binlog' # This BINLOG part is a real SQL statement and cannot be seen.
Fzcsvbmlaaaamaaaagybaaaeoaaaaaaaeabhrlc3qaanqxaaicdwi8aameualg Fzcsvb4laaaalaaaajibaaaeoaaaaaaaeaagac = "" wbaavyb2jpbv7cujq =" '= ""*! * = "";

# When the-v parameter is used, we can see the SQL statement generated by the operation, in the form of insert into... @ 1. if-vv is used, the description of the output column is

Binlog'
Fzcsvbmlaaaamaaaagybaaaeoaaaaaaaeabhrlc3qaanqxaaicdwi8aameualg Fzcsvb4laaaalaaaajibaaaeoaaaaaaaeaagac = "" wbaavyb2jpbv7cujq =" '= ""*! * = "";

# Add the -- base64-output = DECODE-ROWS option to suppress the display of BINLOG, we do not see the BINLOG section below

# Using mysqlbinlog for incomplete recovery

# View the restored result