MYSQL Binlog恢复误删数据库详解

当我们在MySQL数据库中误删了某些数据表或者整个数据库时，可以通过MySQL Binlog来恢复数据。下面就来详细讲解MySQL Binlog恢复误删数据库的完整攻略。

什么是MySQL Binlog

MySQL Binlog全称为Binary Log，即二进制日志。MySQL在进行数据操作时，会将操作记录在二进制日志中。二进制日志包含了所有的数据更改操作，包括增删改查。它记录了MySQL Server上所有的数据更新操作，提供给主从同步、数据备份和恢复等功能使用。

为什么可以通过MySQL Binlog恢复误删数据库

MySQL Binlog可以恢复误删数据库的原理是通过MySQL Binlog中记录的操作语句，再次执行这些语句来重新构建原数据库。因此，在使用MySQL Binlog恢复误删数据库前，我们需要先找到误删数据库的时间点。同时，需要保证MySQL Binlog没有过期并且没有被清除。

操作步骤

确认误删除库的时间点

首先确认误删数据库的时间点。可以通过MySQL Binlog查询命令查看：

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000068 | grep -B3 -i 'drop database'

上述命令中，mysql-bin.000068为二进制日志文件的文件名。通过上述命令可以找到误删数据库的时间点和具体的操作语句。

从误删除库的之后的Binlog文件中找到对应SQL语句

根据误删数据库的时间点，找到从该时间点开始往后的MySQL Binlog文件。可以通过如下命令查看二进制日志文件名：

show binary logs;

找到最后一个二进制日志文件的文件名后，运行如下命令查看二进制日志文件内容：

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000069

根据上述操作可以找到恢复误删数据库的SQL语句。

执行SQL语句

将找到的所有SQL语句按顺序依次执行即可恢复误删数据库。如果误删除了单个数据表，则只需要执行对应的SQL语句即可；如果误删了整个数据库，则需要找到所有被DROP的数据表的SQL语句，并按顺序执行。

示例

假设误删了数据库testdb中的数据表users。下面以这个示例来说明使用MySQL Binlog恢复误删数据表的步骤。

确认误删除库的时间点

通过如下命令找到误删的时间点和具体的操作语句：

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000068 | grep -B3 -i 'drop database'

得到结果如下：

1111 13:14:22 server id 1  end_log_pos 434 Query thread_id=1   exec_time=0 error_code=0
SET TIMESTAMP=1591860862/*!*/;
drop database testdb
/*!*/;
# at 434
#200618 13:14:22 server id 1  end_log_pos 471 Query thread_id=1   exec_time=0 error_code=0
SET TIMESTAMP=1591860862/*!*/;
use `testdb`/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */;
/*!*/;
# at 471
#200618 13:14:22 server id 1  end_log_pos 507 Query thread_id=1   exec_time=0 error_code=0
SET TIMESTAMP=1591860862/*!*/;
drop table if exists `testdb`.`users` /* Table structure for table `users` */ /*!40101 set @saved_cs_client     = @@character_set_client */ /...[以下省略]

根据上述结果，可以看到误删操作在2020-06-18 13:14:22，操作语句为drop database testdb和drop table if existstestdb.users``。

从误删除库的之后的Binlog文件中找到对应SQL语句

查看最后一个二进制日志文件：

show binary logs;

得到结果如下：

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000068 | 107374191 |
| mysql-bin.000069 | 107374190 |
| mysql-bin.000070 | 107374190 |
| mysql-bin.000071 | 107374190 |
+------------------+-----------+

因此，需要查看mysql-bin.000069这个二进制日志文件。命令如下：

mysqlbinlog --base64-output=decode-rows -v mysql-bin.000069

找到对应的SQL语句：

USE `testdb`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;