前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >binlog解析出来的日志为何无法恢复

binlog解析出来的日志为何无法恢复

作者头像
AsiaYe
发布2021-09-03 14:54:13
7830
发布2021-09-03 14:54:13
举报
文章被收录于专栏:DBA随笔DBA随笔

问题描述

问题来自一位群友,简单说就是用 mysqlbinlog 工具读取 binlog 欲进行恢复,却发现数据并没被恢复。

先一起来看下他是怎么做恢复的。

表中原来有几条数据,但不小心被清空了:

代码语言:javascript
复制
[yejr]> select * from t1;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+

查看binlog event,有几条插入数据,最后还有一条 truncate table 的"误操作",现在想要把表数据恢复到误删数据前的状态。

代码语言:javascript
复制
[yejr]> show binlog events in 'binlog.000003';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name      | Pos  | Event_type     | Server_id | End_log_pos | Info                                                               |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+
| binlog.000003 |    4 | Format_desc    |      3306 |         125 | Server ver: 8.0.25-15, Binlog ver: 4                               |
| binlog.000003 |  125 | Previous_gtids |      3306 |         196 | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-5                           |
| binlog.000003 |  196 | Gtid           |      3306 |         282 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:6'  |
| binlog.000003 |  282 | Query          |      3306 |         358 | BEGIN                                                              |
| binlog.000003 |  358 | Rows_query     |      3306 |         405 | # insert into t1 select 1                                          |
| binlog.000003 |  405 | Table_map      |      3306 |         454 | table_id: 91 (yejr.t1)                                            |
| binlog.000003 |  454 | Write_rows     |      3306 |         494 | table_id: 91 flags: STMT_END_F                                     |
| binlog.000003 |  494 | Xid            |      3306 |         525 | COMMIT /* xid=75 */                                                |
| binlog.000003 |  525 | Gtid           |      3306 |         611 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:7'  |
| binlog.000003 |  611 | Query          |      3306 |         687 | BEGIN                                                              |
| binlog.000003 |  687 | Rows_query     |      3306 |         734 | # insert into t1 select 2                                          |
| binlog.000003 |  734 | Table_map      |      3306 |         783 | table_id: 91 (yejr.t1)                                            |
| binlog.000003 |  783 | Write_rows     |      3306 |         823 | table_id: 91 flags: STMT_END_F                                     |
| binlog.000003 |  823 | Xid            |      3306 |         854 | COMMIT /* xid=76 */                                                |
| binlog.000003 |  854 | Gtid           |      3306 |         940 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:8'  |
| binlog.000003 |  940 | Query          |      3306 |        1016 | BEGIN                                                              |
| binlog.000003 | 1016 | Rows_query     |      3306 |        1063 | # insert into t1 select 3                                          |
| binlog.000003 | 1063 | Table_map      |      3306 |        1112 | table_id: 91 (yejr.t1)                                            |
| binlog.000003 | 1112 | Write_rows     |      3306 |        1152 | table_id: 91 flags: STMT_END_F                                     |
| binlog.000003 | 1152 | Xid            |      3306 |        1183 | COMMIT /* xid=77 */                                                |
| binlog.000003 | 1183 | Gtid           |      3306 |        1269 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:9'  |
| binlog.000003 | 1269 | Query          |      3306 |        1345 | BEGIN                                                              |
| binlog.000003 | 1345 | Rows_query     |      3306 |        1392 | # insert into t1 select 4                                          |
| binlog.000003 | 1392 | Table_map      |      3306 |        1441 | table_id: 91 (yejr.t1)                                            |
| binlog.000003 | 1441 | Write_rows     |      3306 |        1481 | table_id: 91 flags: STMT_END_F                                     |
| binlog.000003 | 1481 | Xid            |      3306 |        1512 | COMMIT /* xid=78 */                                                |
| binlog.000003 | 1512 | Gtid           |      3306 |        1596 | SET @@SESSION.GTID_NEXT= 'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:10' |
| binlog.000003 | 1596 | Query          |      3306 |        1693 | use `yejr`; truncate table t1 /* xid=87 */                        |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------------------------+

运行下面的命令想要进行恢复数据,但发现不能正确恢复:

代码语言:javascript
复制
$ mysqlbinlog --start-position=4 --stop-position=1512 binlog.000003 | mysql -f yejr 

已经指定了读取binlog的位置是从4开始,到1512截止,因为再下一个event就是"误操作"了。之后查询 yejr.t1 表数据还是空的,没有被正确恢复。

问题解决

首先再次强调线上操作要非常谨慎,如果只是普通的DML操作,建议事先打开事务模式,这样万一误操作了还能执行 rollback 回滚

但如果是DDL操作,则基本上只能依赖备份或者延迟从库,所以务必再三确认是在测试环境还是线上环境,以及即将执行的命令影响如何。

再回到问题本身。

在执行 mysqlbinlog 解析binlog并尝试恢复时,观察新的binlog,确认没有写入新数据,说明确实没执行恢复操作。

解析binlog查看,并没有类似 SET SQL_LOG_BIN=0 等忽略binlog的设置,而如果复制SQL指令执行出来手动执行,却是可以成功的。说明binlog本身没问题,是其他原因导致的。

再查看 binlog event 时注意到启用了 GTID 模式,在 GTID 模式下有个特点是要求 全局唯一

除了GTID值持续递增之外,有个不容易注意的特点是,当事务GTID在已执行过的GTID集合(Executed_Gtid_Set)中的话,就不会再次被执行。假设以下几种场景:

  • 假设当前Executed_Gtid_Set集合是 1-10,如果下一个GTID是5,它在这个范围内,则无法被执行
  • 假设当前Executed_Gtid_Set集合是 1-10, 20-30,如果下一个GTID是13,不在这个范围内,则可以被执行

分别做个测试:

代码语言:javascript
复制
# 查看当前gtid
[yejr]> show master status;
+---------------+----------+--------------+------------------+----------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                            |
+---------------+----------+--------------+------------------+----------------------------------------------+
| binlog.000004 |      762 |              |                  | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-15:20 |
+---------------+----------+--------------+------------------+----------------------------------------------+

# 下一个GTID在范围内,不会被执行
[yejr]> set session gtid_next='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:13';
[yejr]> insert into t1 select 6;
Query OK, 0 rows affected (0.00 sec)   #<-- 0 rows

# 下一个GTID不在范围内,会被执行
[yejr]> set session gtid_next='aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:17';
[yejr]> insert into t1 select 6;
Query OK, 1 row affected (0.01 sec)   #<-- 1 row affected
Records: 1  Duplicates: 0  Warnings: 0

# 再次查看gtid
[yejr]> show master status;
+---------------+----------+--------------+------------------+-------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                               |
+---------------+----------+--------------+------------------+-------------------------------------------------+
| binlog.000004 |     1091 |              |                  | aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaa1:1-15:17:20 |
+---------------+----------+--------------+------------------+-------------------------------------------------+

验证结果确实如上所说。

知道原因就好办了,查看 mysqlbinlog 的帮助文档,查看选项 --skip-gtids 的解释:

代码语言:javascript
复制
  --skip-gtids        Do not preserve Global Transaction Identifiers; instead
                      make the server execute the transactions as if they were
                      new.

它可以使得解析出来的binlog event不再保留原来的 GTID 信息,这就可以当成新的 event 被执行了。所以,如果想要从binlog恢复数据,执行mysqlbinlog时,记得加上 --skip-gtids 选项

全文完。


本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2021-08-18,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 问题解决
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档