Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you how to understand the binlog_rows_query_log_events and binlog_row_image parameters of binlog. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Two parameters binlog_rows_query_log_events and binlog_row_image introduced by mysql 5.6.2

Personally, I find it very useful for binlog that uses row format.

Binlog_rows_query_log_events = 1

In row mode.. If this parameter is enabled, the sql statement will be printed to the binlog log. Default is 0 (off)

Although the statement is put into the binlog, the sql will not be executed, just like a comment. But for dba, it is very useful when looking at binlog.

Binlog_row_image='minimal'

The default is full. When binlog is row format, full will record the values of all fields before and after update. In minimal, only the values of changed fields and where fields will be recorded. In noblob, the values of all fields except blob and text will be recorded. If the blob or text field of update only records the changed value of this field, it will not be recorded before the change.

As we all know, the growth rate of binlog in row format is too fast, which puts a lot of pressure on storage space, master-slave transmission. Because each event records all the values before and after changing the data, it will be recorded regardless of whether the data has changed or not. the introduction of binlog_row_image greatly reduces the data recorded by binlog. In combination with binlog_rows_query_log_events, there is no pressure for the daily maintenance of dba binlog, and the hard disk space overhead is saved, the I hand O is reduced, and the master-slave transmission pressure is reduced.

The following is illustrated by examples:

The following is a binlog log of a transaction in row format. The red part is the effect of binlog_rows_query_log_events, and the yellow part is the result of binlog_row_image=full, recording the values of all fields before and after the change.

# at 1134

# 161118 17:25:49 server id 3310 end_log_pos 1199 CRC32 0x3d0190ce GTID last_committed=1 sequence_number=2

SET @ @ SESSION.GTID_NEXT= '59194c6emur70dbMuth11e6Muth85bMei5254002eb131pur299 /

# at 1199

# 161118 17:25:49 server id 3310 end_log_pos 1270 CRC32 0x0bc5f8df Query thread_id=26 exec_time=0 error_code=0

SET timestamp 1479461149

BEGIN

/ *! * /

# at 1270

# 161118 17:25:49 server id 3310 end_log_pos 1332 CRC32 0x7533bf59 Rows_query

# update T11 set col7 = 'xxx' where id = 2 # this line is what happens when binlog_rows_query_log_events is enabled

# at 1332

# 161118 17:25:49 server id 3310 end_log_pos 1396 CRC32 0x75f09d77 Table_map: `tt1`.`t11` mapped to number

# at 1396

# 161118 17:25:49 server id 3310 end_log_pos 1929 CRC32 0xa6eb390e Update_rows: table id 137 flags: STMT_END_F

# UPDATE `tt1`.`t11`

# WHERE

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING xxx' / * VARSTRING (60) xxx * /

# @ 3 VARSTRING bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

# @ 4 please cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

# @ 5 VARSTRING dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

# @ 6 / VARSTRING (60) meta=60 nullable=1 is_null=0 * /

# @ 7 hours / BLOB/TEXT meta=2 nullable=1 is_null=0 * /

# @ 8GetWord ("BLOB/TEXT meta=2 nullable=1 is_null=0")

# SET

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING xxx' / * VARSTRING (60) xxx * /

# @ 3 VARSTRING bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

# @ 4 please cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

# @ 5 VARSTRING dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

# @ 6 / VARSTRING (60) meta=60 nullable=1 is_null=0 * /

# @ 7 hours / BLOB/TEXT meta=2 nullable=1 is_null=0 * /

# @ 8roomxxx' / * BLOB/TEXT meta=2 nullable=1 is_null=0 * /

# at 1929

# 161118 17:25:49 server id 3310 end_log_pos 1960 CRC32 0x9d147914 Xid = 551

Com _ MIT _ blank /

Take a look at the following example: the yellow section is the binlog_row_image='minimal' log. Only the changed value and where value are recorded. Compared with the above example, it is found that there are too many fewer records.

# at 1960

# 161118 17:26:25 server id 3310 end_log_pos 2025 CRC32 0x20921004 GTID last_committed=2 sequence_number=3

SET @ @ SESSION.GTID_NEXT= '59194c6emur70dbMuth11e6Muth85bMel 5254002eb131PUB131PUR 300 /

# at 2025

# 161118 17:26:25 server id 3310 end_log_pos 2096 CRC32 0x0c129ffb Query thread_id=26 exec_time=0 error_code=0

SET timestamp 1479461185

BEGIN

/ *! * /

# at 2096

# 161118 17:26:25 server id 3310 end_log_pos 2158 CRC32 0x6a5031d0 Rows_query

# update T11 set col1 = 'xxx' where id = 3

# at 2158

# 161118 17:26:25 server id 3310 end_log_pos 2222 CRC32 0x34de716f Table_map: `tt1`.`t11` mapped to number

# at 2222

# 161118 17:26:25 server id 3310 end_log_pos 2268 CRC32 0x77563057 Update_rows: table id 137 flags: STMT_END_F

# UPDATE `tt1`.`t11`

# WHERE

# @ 1room3 / * INT meta=0 nullable=0 is_null=0 * /

# SET

# @ 2 VARSTRING xxx' / * VARSTRING (60) xxx * /

# at 2268

# 161118 17:26:25 server id 3310 end_log_pos 2299 CRC32 0xe9b5eaa7 Xid = 555

Com _ MIT _ blank /

Let's take a look at binlog_row_image=noblob. The only difference between noblob and full is that there are no values with field types text and blob, and everything else is recorded. And in the example, @ 8 (col7) is specially changed, which is a field of type text. As you can see, the whole field is not recorded before set, only the value after set is recorded.

# at 3691

# 161118 17:27:01 server id 3310 end_log_pos 3756 CRC32 0x59fb0d65 GTID last_committed=6 sequence_number=7

SET @ @ SESSION.GTID_NEXT= '59194c6emur70dbMuth11e6Muth85bMel 5254002eb131pur304 /

# at 3756

# 161118 17:27:01 server id 3310 end_log_pos 3827 CRC32 0x9943381e Query thread_id=26 exec_time=0 error_code=0

SET timestamp 1479461221

BEGIN

/ *! * /

# at 3827

# 161118 17:27:01 server id 3310 end_log_pos 3889 CRC32 0xa617db5e Rows_query

# update T11 set col7 = 'xxx' where id = 4

# at 3889

# 161118 17:27:01 server id 3310 end_log_pos 3953 CRC32 0x74e30167 Table_map: `tt1`.`t11` mapped to number

# at 3953

# 161118 17:27:01 server id 3310 end_log_pos 4180 CRC32 0xd92fe8dc Update_rows: table id 137 flags: STMT_END_F

# UPDATE `tt1`.`t11`

# WHERE

# @ 1room4 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING xxx' / * VARSTRING (60) xxx * /

# @ 3 VARSTRING bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

# @ 4 please cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

# @ 5 VARSTRING dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

# @ 6 / VARSTRING (60) meta=60 nullable=1 is_null=0 * /

# SET

# @ 1room4 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING xxx' / * VARSTRING (60) xxx * /

# @ 3 VARSTRING bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

# @ 4 please cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

# @ 5 VARSTRING dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

# @ 6 / VARSTRING (60) meta=60 nullable=1 is_null=0 * /

# @ 8roomxxx' / * BLOB/TEXT meta=2 nullable=1 is_null=0 * /

# at 4180

# 161118 17:27:01 server id 3310 end_log_pos 4211 CRC32 0x87487ec8 Xid = 561

Com _ MIT _ blank /

# at 4211

The effect is the same for delete, so we won't give examples one by one.

BEGIN

/ *! * /

# at 4347

# 161118 18:46:03 server id 3310 end_log_pos 4398 CRC32 0xd76dbd23 Rows_query

# delete from T11 where id = 2

# at 4398

# 161118 18:46:03 server id 3310 end_log_pos 4462 CRC32 0x75656a14 Table_map: `tt1`.`t11` mapped to number

# at 4462

# 161118 18:46:03 server id 3310 end_log_pos 4590 CRC32 0xafec2072 Delete_rows: table id 137 flags: STMT_END_F

# DELETE FROM `tt1`.`t11`

# WHERE

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 VARSTRING xxx' / * VARSTRING (60) xxx * /

# @ 3 VARSTRING bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

# @ 4 please cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc

# @ 5 VARSTRING dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd

# @ 6 / VARSTRING (60) meta=60 nullable=1 is_null=0 * /

# at 4590

# 161118 18:46:03 server id 3310 end_log_pos 4621 CRC32 0x179ae194 Xid = 562

Com _ MIT _ blank /

The above is the editor for you to share how to understand binlog's binlog_rows_query_log_events and binlog_row_image parameters, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.