MySQL 5.7 Intimate parameter Binlog_row_image

I believe we all know the format of MySQL Binlog, that is, there are three kinds, respectively, is statement,mixed,row. Each has its merits and demerits, please consult the information in detail. Prior to MySQL version 5.7, although the row format had a variety of benefits.

1. For example, to speed up the log replay from the library; Row directly calls the MySQL storage Engine interface (handler API) to perform row insertions, deletions, and updates, completely skipping the processing logic of the MySQL optimizer.

2. Ensure the consistency of master-slave data. Record the changes for each row.

3. The flashback function can be achieved by reverse parsing the binlog.

Then there is actually a flaw, that is, because the record is the change of each row, will bring the cost of disk IO, and because the Binlog log becomes larger, the network overhead becomes larger. Then after MySQL 5.7 binlog format default is row, and introduced a new parameter binlog_row_image, the default value of this parameter is full, and there is a value of minimal. Since the other features of the 5.7 version have been mentioned, this no one mentioned, I also briefly introduced, haha.

Full records changes for each row, minimal only the rows after the impact. Here's a simple test and everyone will understand.

Mysql>Show variables like '%row_im%';+------------------+-------+|Variable_name|Value|+------------------+-------+|Binlog_row_image|  Full  |+------------------+-------+1Rowinch Set(0.01sec) MySQL> UpdateT2SetUid= About whereName='Yayundeng'; Query OK,1Row affected (0.03sec) Rows matched:1Changed:1Warnings:0MySQL> 

Parse Binlog See what happens

[[Email protected]3306]# Mysqlbinlog--no-defaults-v-v--base64-output=decode-rows mysql-bin.000010|Tail-N -#161210  One: Geneva: +ServerID  theEnd_log_pos2043CRC320x3ce8a225Update_rows:tableID  theFlags:stmt_end_f# # # UPDATE ' Test '. ' T2 ' # # # where### @ 1=1/* INT meta=0 nullable=0 is_null=0 */### @2= ' gz '/* STRING (a) meta=65044 nullable=1 is_null=0 */### @3= ' Yayun  Deng '/* STRING (meta=65044 nullable=1 is_null=0 */### @4=1 * INT meta=0 nullable=1 is_null=0 */### set### @1=1/* INT meta=0 nullable=0 is_null=0 */### @2= ' gz '/* STRING (a) meta=65044 nullable=1 is_null=0 */### @3= ' Yayundeng '/* S TRING () meta=65044 nullable=1 is_null=0 */### @4=99/* INT meta=0 nullable=1 is_null=0 */# at2043#161210  One: Geneva: +ServerID  theEnd_log_pos2074CRC320x93619126Xid = theCOMMIT/*!*/; SET @ @SESSION. Gtid_next='AUTOMATIC' /*added by Mysqlbinlog*/ /*!*/;D Elimiter; # End of Logfile/*!50003 SET [email protected]_completion_type*/;/*!50530 SET @ @SESSION. Pseudo_slave_mode=0*/; [[Email protected]3306]#

The following sets the value of Binlog_row_image to minimal

Mysql> SetBinlog_row_image='Minimal'; Query OK,0Rows Affected (0.00sec) MySQL> UpdateT2SetUid= - whereName='Yayundeng'; Query OK,1Row affected (0.02sec) Rows matched:1Changed:1Warnings:0MySQL> 

Then parse Binlog to see what the situation is:

[[Email protected]3306]# Mysqlbinlog--no-defaults-v-v--base64-output=decode-rows mysql-bin.000010|Tail-N -SET TIMESTAMP=1481339144/*!*/; BEGIN/*!*/; # at2211#161210  One: to: -ServerID  theEnd_log_pos2263CRC320x57948074Table_map: ' Test '. ' T2 ' mapped to number the# at2263#161210  One: to: -ServerID  theEnd_log_pos2309CRC320x72114b1fUpdate_rows:tableID  theFlags:stmt_end_f# # # UPDATE ' Test '. ' T2 ' # # # where### @ 1=1/* int meta=0 nullable=0 is_null=0 */### set### @4=100/* int meta=0 nullable=1 is_null=0 */# at 2309#161210  One: to: -ServerID  theEnd_log_pos2340CRC320x23e45a71Xid = theCOMMIT/*!*/; SET @ @SESSION. Gtid_next='AUTOMATIC' /*added by Mysqlbinlog*/ /*!*/;D Elimiter; # End of Logfile/*!50003 SET [email protected]_completion_type*/;/*!50530 SET @ @SESSION. Pseudo_slave_mode=0*/; [[Email protected]3306]#

Can clearly see, when the binlog_row_image set to minimal, Binlog record is only the impact of the line, great. As a result, using the row format must be preferred and there is absolutely no need to use statement,mixed. But now no one is using statement, so it is absolutely unnecessary to use the mixed format.

Summarize:

MySQL 5.7 is a truly milestone version, such as the ability to adjust the BP pool online, modify the replication filter online, the real multi-threaded replication (5.6 is based on the library, 5.7 is based on the table), support the BP pool preheating, support undo log recovery, support the universal table space, support JSON , supports function indexes (implemented through virtual columns), supports read-only transactions, and so on.

MySQL 5.7 Intimate parameter Binlog_row_image