关于binlog:技术分享-mysqlbinlog-技巧

作者：胡呈清

爱可生 DBA 团队成员，善于故障剖析、性能优化，集体博客：https://www.jianshu.com/u/a95…，欢送探讨。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

常用命令

1. 解析 binlog 排查问题

如果只是解析进去查看，能够加 –base64-output=decode-rows 不显示行格局的内容：mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.000201

2. 解析指定 GTID 的事务

用来剖析某个事务做了什么：mysqlbinlog --no-defaults -vv --base64-output=decode-rows --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037' mysql-bin.000199

3. 解析指定范畴的 binlog

a. 工夫范畴

–start-datetime、–stop-datetime 解析出指定工夫范畴内的 binlog，这个只适宜粗略的解析，不精准，因而不要用来回放 binlog。有个小技巧：如果只能确定大略的工夫范畴，而且不确定在哪个 binlog 中，能够间接解析多个 binlog。比方大略在 11:20-12:00 内做了个表删除操作，但这个工夫内有多个 binlog，能够这样：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --start-datetime='2020-08-18 11:20:00' --stop-datetime='2020-08-18 12:00:00' mysql-bin.000203 mysql-bin.000204 mysql-bin.000205

b. 偏移量范畴

–start-position、–stop-position 解析 binlog 指定偏移量范畴内的 binlog。如果同时指定了 –start-position 和 –stop-position，并且是解析多个 binlog，则 –start-position 只对第一个 binlog 失效，–stop-position 只对最初一个 binlog 失效。

这个罕用场景是：曾经解析过一次 binlog 并获得指标事务的 起始 position 后，准确的解析这一段 binlog：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows  --start-position='537' --stop-position='945' mysql-bin.000204
# at 537           "起始地位是 GTID event 前的这个 position"
#200818 11:29:03 server id 3 end_log_pos 602 CRC32 0x7f07dd8c GTID last_committed=1 sequence_number=2 rbr_only=yes 
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; 
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614061'/*!*/; 
...
... 
#200818 11:29:03 server id 3 end_log_pos 945 CRC32 0xedf2b011 Query thread_id=3 exec_time=0 error_code=0 SET TIMESTAMP=1597721343/*!*/; 
COMMIT /*!*/; 
# at 945          "完结地位是 COMMIT event 后的这个 position"

c. GTID 范畴

–include-gtids、–exclude-gtids 具体看参数解释。

4. 回放 binlog

• 回放肯定不能加 –base64-output=decode-rows 参数，因为不会解析出行格局（这是 binlog 真正无效的局部）；
• 回放也能够用下面指定范畴的参数；
• 解析 binlog 回放到本实例，不须要批改 server id，但要留神 GTID 是否已存在；
• GTID 曾经存在，回放不会报错，但也不会真正回放这些事务，能够通过 –skip-gtids 参数跳过 GTID 的限度；

mysqlbinlog --no-defaults --skip-gtids mysql-bin.000203 | mysql -S /data/mysql/data/3306/mysqld.sock -proot

参数解释

1. –no-defaults

能够防止 my.cnf 里配了 [client] 某些 mysqlbinlog 没有的参数导致 mysqlbinlog 失败

2. -v

不加，只显示行格局（即那一串字符串），无奈失去伪 SQL：

加 -v，从行格局中重建伪 SQL(带正文)，不显示 binlog_rows_query_log_events 参数成果：

加 -vv，从行格局中重建伪 SQL 并增加字段数据类型的正文，能够显示 binlog_rows_query_log_events 参数成果：

3. 加 –base64-output=decode-rows

不显示行格局，如果同时加 -v 参数，能够从行格局中解码为带正文的伪 SQL：

4. –skip-gtids

不保留 GTID 事件信息，这样回放 binlog 时会跟执行新事务一样，生成新的 GTID。比照如下：

5. –include-gtids

只解析出指定的 GTID 的事务：

[root@localhost 3306]# mysqlbinlog --no-defaults -vv --base64-output=decode-rows \
> --include-gtids='b0ca6715-7554-11ea-a684-02000aba3dad:614037-614040' mysql-bin.000199 |grep GTID
#200807 17:32:17 server id 2  end_log_pos 194 CRC32 0xc840be04  Previous-GTIDs
#200807 17:32:17 server id 2  end_log_pos 3818435 CRC32 0x9fdea913  GTID    last_committed=3    sequence_number=5   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614037'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 5726909 CRC32 0x51b51cc1  GTID    last_committed=4    sequence_number=6   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614038'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 5727523 CRC32 0x758852f1  GTID    last_committed=6    sequence_number=7   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614039'/*!*/;
#200807 17:32:17 server id 2  end_log_pos 7635997 CRC32 0x47c43f83  GTID    last_committed=6    sequence_number=8   rbr_only=yes
SET @@SESSION.GTID_NEXT= 'b0ca6715-7554-11ea-a684-02000aba3dad:614040'/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;

6. –exclude-gtids

不解析指定的 GTID 的事务