MySQL suppresses the binlog part in the BINLOG part, and mysql suppresses the binlog part.

MySQL suppresses the binlog part in the BINLOG part, and mysql suppresses the binlog part.

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 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") orUNSPEC("Unspecified") displaysBINLOGStatements 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.
•  NEVERCausesBINLOGStatements not to be displayed.MysqlbinlogExits with an error if a row event is found that must be displayed usingBINLOG.
• DECODE-ROWSSpecifiesMysqlbinlogThat you intend for row events to be decoded and displayed as commented SQL statements by also specifying the -- verbose option. LikeNEVER,DECODE-ROWSSuppresses displayBINLOGStatements, but unlikeNEVER, 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 (BINARYAndCHAR,VARBINARYAndVARCHAR,BLOBAndTEXT). The output uses a data typeSTRINGFor fixed-length strings andVARSTRINGFor 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 typeUPDATE_ROWS_EVENT,UPDATEStatements are displayed withWHEREClause precedingSETClause.

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
# The default value is AUTO if the base64-output option is not used
SHELL> mysqlbinlog/opt/data/APP01bin. 000001 | grep truncate-B15
# At 310
#141218 16:28:05 server id 11 end_log_pos 358 CRC32 0xe0025004 Table_map: 'test'. 't1' mapped to number 74
# At 358
#141218 16:28:05 server id 11 end_log_pos 402 CRC32 0x3452dcfe Write_rows: table id 74 flags: STMT_END_F

Binlog' # This BINLOG part is a real SQL statement and cannot be seen.
Fzcsvbmlaaaamaaaagybaaaeoaaaaaaaeabhrlc3qaanqxaaicdwi8aameualg
Fzcsvb4laaaalaaaajibaaaeoaaaaaaaeaagac // wBAAVyb2Jpbv7cUjQ =
'/*! */;
# At 402
#141218 16:28:05 server id 11 end_log_pos 433 CRC32 0xbe26740a Xid = 30
COMMIT /*! */;
# At 433
#141218 16:29:00 server id 11 end_log_pos 517 CRC32 0x89c52d6a Query thread_id = 1 exec_time = 0 error_code = 0
Set timestamp = 1418891340 /*! */;
Truncate table t1

# 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
# The BINLOG part is still displayed.
SHELL> mysqlbinlog-v/opt/data/APP01bin. 000001 | grep truncate-B20
/*! */;
# At 310
#141218 16:28:05 server id 11 end_log_pos 358 CRC32 0xe0025004 Table_map: 'test'. 't1' mapped to number 74
# At 358
#141218 16:28:05 server id 11 end_log_pos 402 CRC32 0x3452dcfe Write_rows: table id 74 flags: STMT_END_F

Binlog'
Fzcsvbmlaaaamaaaagybaaaeoaaaaaaaeabhrlc3qaanqxaaicdwi8aameualg
Fzcsvb4laaaalaaaajibaaaeoaaaaaaaeaagac // wBAAVyb2Jpbv7cUjQ =
'/*! */;
### Insert into 'test'. 't1'
### SET
###@ 1 = 1
###@ 2 = 'Robin'
# At 402
#141218 16:28:05 server id 11 end_log_pos 433 CRC32 0xbe26740a Xid = 30
COMMIT /*! */;
# At 433
#141218 16:29:00 server id 11 end_log_pos 517 CRC32 0x89c52d6a Query thread_id = 1 exec_time = 0 error_code = 0
Set timestamp = 1418891340 /*! */;
Truncate table t1

# Add the -- base64-output = DECODE-ROWS option to suppress the display of BINLOG, we do not see the BINLOG section below
SHELL> mysqlbinlog -- base64-output = DECODE-ROWS-v/opt/data/APP01bin. 000001 | grep truncate-B20
/*! */;
# At 238
#141218 16:28:05 server id 11 end_log_pos 310 CRC32 0x60507739 Query thread_id = 1 exec_time = 0 error_code = 0
Set timestamp = 1418891285 /*! */;
BEGIN
/*! */;
# At 310
#141218 16:28:05 server id 11 end_log_pos 358 CRC32 0xe0025004 Table_map: 'test'. 't1' mapped to number 74
# At 358
#141218 16:28:05 server id 11 end_log_pos 402 CRC32 0x3452dcfe Write_rows: table id 74 flags: STMT_END_F
### Insert into 'test'. 't1'
### SET
###@ 1 = 1
###@ 2 = 'Robin'
# At 402
#141218 16:28:05 server id 11 end_log_pos 433 CRC32 0xbe26740a Xid = 30
COMMIT /*! */;
# At 433
#141218 16:29:00 server id 11 end_log_pos 517 CRC32 0x89c52d6a Query thread_id = 1 exec_time = 0 error_code = 0
Set timestamp = 1418891340 /*! */;
Truncate table t1

# Using mysqlbinlog for Incomplete recovery
SHELL> mysqlbinlog -- database = test -- start-position = "238" -- stop-position = "433"/opt/data/APP01bin. 000001 | mysql-uroot-p -- database = test
Enter password:

# View the restored result
SHELL> mysql-uroot-p-e "select * from test. t1"
Enter password:
+ ------ + ------- +
| Id | val |
+ ------ + ------- +
| 1 | robin |
+ ------ + ------- +