MySQL 5.7 - 通过 BINLOG 恢复数据

日常开发，运维中，经常会出现误删数据的情况。误删数据的类型大致可分为以下几类：

• 使用 delete 误删行
• 使用 drop table 或 truncate table 误删表
• 使用 drop database 语句误删数据库
• 使用 rm 命令误删整个 MySQL 实例。

不同的情况，都会有其优先的解决方案：

• 针对误删行，可以通过 Flashback 工具将数据恢复
• 针对误删表或库，一般采用通过 BINLOG 将数据恢复。
• 而对于误删 MySQL 实例，则需要我们搭建 HA 的 MySQL 集群，并保证我们的数据跨机房，跨城市保存。

本篇主要讨论的内容是误删表或者库，会先介绍有关 BINLOG 的操作命令，然后会对误删表的这种情况进行实际的模拟。

BINLOG 常见操作命令

BINLOG 的查询方式一般分为两种，一种是进入 MySQL 控制台进行查询，另一种是通过 MySQL 提供的工具 mysqlbinlog 进行查询，两者的不同会在下面介绍。

通过 MySQL Cli 查询 BINLOG 信息

在 cli 中，常见的命令如下：

# 查询 BINLOG 格式
show VARIABLES like 'binlog_format';

# 查询 BINLOG 位置
show VARIABLES like 'datadir';

# 查询当前数据库中 BINLOG 名称及大小
show binary logs;

# 查看 master 正在写入的 BINLOG 信息
show master status\G;

# 通过 offset 查看 BINLOG 信息
show BINLOG events in 'mysql-bin.000034' limit 9000,  10;

# 通过 position 查看 binlog 信息
show BINLOG events in 'mysql-bin.000034' from 1742635 limit 10;

使用 show BINLOG events 的问题：

• 使用该命令时，如果当前 binlog 文件很大，而且没有指定 limit，会引发对资源的过度消耗。因为 MySQL 客户端需要将 binlog 的全部内容处理，返回并显示出来。为了防止这种情况，mysqlbinlog 工具是一个很好的选择。

通过 mysqlbinlog 查询 BINLOG 信息

在介绍 mysqlbinlog 工具使用前，先来看下 BINLOG 文件的内容：

# 查询 BINLOG 的信息
mysqlbinlog  --no-defaults mysql-bin.000034 | less

# at 141
#100309  9:28:36 server id 123  end_log_pos 245
  Query thread_id=3350  exec_time=11  error_code=0

• at 表示 offset 或者说事件开始的起始位置
• 100309 9:28:36 server id 123 表示 server 123 开始执行事件的日期
• end_log_pos 245 表示事件的结束位置 + 1，或者说是下一个事件的起始位置。
• exec_time 表示在 master 上花费的时间，在 salve 上，记录的时间是从 Master 记录开始，一直到 Slave 结束完成所花费的时间。
• rror_code=0 表示没有错误发生。

在大致了解 binlog 的内容后，mysqlbinlog 的用途有哪些？：

• mysqlbinlog 可以作为代替 cli 读取 binlog 的工具。
• mysqlbinlog 可以将执行过的 SQL 语句输出，用于数据的恢复或备份。

查询 BINLOG 日志：

# 查询规定时候后发生的 BINLOG 日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2019-11-22 14:00:00" \
 --database sync_test  mysql-bin.000034 | less

导出 BINLOG 日志，用于分析和排查 sql 语句：

mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2019-11-22 14:00:00" \
 --database sync_test \
 mysql-bin.000034 > /home/mysql_backup/binlog_raw.sql

导入 BINLOG 日志

# 通过 BINLOG 进行恢复。
mysqlbinlog --start-position=1038 --stop-position=1164 \
 --database=db_name  mysql-bin.000034 | \
 mysql  -u cisco -p db_name

# 通过 BINLOG 导出的 sql 进行恢复。
mysql -u cisco -p db_name < binlog_raw.sql

mysqlbinlog 的常用参数：

• --database 仅仅列出配置的数据库信息
• --no-defaults 读取没有选项的文件, 指定的原因是由于 mysqlbinlog 无法识别 BINLOG 中的 default-character-set=utf8 指令
• --offset 跳过 log 中 N 个条目
• --verbose 将日志信息重建为原始的 SQL 陈述。
  • -v 仅仅解释行信息
  • -vv 不但解释行信息，还将 SQL 列类型的注释信息也解析出来
• --start-datetime 显示从指定的时间或之后的时间的事件。
  • 接收 DATETIME 或者 TIMESTRAMP 格式。
• --base64-output=decode-rows 将 BINLOG 语句中事件以 base-64 的编码显示，对一些二进制的内容进行屏蔽。
  • AUTO 默认参数，自动显示 BINLOG 中的必要的语句
  • NEVER 不会显示任何的 BINLOG 语句，如果遇到必须显示的 BINLOG 语言，则会报错退出。
  • DECODE-ROWS 显示通过 -v 显示出来的 SQL 信息，过滤到一些 BINLOG 二进制数据。

MySQL Cli 和 mysqlbinlog 工具之间的比较

如果想知道当前 MySQL 中正在写入的 BINLOG 的名称，大小等基本信息时，可以通过 Cli 相关的命令来查询。

但想查询，定位，恢复 BINLOG 中具体的数据时，要通过 mysqlbinlog 工具，因为相较于 Cli 来说，mysqlbinlog 提供了 --start-datetime，--stop-position 等这样更为丰富的参数供我们选择。这时 Cli 中 SHOW BINLOG EVENTS 的简要语法就变得相形见绌了。

使用 BINLOG 恢复数据

恢复的大致流程如下：

1. 会创建数据库和表，并插入数据。
2. 误删一条数据。
3. 继续插入数据。
4. 误删表。
5. 最后将原来以及之后插入的数据进行恢复。

准备数据

准备数据库，表及数据：

# 创建临时数据库
CREATE DATABASE IF NOT EXISTS test_binlog \
default charset utf8 COLLATE utf8_general_ci; 

# 创建临时表
CREATE TABLE `sync_test` (`id` int(11) \
NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL,  \
PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 添加数据
insert into sync_test (id, name) values (null, 'xiaoa');
insert into sync_test (id, name) values (null, 'xiaob');
insert into sync_test (id, name) values (null, 'xiaoc');

# 查看添加的数据
select * from sync_test;

删除表或者数据

误删操作：

# 删除 name=xiaob 的数据
delete from sync_test where id=3

# 插入几条数据
insert into sync_test (id, name) values (null, 'xiaod');
insert into sync_test (id, name) values (null, 'xiaoe');
insert into sync_test (id, name) values (null, 'xiaof');

# 删除表
DROP TABLE sync_test;

数据的恢复

在执行数据恢复前，如果操作的是生产环境，会有如下的建议：

• 使用 flush logs 命令，替换当前主库中正在使用的 binlog 文件，好处如下：

  • 可将误删操作，定位在一个 BINLOG 文件中，便于之后的数据分析和恢复。
  • 避免操作正在被使用的 BINLOG 文件，防止发生意外情况。

• 数据的恢复不要在生产库中执行，先在临时库恢复，确认无误后，再倒回生产库。防止对数据的二次伤害。

通常来说，恢复主要有两个步骤：

1. 在临时库中，恢复定期执行的全量备份数据。
2. 然后基于全量备份的数据点，通过 BINLOG 来恢复误操作和正常的数据。

使用 BINLOG 做数据恢复前：

# 查看正在使用的 Binlog 文件
show master status\G;
# 显示结果是： mysql-bin.000034

# 执行 flush logs 操作，生成新的 BINLOG
flush logs;

# 查看正在使用的 Binlog 文件
show master status\G;
# 结果是：mysql-bin.000035

确定恢复数据的步骤：

这里主要是有两条误删的操作，数据行的误删和表的误删。有两种方式进行恢复。

• 方式一：首先恢复到删除表操作之前的位置，然后再单独恢复误删的数据行。
• 方式二：首先恢复到误删数据行的之前的位置，然后跳过误删事件再恢复数据表操作之前的位置。

这里采用方式一的方案进行演示，由于是演示，就不额外找一个临时库进行全量恢复了，直接进行操作。

查询创建表的事件位置和删除表的事件位置

#  根据时间确定位置信息
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-datetime  "2019-11-22 14:00:00" \
 --database test_binlog  mysql-bin.000034 | less

创建表的开始位置：

删除表的结束位置：

插入 name='xiaob' 的位置：

# 根据位置导出 SQL 文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-position "2508132" --stop-position "2511004" \
 --database test_binlog  mysql-bin.000034 \
 > /home/mysql_backup/test_binlog_step1.sql

mysqlbinlog --no-defaults --base64-output=decode-rows -v \
 --start-position "2508813" --stop-position "2509187" \
 --database test_binlog  mysql-bin.000034 \
 > /home/mysql_backup/test_binlog_step2.sql

# 使用 mysql 进行恢复
mysql -u cisco -p < /home/mysql_backup/test_binlog_step1.sql
mysql -u cisco -p < /home/mysql_backup/test_binlog_step2.sql

MySQL 5.7 中无论是否打开 GTID 的配置，在每次事务开启时，都首先会出 GTID 的一个事务，用于并行复制。所以在确定导出开始事务位置时，要算上这个事件。

在使用 --stop-position 导出时，会导出在指定位置的前一个事件，所以这里要推后一个事务。

对于 DML 的语句，主要结束位置要算上 COMMIT 的位置。

总结

在文章开始时，我们熟悉了操作 BINLOG 的两种方式 CLI 和 mysqlbinlog 工具，接着介绍了其间的区别和使用场景，对于一些大型的 BINLOG 文件，使用 mysqlbinlog 会更加的方便和效率。并对 mysqlbinlog 的一些常见参数进行了介绍。

接着通过使用 mysqlbinlog 实际模拟了数据恢复的过程，并在恢复数据时，提出了一些需要注意的事项，比如 flush logs 等。

最后在恢复数据时，要注意 start-position 和 end-position 的一些小细节，来保证找到合适的位置。

参考

point-in-time-recovery

recovery-from-backups

backup-policy.