通过binlog文件恢复mysql数据的三种方式

由于数据库被攻击，导致大量数据被篡改删除，还好做了数据库的备份，于是记录一下通过binlog日志恢复数据的过程。

1.首先确保的是数据库开启了binlog日志，可先使用命令

show variables like “%log_bin%”;

查看是否开启，如果Value 为 OFF 则未开启日志文件，可在my.cnf或my.ini 文件中的 [mysqld] 添加

log-bin = /var/lib/mysql/mysql-bin.log

然后重启mysql数据库即可，如需要可配置日志大小和过期时间等参数，一般来说开启binlog日志大概会有1%的性能损耗。

2.开启binlog日志后，会保存在

/var/lib/mysql

目录下，文件名为mysql-bin.000001和mysql-bin.index的文件，mysql-bin.index该文件包含所有的二进制日志；mysql-bin.000001日志文件是会在每次重启数据库或者使用

FLUSH LOGS

命令会生成新的日志文件，文件编号+1（mysql-bin.000002），此时就可以通过binlog文件使用命令恢复数据。

3.通过binlog恢复数据有三种方式，一种是通过binlog中的position id恢复,首先通过备份将数据导入数据库，然后将后面缺失的数据库操作通过binlog恢复
使用命令

mysqlbinlog --start-position=100 --stop-position=500 --database=test
/var/lib/mysql/mysql-bin.000001 | /usr/bin/mysql -u root -p passwd -v
database_name

我们可以使用命令查看binlog文件中的position id

show binlog events in ‘mysql-bin.000001’;

database_name为要恢复的数据库名称
这种是需要知道数据操作保存在日志中的position id的起始位置，而这起始位置是不好判断的，如果是重复的操作，那么执行命令时，会抛出数据重复的异常并停止执行；
这时使用第二种方式，通过时间点恢复是比较好的选择

mysqlbinlog --start-datetime=“2020-03-20 10:00:00” --stop-datetime=“2020-03-21 10:00:00” mysql-bin.000001 | mysql -u root -passwd database_name

database_name为要恢复的数据库名称
–start-datetime 恢复的起始时间
–stop-datetime 恢复的结束时间

但是通过这种方式恢复数据，也会有着相同的问题，如果在同一时间点进行了多个操作导致数据重复，也会报错停止执行，这时我们也不好判断到底是哪条命令导致重复；

这时第三种方式可能更加适合我们，我们可以先使用命令转换成sql文件

mysqlbinlog --start-datetime=“2020-03-20 10:00:00” --stop-datetime=“2020-03-21 10:00:00” mysql-bin.000001 -d database_name> filename_binlog.sql

database_name为binlog文件中需要恢复数据的mysql数据库名称（可能存在多个库操作保存在一个binlog文件）;

如果执行命令报错:

unknown variable ‘default-character-set=utf8’

是因为mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8这个指令，我们在命令中加入-no-defaults即可

mysqlbinlog --no-defaults --start-datetime=“2020-03-20 10:00:00” --stop-datetime=“2020-03-21 10:00:00” mysql-bin.000001 -d database_name> filename_binlog.sql

但是转化成的sql文件不能直接导入mysql数据库的方式执行，它本质上还是一个二进制的文本文件，我们可能通过

source filename_binlog.sql

执行文件，这种方式在遇到错误时会继续执行，就能比较好的避免上面说到的遇到数据重复时，报错停止执行的问题，将缺失的数据操作执行下去。

以上，因为是一段时间后记录，可能出现遗漏和和偏差，欢迎补充指导。