在工作当中，有时候误删了数据，需要进行数据恢复。以下所有恢复方法都是基于开启了binlog日志情况下，如果没有开启binlog日志，就不用再往下看。

目前有两种方式可以恢复，一种是不借助第三方工具，使用官方mysqlbinlog命令来恢复。一种是使用第三方工具，另外分享一个第三方工具my2sql，也可以快速恢复数据。

官方mysqlbinlog命令

注意：官方mysqlbinlog命令我没有具体实操过，因为不能很直观的看到误删的具体sql，但理论上是可以恢复的。建议先搭建测试环境再进行验证。

按照指定时间段恢复

mysqlbinlog --no-defaults --start-datetime='2024-06-05 15:00:00' --stop-datetime='2022-06-05 16:25:00' /var/lib/mysql/data/bin.0000014 | mysql -uXXXX -pXXXX

按照pos事件位置号恢复

但有时候需要恢复的不是时间段，需要恢复误删的sql，通过mysqlbinlog命令将binlog转为sql，方便查询具体需要恢复的位置

mysqlbinlog --no-defaults --start-datetime='2024-06-05 15:00:00' --stop-datetime='2022-06-05 16:25:00' /var/lib/mysql/data/bin.0000014 > /home/backup/mysql/mysql_delete_20240605.sql

分析mysql_delete_20240605.sql文件，找到pos的起止点。

按照pos事件位置号恢复：

mysqlbinlog --no-defaults --start-position=352542100 --stop-position=362183044 /var/lib/mysql/data/bin.0000014 | mysql -uXXXX -pXXXX

第三方my2sql命令

注意：第三方my2sql命令在生产环境实操过，直接连接生产库，然后生成回滚sql，人为检查回滚sql后，在生产环境手动执行。

gitlab地址：https://github.com/liuhr/my2sql

此工具可以很直观的看到你具体误删时的原始sql，然后也可以生成回滚sql。

编译安装

需要安装go环境，go环境安装请自行百度。

git clone https://github.com/liuhr/my2sql.git
cd my2sql/
go build .

官方有已经编译好的CentOS 7.x版本二进制下载，可以直接使用。下载地址:https://github.com/liuhr/my2sql/blob/master/releases/centOS_release_7.x/my2sql

重要参数说明

参数

说明

-U

优先使用unique key作为where条件，默认false

-mode

repl: 伪装成从库解析binlog文件，file: 离线解析binlog文件, 默认repl

-local-binlog-file

当指定-mode=file 参数时，需要指定-local-binlog-file binlog文件相对路径或绝对路径,可以解析多个binlog文件，只需要指定起始文件名，会自动解析下个文件

-work-type

2sql：生成原始sql，rollback：生成回滚sql，stats：只统计DML、事务信息

-databases、-tables

库及表条件过滤, 以逗号分隔

-sql

要解析的sql类型，可选参数insert、update、delete，默认全部解析

-file-per-table

为每个表生成一个sql文件

-output-dir

将生成的结果存放到制定目录

解析出标准SQL-work-type=2sql

#伪装成从库解析binlog

./my2sql -host=192.168.20.104 -port=3306 -user=XXXX -password=XXXX -mode=repl -work-type=2sql -start-file=bin.0000014 -start-datetime "2024-06-18 10:00:00" -stop-datetime "2024-06-18 18:00:00" -output-dir=/root/

#直接读取binlog文件解析

./my2sql -host=192.168.20.104 -port=3306 -user=XXXX -password=XXXX -mode=file -local-binlog-file=bin.0000014 -work-type=2sql -start-file=bin.0000014 -start-datetime "2024-06-18 10:00:00" -stop-datetime "2024-06-18 18:00:00" -output-dir=/root/

解析出回滚SQL-work-type=rollback

#伪装成从库解析binlog

./my2sql -host=192.168.20.104 -port=3306 -user=XXXX -password=XXXX -mode=repl -work-type=rollback -start-file=bin.0000014 -start-datetime "2024-06-18 10:00:00" -stop-datetime "2024-06-18 18:00:00" -output-dir=/root/

#直接读取binlog文件解析

./my2sql -host=192.168.20.104 -port=3306 -user=XXXX -password=XXXX -mode=file -local-binlog-file=bin.0000014 -work-type=rollback -start-file=bin.0000014 -start-datetime "2024-06-18 10:00:00" -stop-datetime "2024-06-18 18:00:00" -output-dir=/root/

限制

  • 使用回滚/闪回功能时，binlog格式必须为row,且binlog_row_image=full， DML统计以及大事务分析不受影响

  • 只能回滚DML， 不能回滚DDL

  • 使用rollback功能时，要解析的binlog段，表结构要保持一致（例如：解析mysql-bin.000001文件，此binlog文件的的表有add column或drop column操作，则执行rollback可能会执行异常）

  • 支持指定-tl时区来解释binlog中time/datetime字段的内容。开始时间-start-datetime与结束时间-stop-datetime也会使用此指定的时区， 但注意此开始与结束时间针对的是binlog event header中保存的unix timestamp。结果中的额外的datetime时间信息都是binlog event header中的unix timestamp

  • 此工具是伪装成从库拉取binlog，需要连接数据库的用户有SELECT, REPLICATION SLAVE, REPLICATION CLIENT权限

  • MySQL8.0版本需要在配置文件中加入default_authentication_plugin =mysql_native_password，用户密码认证必须是mysql_native_password才能解析