1. 背景

常在河边走,哪能不湿鞋。运维多年，误删除数据这是偶尔会遇到的数据。如果你使用的是云数据库RDS，那么你应该可以借助RDS提供的能力，轻松把数据恢复到某个时间节点前。或者说你可以借用其他的binlog日志恢复工具能够轻松实现数据恢复。
为了加深印象，这里整理了以前写过的文章。例子比较简单，但是可以看出使用binlog日志恢复数据的过程。

2. 实验环境

系统版本：CentOS 7.8
Mysql版本：mysql-5.7.30

3. 实验步骤

3.1 开启binlog日志功能

Mysql5.7版本默认的binlog日志格式为row，这个格式会记录所有的mysql操作，为了保险起见，我们还是应该手动指定binlog的日志格式。设置的办法可以参考配置文件。

[mysqld]
log_bin=mysql-bin
server_id=1
binlog_format=ROW
expire_logs_days = 15

设置之后需要重启mysql服务才能起效

接下来可以登陆数据库确认下binlog日志是否已经开启

show variables like '%log_bin%';

3.2 准备测试数据

我们新建一个test1的数据库，再新建一个test的表，然后插入几条数据，最后再删除这几条数据。

create database test1;
use test1;
create table test (id int);
insert into test values (1);
insert into test values (2);
delete  from test;

3.3 使用mysqlbinlog查看二进制日志文件

binlog日志文件我们可以使用mysqlbinlog命令来查看，直接输入命令查看。

mysqlbinlog --no-defaults mysql-bin.000004

可以看到对应的地方已经被处理过了,如果你使用的是mysql 5.6，那就没有这个问题的。

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#210810 11:14:15 server id 1  end_log_pos 123 CRC32 0x745dace3  Start: binlog v 4, server v 5.7.30-log created 210810 11:14:15
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
B+8RYQ8BAAAAdwAAAHsAAAABAAQANS43LjMwLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AeOsXXQ=
'/*!*/;
# at 123
#210810 11:14:15 server id 1  end_log_pos 154 CRC32 0x923b22b3  Previous-GTIDs
# [empty]

所以我们不能直接查看，需要加上对应的参数 --base64-output

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004 |less

在这里我们可以看到在对应的时间点数据库做了什么，我这里截取了部分的内容。我们可以看到在什么时间点在哪个position，数据库执行了什么操作。下面可以看到创建数据库、插入数据、删除数据的操作。针对DML语句，binlog日志并不是单纯记录我们执行的insert或者delete操作，而是会记录每一个字段的值。因为binlog日志是这样记录的，所以才能够使用binlog日志生成回滚的sql（这里我们不使用）。

# at 15366167
#210813 11:12:19 server id 1  end_log_pos 15366264 CRC32 0x02b82768     Query   thread_id=51710 exec_time=0     error_code=0
SET TIMESTAMP=1628824339/*!*/;
create database test1
/*!*/;
# at 15366264
#210813 11:12:19 server id 1  end_log_pos 15366329 CRC32 0xf9d4c6ff     Anonymous_GTID  last_committed=37836    sequence_number=37837   rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 15366329
#210813 11:12:19 server id 1  end_log_pos 15366431 CRC32 0xa14fe018     Query   thread_id=51710 exec_time=0     error_code=0
use `test1`/*!*/;
....
# at 15366496
#210813 11:12:19 server id 1  end_log_pos 15366569 CRC32 0x4a573b70     Query   thread_id=51710 exec_time=0     error_code=0
SET TIMESTAMP=1628824339/*!*/;
BEGIN
/*!*/;
# at 15366569
#210813 11:12:19 server id 1  end_log_pos 15366617 CRC32 0xfd91a057     Table_map: `test1`.`test` mapped to number 23253
# at 15366617
#210813 11:12:19 server id 1  end_log_pos 15366657 CRC32 0x7bc7ecfe     Write_rows: table id 23253 flags: STMT_END_F
### INSERT INTO `test1`.`test`
### SET
###   @1=1
# at 15366657
#210813 11:12:19 server id 1  end_log_pos 15366688 CRC32 0x4aefd889     Xid = 2624482
COMMIT/*!*/;
# at 15366688
#210813 11:12:19 server id 1  end_log_pos 15366753 CRC32 0xa11ed2fd     Anonymous_GTID  last_committed=37838    sequence_number=37839   rbr_only=yes
...
# at 15367010
#210813 11:12:19 server id 1  end_log_pos 15367083 CRC32 0x357a7afe     Query   thread_id=51710 exec_time=0     error_code=0
SET TIMESTAMP=1628824339/*!*/;
BEGIN
/*!*/;
# at 15367083
#210813 11:12:19 server id 1  end_log_pos 15367131 CRC32 0x989fb7e5     Table_map: `test1`.`test` mapped to number 23253
# at 15367131
#210813 11:12:19 server id 1  end_log_pos 15367176 CRC32 0x569a8025     Delete_rows: table id 23253 flags: STMT_END_F
### DELETE FROM `test1`.`test`
### WHERE
###   @1=1
### DELETE FROM `test1`.`test`
### WHERE
###   @1=2
# at 15367176
#210813 11:12:19 server id 1  end_log_pos 15367207 CRC32 0x49a8a413     Xid = 2624484
COMMIT/*!*/;
# at 15367207
#210813 11:12:21 server id 1  end_log_pos 15367272 CRC32 0x7e7c2070     Anonymous_GTID  last_committed=37840    sequence_number=37841   rbr_only=yes

3.4 通过mysqlbinlog恢复对应数据

在上面，我们是查看整个binlog日志文件，从文件的内容可以看到每一次操作（事务），binlog日志都会记录其position和时间。相应地，mysqlbinlog也提供了参数，让我们只查看对应位置的日志。如下：

#指定位置区间
mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-position="15366496" --stop-position="15366945"  mysql-bin.000004

#指定时间区间，这里因为我们的时间间隔非常小，不适合使用时间
mysqlbinlog --no-defaults --base64-output=decode-rows -v --start-datetime='2021-08-13 11:12:19' --stop-datetime='2021-08-13 11:12:19'  mysql-bin.000004

通过上面，我们可以截取到之前的insert语句出来了，这时候我们把数据导入到数据库中恢复就行了。

mysqlbinlog --no-defaults  --start-position="15366496" --stop-position="15366945"  mysql-bin.000004 |mysql  -uroot -p -h127.0.0.1 -P3306

最后我们查询下数据库，看下数据是不是已经恢复了。

在上面我在使用 mysqlbinlog 命令时都增加了 --no-defaults 参数，原因是我的mysql配置文件中的client有这样的配置 default-character-set = utf8。因为这个的存在，所以 mysqlbinlog 命令会报错。所以需要加上 --no-defaults 参数。

4. 总结

上面的实验比较取巧，数据量也就只有两条，并且我知道前后都做了什么。所以我才能知道指定恢复哪个position的数据来恢复数据。实际的生产中肯定不能这样来的，应该使用两种方式来恢复数据：

利用全量备份+binlog日志，把数据恢复到误操作前的状态。最好能指定恢复特定库特定表的数据，这样能带来更好的用户体验。
利用binlog日志输出回滚的sql语句，比如delete的操作回滚出insert的语句，然后再使用insert的语句去恢复数据。

MySQL中使用binlog日志恢复误删除的数据