使用mysql的binlog功能恢复数据

「爱情、让人受尽委屈。」 2022-05-24 10:07 229阅读 0赞

环境：linux（centos6.8），mysql（5.6）

#### 1：开启binlog功能 ####

编辑 vim /etc/my.cnf，加上 log-bin =/app/mysql\_binlog/mysql-bin，顺便加上 expire\_logs\_days=7，后面的配置代表保留7天日志，接着创建/app/mysql\_binlog文件夹。最后重启mysql，service mysqld restart

![70][]

![70 1][]

#### 2：模拟被删除的数据 ####

打开数据库客户端工具，创建表，添加数据，并删除数据，这里以t\_ce\_msg为例，首先创建两条数据，1和2。

![70 2][]

然后删除1数据

![70 3][]

#### 3：查看binlog日志 ####

接着查看binlog日志，在这里我们直接使用客户端的功能就可以，比较方便，命令为 show binlog events in 'mysql-bin.000003';

![70 4][]

![70 5][]

![70 6][]

#### 4：恢复数据 ####

在日志中我们可以清楚的看到，其实我不是添加1和2数据然后删除1的，而是先添加1，删除1，又添加2的，不过这不重要，接下来就要使用binlog来恢复已经被删除的1数据了，注意看日志，日志里记录了我添加数据的sql和删除数据的sql，现在我要恢复数据，并不需要执行日志里那条删除1数据的sql，可以看到，添加1数据的开始（BEGIN）到提交（COMMIT），是从2051（Pos）到2399（End\_log\_pos）。执行恢复需要有用到mysqlbinlog命令，在这里，我直接连接到了数据库的服务器，进入mysql的bin目录进行操作的，命令 mysqlbinlog --start-position=2051 --stop-position=2399 /app/mysql\_binlog/mysql-bin.000003 | mysql -uroot -p ，回车后输入密码。

![70 7][]

![70 8][]

![70 9][]

#### 5：可能出现的问题 ####

执行恢复命令的时候，可能会出现ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password:yes)，这可能是一个bug，因为我之前刚装上mysql的时候就已经自己改了密码，长时间没用命令连接，就又出现这个错误，具体解决方法参考https://blog.csdn.net/zzq900503/article/details/14163769，注意mysql5.6和mysql5.7修改命令是不一样的。

记：由于是用于演示，所以是在添加数据之前就已经开启了binlog，并且也不会超过7天，如果是在正式环境，很可能binlog的记录并不是从头到尾的所有sql，所以就需要和全量备份配合使用，数据恢复期间，最好停止外界对数据库的访问，避免数据错误的出现。

[70]: /images/20220524/757781a807a247dc94803f0d1fd08531.png
[70 1]: /images/20220524/1f43714c784c46db81bf472ed058f16d.png
[70 2]: /images/20220524/8f146f4d7afc4904a220e83b28e1562f.png
[70 3]: /images/20220524/28f7ac149f2448eb826ab5dc55c9df0b.png
[70 4]: /images/20220524/70d7111e70bb4b7b9def66ab74ea5c5b.png
[70 5]: /images/20220524/33faafbbb17c410e8f537811d7e3f967.png
[70 6]: /images/20220524/0b44d103f70745a49992566d48c8c8e8.png
[70 7]: /images/20220524/e6f29051ec9f4bbba8007cdaf10083e8.png
[70 8]: /images/20220524/4fa7bb2ef42648008f22b7a4f07c3987.png
[70 9]: /images/20220524/2c3de5ff35144059ac7c36b42c7ddfaa.png