记录一次mysql 误操作事件,通过binlog进行数据恢复

记录一次意外情况,具体什么原因嘛,你懂的

mysql V5.7 误操作的数据恢复

• 恢复数据的两种方案,仅供参考
• 使用binlog恢复数据.
• 每天有进行定时备份的,可以选择备份文件进行恢复.(备份的数据不可能是误操作之前刚好备份下来的,所以可以手动还原备份文件,然后使用binlog文件恢复误操作后一段时间的数据)

1. 前提条件 mysql 启用了 binlog 才能进行数据恢复
2. 查看是否开启,如果是OFF 的话就不用往下看了

   show variables like 'log_bin';
   

在进行数据恢复之前最好是执行以下以下命令:
flush logs;
将数据写入到binlog文件,然后重新生成一个binlog文件.然后在根据之前的binlog文件进行分析

可以将二进制文件解码后输出缓存文件进行分析

1. 导出到文件进行sql分析

mysqlbinlog  --no-defaults -vv --skip-gtids -v --base64-output='decode-rows' -d XXX binlog.000004 > temp.sql

–no-defaults 默认charset问题
-vv 参数为查看具体SQL语句及备注。
–base64-output=decode-rows 参数为解析Binlog日志文件。
-v 从binlog中重建sql语句
–skip-gtids 不保留全局事务标识符；而是使服务器像执行事务一样执行事务
-d 指定数据库

2. 查询指定关键字

mysqlbinlog -v --base64-output='decode-rows' binlog.000004 |grep -B11 'UPDATE'

3. 通过日志信息的也可以写语句查询

show binlog events in 'binlog.000004';
or
show binlog events in 'binlog.000004' from XXX limit 0,10;

XXX 是 position 信息

数据恢复

• 根据sql的起始位置恢复

mysqlbinlog  --no-defaults -vv --skip-gtids --start-position=XXX --stop-position=XXX binlog.000004  -d XXX | mysql -uroot -p

–no-defaults 默认charset问题
-vv 参数为查看具体SQL语句及备注。
–skip-gtids 不保留全局事务标识符；而是使服务器像执行事务一样执行事务
-d 指定数据库
–start-position sql语句起始位置 (可以通过分析二进制文件中的伪sql中得到位置信息)
–stop-position sql语句的结束位置 (可以通过分析二进制文件中的伪sql中得到位置信息)
| mysql -uroot -p 直接登录数据库 -u 用户

• 也可以使用时间段进行恢复

mysqlbinlog  --no-defaults -vv --skip-gtids --start-datetime="yyyy-12-25 10:19:19" --stop-datetime="yyyy-12-25 10:38:07" binlog.000004  -d XXX | mysql -uroot -p

–no-defaults 默认charset问题
-vv 参数为查看具体SQL语句及备注。
–skip-gtids 不保留全局事务标识符；而是使服务器像执行事务一样执行事务
-d 指定数据库
–start-datetime sql语句开始时间位置 (可以通过分析二进制文件中的伪sql中得到开始时间信息)
–stop-datetime sql语句的结束时间位置 (可以通过分析二进制文件中的伪sql中得到结束时间信息)
| mysql -uroot -p 直接登录数据库 -u 用户

- 注意事项

• 多个 binlog.000000 文件一定要按顺序来进行数据恢复 (可能会导致你之前创建或修改的表结构,后面没有就会直接报错)
• 出现语句指定报错的情况可以使用强制执行 -f:强行执行sql语句

mysqlbinlog  --no-defaults -vv --skip-gtids --start-datetime="yyyy-12-25 10:19:19" --stop-datetime="yyyy-12-25 10:38:07" binlog.000004  -d XXX | mysql -f -uroot -p