mysql数据误删除的恢复，drop表或库的恢复

迷南。 2022-05-15 07:51 3630阅读 0赞

昨天，我不小心，在没有完全沟通的情况下，直接删除了一个库，导致同事辛苦了一周的数据丢失，由于是整个库都删掉了，所以并不是单纯的去找误操作的日志，然后根据操作sql，去回滚数据。好歹会后恢复了。

下面就根据我恢复的经历，讲一下mysql数据库数据恢复的方法：

1. 首先，我慌的不行，还好有人提醒我还有binlog日志可以恢复数据，我才恍然大悟，以前没发生过这种事，还没遇到过，环境如下：

mysql 5.6.37

centos7.2

binlog 格式row    \#\# 这个很重要，必须开启

假设需要恢复数据的库为test

2 . 用mysql自身自带的工具，提取出binlog日志进行分析

mysqlbinlog --base64-output=decode-rows -v --start-datetime=“2017--11-01 00:00:00” --stop-datetime="2017-11-11 00:00:00" --database=test mysql-bin.000012 > aaa.sql

\--base64-output=decode-rows 是在你的binglog格式为row时，进行解码，然后翻译成常见的sql语句供我们查看分析。

内容大致如下:

1.  `#171008 12:35:19 server id 1 <strong><span style="color:#ff0000;">end_log_pos 69295</span></strong> CRC32 0x015beaa8 Query thread_id=1 exec_time=0 error_code=0`
2.  `SET TIMESTAMP=1507437319/*!*/;`
3.  `COMMIT ## end_log_pos 为标识点，需要注意`

然后，根据实际情况，找到自己误操作的起始点和结束点，  就是两个end\_log\_pos的值。然后导出为sql文件，需要将sql文件反写，也就是insert变成delete等，可以参考使用binlog2sql工具。

mysqlbinlog --start-position=123212(起始点) --stop-position=3333333(结束点) --database=test mysql-bin.000012 > restore.sql   ## 起始点结束点则是操作的 end_log_pos值

ps： 另外，--start-position 指定的end\_log\_pos是不会被提取的，会从指定的这个end\_log\_pos的下一条日志开始。

但 --stop-position 指定的end\_log\_pos是会被提取出来的。大家提取指定节点之间的操作日志时需要注意。

然后执行：

mysql [-f] -u root -p < restore_fanxie.sql     ## 执行导出的sql文件的反写sql，怎么反写可以自己手动也可以用工具：binlog2sql

\-f : 这个为忽略错误，继续执行的参数，可以根据情况确定是否使用。

**重点来了：**

如果像我一样**直接drop表或者库的话。在binlog日志里是不会记录删除的数据的，所以没办法通过这样来恢复数据**。这种时候就要找一个起始点，以删除操作之前的点为结束点，将数据binlog重新执行一遍，相当于重新重复所有的操作，来恢复数据。以最近的备份数据的备份时间来作为起始点，drop操作的前一个操作为结束点。

mysqlbinlog --start-position=123212(起始点) --stop-position=3333333(结束点) --database=test mysql-bin.000012 > restore.sql

中间，需要先将备份数据覆盖原数据： 先删除掉原数据库，新建一个同名数据库，然后将备份数据导入：

mysql -u root -p test < test_bakup.sql

然后执行：

mysql [-f] -u root -p < restore.sql

**如果没做备份怎么办，没有每天的全量备份数据**。那怎么办，一些drop了表或数据库怎么办，怎么恢复呢？！  网上说，一般是没希望的，但实际上并不是的，还是可以恢复的，但，前提是，**你的binlog日志文件齐全。**

怎么做呢，很简单，一样的想法，**从头执行所有操作**，也就是从你的binlog日志文件里，将相关数据库的所有日志数据全部导出为sql文件，然后依次全部重新执行，也就是从执行如下命令：

mysqlbinlog --database=test mysql-bin.000001（这里依次将所有binlog日志里相关库的日志都提出来） > restore01.sql   ## 依次从01一直到你现在最新的binlog文件

**ps：最新的binlog日志时，需要设定结束点，结束点为你drop表或库的上一个操作的end\_log\_pos。**

然后，依次执行:

mysql [-f] -u root -p < restore01.sql(从01一直到最新的)  ## 依次执行，就相当于从新重复从你mysql服务第一次启动开始，到你删除库之前的所有对于test数据库的操作。

**\[plain\]** [view plain][] [copy][view plain]