MySQL master-slave binlog format detailed

Binlog file record format statement, row, rixed three kinds, before 5.7 by default is statement mode, to 5.7 starts by default to row mode.

Statement is the statement mode, Binlog record all the statements to make changes to the data, to see Binlog record details can be viewed with mysqlbing, now to test the statement mode:

Here, create a T2 table and test it beforehand.

Mysql> Show CREATE TABLE T2\g

1. Row ***************************

Table:t2

Create table:create Table ' T2 ' (

' id ' int (one) not NULL auto_increment,

' Name ' varchar (+) DEFAULT NULL,

' Date_time ' datetime DEFAULT NULL,

PRIMARY KEY (' id ')

) Engine=innodb auto_increment=2 DEFAULT charset=utf8mb4

1 row in Set (0.00 sec)

Mysql> SELECT @ @binlog_format;

+-----------------+

| @ @binlog_format |

+-----------------+

| STATEMENT

+-----------------+

mysql> INSERT INTO T2 (name,date_time) VALUES (UUID (), now ());

Query OK, 1 row affected, 1 Warning (0.00 sec)

Mysql> select * from T2\g;

1. Row ***************************

Id:1

name:7ba7e11e-f6eb-11e5-a293-000c29fa3584

Date_time:2016-03-31 10:51:35

1 row in Set (0.00 sec)

Now to view Binlog log records

# at 6450

# at 6482

#160331 10:51:35 Server ID 249 end_log_pos 6482 CRC32 0xa3e29747 Intvar

SET insert_id=1/*!*/;

#160331 10:51:35 Server ID 249 end_log_pos 6611 CRC32 0x9fc70343 Query thread_id=14 exec_time=0 error_code =0

SET timestamp=1459392695/*!*/;

Insert into T2 (name,date_time) VALUES (UUID (), now ())

/*!*/;

# at 6611

#160331 10:51:35 Server ID 249 end_log_pos 6642 CRC32 0xdff2aeb8 Xid = 170

commit/*!*/;

Binlog Log is a one-to-one event composition, so a SQL will produce a number of rows of record data, from the red part of the above can see a set insert_id=1 and set TIMESTAMP, after the time stamp conversion is exactly the time we insert data, The ID in the table structure is actually self-increasing, and it can be seen that Binlog records the value of the ID and now () function before executing the statement to ensure consistency from the database data, but does not record the value of the UUID () function, which causes the master to never be consistent.

The row pattern, as its name implies, records the row data for each change, which is tested below:

mysql> INSERT INTO T2 (name,date_time) VALUES (UUID (), now ());

Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * FROM t2 ORDER BY id desc LIMIT 1;

+----+--------------------------------------+---------------------+

| ID | name | Date_time |

+----+--------------------------------------+---------------------+

| 7 | 00e3a5b8-f6ed-11e5-86fc-000c29fa3584 | 2016-03-31 11:02:28 |

+----+--------------------------------------+---------------------+

1 row in Set (0.00 sec)

Production to see Binlog log records, because the row mode is recorded by the MySQL internal identification encoding, so need to add-v parameter to the data we can understand the row

BEGIN

/*!*/;

# at 337

#160331 11:02:28 Server ID 249 end_log_pos 385 CRC32 0xff339393 table_map: ' MC '. ' T2 ' mapped to number 109

# at 385

#160331 11:02:28 Server ID 249 end_log_pos 468 CRC32 0x1560b09c write_rows:table ID 109 flags:stmt_end_f

BINLOG '

rjp8vhp5aaaamaaaaiebaaaaag0aaaaaaaeaam1jaaj0mgadaw8sa5abaaatkzp/

Rjp8vh75aaaauwaaanqbaaaaag0aaaaaaaeaagadb/ghaaaajaawmguzytvioc1mnmvkltexztut

odzmyy0wmdbjmjlmytm1odszmp6wnjywybu=

‘/*!*/;

# # # INSERT into ' MC '. ' T2 '

# # SET

# # @1=7

# # # @2= ' 00e3a5b8-f6ed-11e5-86fc-000c29fa3584 '

# # @3= ' 2016-03-31 11:02:28 '

# at 468

#160331 11:02:28 Server ID 249 end_log_pos 499 CRC32 0X17F01CAC Xid = 9

commit/*!*/;

The green part above is the MySQL record line record changes the code, the red part is the converted data, the red part is actually according to the order of the fields in our table structure in order to 1, 2, 3 variables are directly assigned, so long as the master-slave data structure can guarantee the consistency of the data generated by the function.

Insert a record way to understand, now look at the row mode Update, DELETE statement operation is how to record.

Mysql> select * from T2 where date_time= ' 2016-03-31 11:09:11 ';

+----+--------------------------------------+---------------------+

| ID | name | Date_time |

+----+--------------------------------------+---------------------+

| 8 | f0cac692-f6ed-11e5-86fc-000c29fa3584 | 2016-03-31 11:09:11 |

+----+--------------------------------------+---------------------+

1 row in Set (0.00 sec)

mysql> Update T2 set name= ' a ' where date_time= ' 2016-03-31 11:09:11 ';

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

BEGIN

/*!*/;

# at 939

#160331 11:19:03 Server ID 249 end_log_pos 987 CRC32 0x7ad2c2f9 table_map: ' MC '. ' T2 ' mapped to number 109

# at 987

#160331 11:19:03 Server ID 249 end_log_pos 1032 CRC32 0x30d090d0 update_rows:table ID 109 flags:stmt_end_f

BINLOG '

J5f8vhp5aaaamaaaansdaaaaag0aaaaaaaeaam1jaaj0mgadaw8sa5abaab5wtj6

J5f8vh/5aaaalqaaaageaaaaag0aaaaaaaeaagadaql+caaaap4baghqknaw

‘/*!*/;

# # # UPDATE ' MC '. ' T2 '

# # WHERE

# # @1=8

# # SET

# # # @2= ' a '

# at 1032

#160331 11:19:03 Server ID 249 end_log_pos 1063 CRC32 0x9f1f68e7 Xid = 17

commit/*!*/;

The green part is the record to be executed from the database, it can be seen that the where @1=8, in the order of the field @1 as our primary key ID, but we do not use date_time to do the conditions, not urgent ... Let's see it down.

Mysql> ALTER TABLE T2 modify ID int,drop primary key;

Query OK, 8 rows affected (0.06 sec)

Records:8 duplicates:0 warnings:0

Mysql> select * from T2 where date_time= ' 2016-03-31 11:09:11 ';

+------+------+---------------------+

| ID | name | Date_time |

+------+------+---------------------+

| 8 | A | 2016-03-31 11:09:11 |

+------+------+---------------------+

1 row in Set (0.00 sec)

mysql> Update T2 set name= ' aaa ' where date_time= ' 2016-03-31 11:09:11 ';

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

BEGIN

/*!*/;

# at 1378

#160331 11:23:05 Server ID 249 end_log_pos 1426 CRC32 0x603c2455 table_map: ' MC '. ' T2 ' mapped to number 110

# at 1426

#160331 11:23:05 Server ID 249 end_log_pos 1481 CRC32 0x5edd4f26 update_rows:table ID-flags:stmt_end_f

BINLOG '

Gzj8vhp5aaaamaaaajifaaaaag4aaaaaaaeaam1jaaj0mgadaw8sa5abaadvjdxg

gzj8vh/5aaaanwaaamkfaaaaag4aaaaaaaeaagad/wl4caaaaaeayzmy/rjl/gmaywfhjk/dxg==

‘/*!*/;

# # # UPDATE ' MC '. ' T2 '

# # WHERE

# # @1=8

# # # @2= ' a '

# # @3= ' 2016-03-31 11:09:11 '

# # SET

# # # @2= ' AAA '

# at 1481

#160331 11:23:05 Server ID 249 end_log_pos 1512 CRC32 0x8f9b33cb Xid = 20

commit/*!*/;

This time we delete the key to the record update operation, we can see that the green section where the conditions for all of our fields have been matched, delete operation is the same, so that in the row mode from the server to make data changes are based on the primary key to the data lookup to update, If no primary key is all scanned.

Now let's test the last mixed blending mode:

mysql> INSERT INTO T2 (name,date_time) VALUES (UUID (), now ());

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO T2 (name,date_time) VALUES (' A ', now ());

Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * FROM t2 ORDER BY id desc limit 2;

+----+--------------------------------------+---------------------+

| ID | name | Date_time |

+----+--------------------------------------+---------------------+

| 14 | A | 2016-03-31 11:31:11 |

| 13 | f4bb52a8-f6f0-11e5-a33c-000c29fa3584 | 2016-03-31 11:30:46 |

+----+--------------------------------------+---------------------+

2 rows in Set (0.00 sec)

BEGIN

/*!*/;

# at 337

#160331 11:30:46 Server ID 249 end_log_pos 385 CRC32 0xfb29d460 table_map: ' MC '. ' T2 ' mapped to number 109

# at 385

#160331 11:30:46 Server ID 249 end_log_pos 468 CRC32 0x93c87ab6 write_rows:table ID 109 flags:stmt_end_f

BINLOG '

5pn8vhp5aaaamaaaaiebaaaaag0aaaaaaaeaam1jaaj0mgadaw8sa5abaazg1cn7

5pn8vh75aaaauwaaanqbaaaaag0aaaaaaaeaagadb/gnaaaajabmngjintjhoc1mnmywltexztut

ytmzyy0wmdbjmjlmytm1odszmp63rrz6yjm=

‘/*!*/;

# # # INSERT into ' MC '. ' T2 '

# # SET

# # @1=13

# # # @2= ' f4bb52a8-f6f0-11e5-a33c-000c29fa3584 '

# # @3= ' 2016-03-31 11:30:46 '

# at 468

#160331 11:30:46 Server ID 249 end_log_pos 499 CRC32 0xa8544a3c Xid = 9

commit/*!*/;

# at 499

#160331 11:31:11 Server ID 249 end_log_pos 564 CRC32 0x04029a22 GTID last_committed=1 sequence_number=2

SET @ @SESSION. gtid_next= ' 81570ee3-e47e-11e5-a7cd-000c29fa3584:17 '/*!*/;

# at 564

#160331 11:31:11 Server ID 249 end_log_pos 647 CRC32 0x0741bd22 Query thread_id=2 exec_time=0 error_code =0

SET timestamp=1459395071/*!*/;

BEGIN

/*!*/;

# at 647

# at 679

#160331 11:31:11 Server ID 249 end_log_pos 679 CRC32 0xe4ca7495 Intvar

SET insert_id=14/*!*/;

#160331 11:31:11 Server ID 249 end_log_pos 805 CRC32 0x01e84a88 Query thread_id=2 exec_time=0 error_code =0

Use ' MC '/*!*/;

SET timestamp=1459395071/*!*/;

Insert into T2 (name,date_time) VALUES (' A ', now ())

/*!*/;

# at 805

#160331 11:31:11 Server ID 249 end_log_pos 836 CRC32 0xf9959f9c Xid = 10

commit/*!*/;

There are table query results limit the first time we use the UUID to insert the ID value of 13, it can be seen in the Binlog is the use of row mode record, the second time not using the UUID function is directly recorded statements, It can be concluded that the mixed mode uses row mode logging when the statement contains an unsafe function of the UUID.

After repeated tests above, the advantages and disadvantages of the three models can be obtained:

Statement

Advantages: Direct record of the operation of the statement, Binlog production of small, easy to network transmission

Disadvantage: Data generated by the unsafe function of the UUID cannot guarantee the master-slave consistency

Row

Advantages: Record each row of data, in the case of consistent data structure can be very good to ensure the consistency of master and slave

Disadvantage: Because the record of each row of data, the amount of data generated is relatively large, increase network traffic (Binlog_row_image=minimal can reduce a portion of the log volume), each row of changes will be executed from the lookup, increase the execution pressure from the table, if not set the primary key situation is not optimistic

Mixed

Pros: Automatically determine what mode to use according to the situation, can reduce the Binlog log volume

Cons: Adding a single judgment increases the pressure on the host, and it may not be completely correct for unsafe factors

In the case of the isolation level of RC, using the statement format does not use unsafe functions may also lead to inconsistent master-slave data, we all know that MySQL Binlog is recorded in the order of the commit of things, in a thing to update the data, in time to submit, The other thing is to insert a condition that satisfies the update of the previous thing, because RC does not have a gap lock mechanism, which causes inconsistencies between the data and the master data.

This article is from the "Yuanzhan" blog, make sure to keep this source http://xiaozhong991.blog.51cto.com/2354914/1758773

MySQL master-slave binlog format detailed