mysql利用binlog日志恢复数据

1.前言

前段时间，同事操作生产数据库时，误删了一张表的数据，并且没有开启binlog日志，数据恢复起来很是头疼。这里简单的介绍下，数据误操作之后，如何通过binlog日志将原有数据恢复回来。
主要的思路是，利用备份数据恢复到某一时间点，然后之后的数据利用binlog日志恢复。

2.开启binlog日志

首先要确认数据库开启了binlog日志，可使用命令在数据库中查询。

show variables like '%log_bin%';

如果没有开启的话，可以在mysql的配置文件（linux环境下/etc/my.cnf）开启该配置。

#bin-log
server-id=1
log-bin=/var/lib/mysql/mysql-bin

3.开启定时备份

mysql备份数据其实比较简单，可以利用mysqldump命令导出对应数据。

mysqldump -uroot -p123456 work > /root/mysql_init/work_2021_08_01.sql

这里要提到的是，利用定时任务（crontab）去定期备份数据；首先要确保安装了crontab和cron,没有安装的先自行安装，因为比较简单，不再赘述。
创建备份脚本backup.sh，并编辑该脚本。

mysqldump -uroot -p123456 work > /root/mysql_init/work_`date +%Y-%m-%d`.sql

使用 crontab -e 命令，开始编辑定时任务,每天0点执行备份。

0 0 * * * /home/backup.sh

可以使用crontab -l 查看定时任务是否创建成功。

4.定位事件位置点

当开启了binlog日志之后，我们可以查看所有的binlog列表

mysql> SHOW BINARY LOGS;

我们也可以通过命令简单查看下对应日志的事件。

mysql> show BINLOG EVENTS in 'mysql-bin.000001'

当出现误操作情况时，可以先关闭该日志文件，生成新的日志文件，这样后续的操作都会记录到新的日志里面。

mysql> flush logs;

刷新之后，产生了新日志mysql-bin.000002 ，后续的操作都会记录到该日志中，我们只需要对mysql-bin.000001 日志进行恢复处理就可以了。
首先根据大概时间点导出binlog日志，根据日志操作记录进行分析。

mysqlbinlog --start-datetime="2021-07-31 0:00:00" --stop-datetime="2021-07-31 23:15:00" --base64-output=decode-rows -v  /var/lib/mysql/mysql-bin.000001  >/var/lib/mysql/bak_07_31.sql

这里需要注意的是，/var/lib/mysql/mysql-bin.000001 是你binlog存放的位置。–base64-output=decode-rows -v 是为了增加可读性。

可以看到每一次的操作记录，其中position信息是比较重要的，如果觉得按时间点导出日志不够精准，也可以利用position事件位置点来导出。

mysqlbinlog --start-position=4 --stop-position=1230 --base64-output=decode-rows -v  /var/lib/mysql/mysql-bin.000001  >/var/lib/mysql/bak_07_31.sql

5.恢复数据

导出了日志文件之后，就可以利用日志文件进行增量恢复了（记得先恢复定时备份的数据）。

mysql -uroot -p123456 < /var/lib/mysql/bak_07_31.sql

如此以来，数据就恢复成功了。

相关链接：
[1]mysql官网