烂泥：通过binlog恢复mysql数据库

╰半橙微兮° 2021-12-23 07:41 307阅读 0赞

本文由[秀依林枫][Link 1]提供友情赞助，首发于[烂泥行天下][Link 2]。

在上一篇文章，我们讲解了有关mysql的binlog日志的基础知识。这篇文章，我们来讲解如何通过mysql的binlog日志来恢复数据库。

在使用binlog日志来恢复数据库之前，我们有一些前提工作需要做。步骤如下：

1、创建新的数据库ailanni

2、创建新的表ilannitable

3、向表ilannitable插入数据

4、刷新mysqlbin日志

5、完整备份ailanni数据库

6、删除表ilannitable插入的部分数据

7、删除数据库ailanni

8、各个binlog日志的内容解析

9、恢复ailanni全部数据

10、恢复ailanni数据库指定的部分

**一、** **创建新的数据库ailanni**

使用以下命令创建数据库，如下：

mysql --h 192.168.1.213 -uroot -p123456

create database ailanni;

show databases;

[![clip\_image001][clip_image001]][clip_image001_clip_image001]

**二、** **创建新的表ilannitable**

使用以下命令创建表，如下：

use ailanni;

create table ilannitable(id int(4));

show tables;

[![clip\_image002][clip_image002]][clip_image002_clip_image002]

**三、** **向表ilannitable插入数据**

使用以下命令向表ilannitable中插入数据1、2，如下：

insert into ilannitable values(1);

insert into ilannitable values(2);

select id from ilannitable;

[![clip\_image003][clip_image003]][clip_image003_clip_image003]

**四、** **刷新mysqlbin日志**

此时mysql的binlog日志文件只有一个mysql-bin.000001，如下：

show master logs;

[![clip\_image004][clip_image004]][clip_image004_clip_image004]

注意:此时mysql的binlog文件为mysql-bin.000001，并且数据库ailanni中只有1、2两条数据。如下：

select id from ilannitable;

[![clip\_image005][clip_image005]][clip_image005_clip_image005]

现在我们来刷新binlog日志，生成新的binlog日志文件mysql-bin.000002，如下：

flush logs;

show master logs;

[![clip\_image006][clip_image006]][clip_image006_clip_image006]

现在我们再次向ailanni数据库中插入新的数据3、4，如下：

insert into ilannitable values(3);

insert into ilannitable values(4);

[![clip\_image007][clip_image007]][clip_image007_clip_image007]

数据3、4插入完毕后，我们再次刷新binlog日志，生成新的binlog日志文件为mysql-bin.000003，如下：

[![clip\_image008][clip_image008]][clip_image008_clip_image008]

现在我们总共有三个binlog日志文件：mysql-bin.000001、mysql-bin.000002、mysql-bin.000003。其中在mysql-bin.000001中，我们向ilannitable表中插入两个数据1、2。在mysql-bin.000002中我们向ilannitable表中插入两个数据3、4。

我们可以查看binlog中的内容如下：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

[![clip\_image009][clip_image009]][clip_image009_clip_image009]

[![clip\_image010][clip_image010]][clip_image010_clip_image010]

**五、** **完整备份ailanni数据库**

现在我们来完整的备份ailanni数据库，使用mysqldump命令，如下：

/usr/local/mysql/bin/mysqldump -uroot -p123456 ailanni >/root/ailanni.sql

[![clip\_image011][clip_image011]][clip_image011_clip_image011]

查看备份文件是否与ailanni数据库的内容一致，如下：

cat ailanni.sql

[![clip\_image012][clip_image012]][clip_image012_clip_image012]

通过上图可知，备份的数据库文件与ailanni数据库的内容是一致的。

**六、** **删除表ilannitable插入的部分数据**

在第三、四步中，我们向表ilannitable中插入了1、2、3、4四个数据。为了模拟在ailanni数据库被破坏后的实际情形，我们现在把ailanni数据库中的3这条数据给删除，如下：

delete from ilannitable where id=3;

[![clip\_image013][clip_image013]][clip_image013_clip_image013]

此时我们再次刷新binlog日志，生成新的binlog日志文件为mysql-bin.000004，如下：

flush logs;

show master logs;

[![clip\_image014][clip_image014]][clip_image014_clip_image014]

**七、** **删除数据库ailanni**

在第六步中，我们删除了数据记录3，现在我们来删除ailanni数据库。如下：

drop database ailanni;

show databases;

[![clip\_image015][clip_image015]][clip_image015_clip_image015]

删除完毕后，我们再次刷新binlog日志，生成新的binlog日志文件为mysql-bin.000005，如下：

flush logs;

show master logs;

[![clip\_image016][clip_image016]][clip_image016_clip_image016]

**八、** **各个binlog日志的内容解析**

现在mysql的binlog日志文件有5个，从mysql-bin.000001到mysql-bin.000005，其中在mysql-bin.000001中我们向ilannitable表中插入了1、2两条数据。

在mysql-bin.000002中我们向ilannitable表中插入了3、4两条数据，在mysql-bin.000003中我们从表ilannitable删除了3这条数据。在mysql-bin.000004中我们把ailanni数据库给删除了。

各个binlog日志的SQL操作语句，如下：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001

[![clip\_image017][clip_image017]][clip_image017_clip_image017]

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

[![clip\_image010\[1\]][clip_image010_1]][clip_image010_1_clip_image010_1]

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

[![clip\_image018][clip_image018]][clip_image018_clip_image018]

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000004

[![clip\_image019][clip_image019]][clip_image019_clip_image019]

**九、** **恢复ailanni全部数据**

现在我们要恢复ailanni数据库，在此首先有关ailanni数据库是否存在的情况要说明下。

如果目前mysql数据库服务器上有ailanni这个数据库，那么我们在恢复数据时，就不需要重新创建ailanni数据库。如果目前mysql数据库服务器上没有ailanni这个数据库，那么我们就需要重新创建一个空的ailanni数据库。即该数据库只是一个空的库，里面没有任何表或者其他的元素。

如果不创建ailanni数据库的话，我们在通过mysql命令恢复数据库时，系统就会报错，如下：

[![clip\_image020][clip_image020]][clip_image020_clip_image020]

在第五步时，我们备份的数据库是ailanni数据库全部的数据。如果要恢复ailanni数据库全部数据的话，我们只需要执行mysql命令导入备份的sql文件即可。如下：

create database ailanni;

/usr/local/mysql/bin/mysql -uroot -p123456 ailanni</root/ailanni.sql

[![clip\_image021][clip_image021]][clip_image021_clip_image021]

查看恢复后的ailanni数据库是否和被删除之前的数据一样，如下：

mysql -uroot -p123456

use ailanni;

select id from ilannitable;

[![clip\_image022][clip_image022]][clip_image022_clip_image022]

通过上图，我们可以很明显的看到目前ailanni数据库已经被全部恢复，而且数据一个也没有丢失。

**十、** **恢复ailanni数据库指定的部分**

在通过mysql的binlog日志恢复数据库时，我们可以指定数据库恢复的位置、恢复的时间，同时也可以不指定数据库恢复的位置和时间。

有关mysqlbinlog命令的使用方法，我们可以通过mysqlbinlog的帮助命令进行查看，如下：

/usr/local/mysql/bin/mysqlbinlog --help

[![clip\_image023][clip_image023]][clip_image023_clip_image023]

**注意图中标记出来的数字，其中1和2是表示通过binlog恢复数据的开始和结束时间，3和4表示的binlog恢复数据的开始和结束位置。**

**其中时间我们都知道，看下binlog的日志文件内容就知道了。而位置就是我们在前面查看各个binlog日志内容时标记出来的数字部分。如下：**

[![clip\_image024][clip_image024]][clip_image024_clip_image024]

**图中被标出来的黄色部分数字5表示时间，6表示位置的节点，位置节点我们也称为binlog的pos点。**

**10.1****不指定位置和时间来恢复数据库**

我们先来测试不指定时间和位置来恢复数据库。

在第九步中我们恢复了ailanni全部的数据库，现在我们要求把数据库恢复到删除数据记录3时的状态。即ailanni数据库中只有1、2、4，这三条数据。这个时候我们就要根据mysql的binlog日志进行恢复，要不然达不到这个要求。

在第八步中我们已经分析了各个binlog日志中执行的SQL语句。根据分析，我们需要使用mysql-bin.000003这个binlog日志来恢复数据库。现在我们再次查看该binlog日志，如下：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

[![clip\_image018\[1\]][clip_image018_1]][clip_image018_1_clip_image018_1]

删除3这条数据后，ailanni数据库中的数据记录情况也就是我们恢复数据库后所要的状态。如下：

[![clip\_image025][clip_image025]][clip_image025_clip_image025]

现在我们来通过mysqlbin来恢复删除3这条数据时的数据库，使用如下命令：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123456

[![clip\_image026][clip_image026]][clip_image026_clip_image026]

通过上图我们可以看到此时数据库ailanni已经恢复到删除数据3时的状态了，ailanni数据库中确实只有1、2、4，这三条数据。这样也达到我们的要求了。

**重要说明：**

**通过上面的文章，我们知道现在ailanni数据库的情况是，已经恢复全部数据。**

**为了做下面的实验，我们就要把ailanni数据库恢复到还没有进行完全备份之前的状态，即ailanni数据库还没有数据的状态。我们需要做的是清空现在的ailanni数据库，保持ailanni是一个空的数据库。但是ailanni数据库的表结构、主键、外键等等信息都是完整的。**

**同时还要说明下：如果在同一个时间点，binlog日志中有执行过多条sql语句的话，那么我们在恢复数据库时，一定要根据pos点的位置来恢复数据，切记切记。因为此时在binlog日志中，时间节点是一样的，但是pos位置节点是唯一的。**

**因此在通过binlog恢复数据库时，强烈建议使用pos位置节点的方法来恢复数据。**

清空ailanni数据库使用以下命令，如下：

delete from ilannitable;

select id from ilannitable;

[![clip\_image027][clip_image027]][clip_image027_clip_image027]

有关这个情况，我会再写一篇文章。

现在我们得到的是一个没有数据的ailanni数据库，这样我们下面的实验就可以继续进行。

**10.2****指定时间来恢复数据库**

要求恢复到删除数据记录3时的数据库。即数据库中只有1、2、4，这三条数据。

还是和10.1要求是一样的，但是这次我们根据时间点来恢复数据。我们也需要一个空的ailanni数据库，这个我们可以通过前面的说明可以得到。

现在我们再次查看mysql-bin.000003文件，如下：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000003

[![clip\_image028][clip_image028]][clip_image028_clip_image028]

注意图中黄色部分标记出来的时间点，我们可以看到在这个时间点之后，数据3就被删除了。我们恢复数据，就恢复到这个时间点即可达到要求。使用以下命令进行恢复，如下：

/usr/local/mysql/bin/mysqlbinlog --stop-datetime='2014-11-17 11:30:43' /usr/local/mysql/data/mysql-bin.000003 |mysql -uroot -p123456

mysql -uroot -p123456

use ailanni;

select id from ilannitable;

[![clip\_image029][clip_image029]][clip_image029_clip_image029]

通过上图，我们可以看到通过时间点来恢复数据库也达到我们的要求，并且ailanni数据库中确实只有1、2、4，这三条数据。

**10.3****指定位置来恢复数据库**

要求把数据库ailanni恢复到插入数据3后，未插入数据4时的状态。即数据库ailanni中只有数据1、2、3，这三条数据。

分析要达到此要求，我们只能通过binlog日志的方式来恢复数据。根据第八步的binlog日志可以得知，要想恢复到此时的数据库，那么我们就要使用mysql-bin.000002这个日志文件。

要到达上述的目的，我们需要先来恢复数据1、2，使用mysql-bin.000001文件，如下：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 |mysql -uroot -p123456

[![clip\_image030][clip_image030]][clip_image030_clip_image030]

数据1、2恢复后，我们现在正式开始恢复插入数据3后，未插入数据4时的数据库。

再次查看mysql-bin.000002日志文件，如下：

/usr/local/mysql/bin/mysqlbinlog /usr/local/mysql/data/mysql-bin.000002

[![clip\_image031][clip_image031]][clip_image031_clip_image031]

注意图中标记出来的数字304，这个就是binlog的pos节点位置。我们可以看到在该pos节点后mysql才执行插入数据4的SQL语句。那么我们只需要把数据恢复这个节点即可。

使用如下命令进行恢复数据，如下：

/usr/local/mysql/bin/mysqlbinlog --stop-position=304 /usr/local/mysql/data/mysql-bin.000002 |mysql -uroot -p123456

[![clip\_image032][clip_image032]][clip_image032_clip_image032]

通过上图，我们现在可以看到ailanni数据库已经恢复到插入数据3之后，未插入4的状态。现在ailanni数据库中确实只有1、2、3，这三条数据，已经达到我们的要求。

到此我们有关通过binlog日志恢复mysql数据库告一段落。

转载于:https://www.cnblogs.com/ilanni/p/4107530.html

[Link 1]: http://xiuylf.taobao.com/
[Link 2]: http://www.ilanni.com/
[clip_image001]: https://images0.cnblogs.com/blog/62984/201411/191012305311565.png
[clip_image001_clip_image001]: /images/20211223/c0dcba9447d84d5a8728ce4a0fb47ffe.png
[clip_image002]: https://images0.cnblogs.com/blog/62984/201411/191012318435363.png
[clip_image002_clip_image002]: /images/20211223/b2af2fc342bc442ba4b48f7f505dc974.png
[clip_image003]: https://images0.cnblogs.com/blog/62984/201411/191012330009377.png
[clip_image003_clip_image003]: /images/20211223/034bd84a568f42b89908299cacaca4fb.png
[clip_image004]: https://images0.cnblogs.com/blog/62984/201411/191012341093705.png
[clip_image004_clip_image004]: /images/20211223/aa412ab2828b4876bbf67f59f6c978e1.png
[clip_image005]: https://images0.cnblogs.com/blog/62984/201411/191012358432675.png
[clip_image005_clip_image005]: /images/20211223/6cc4ca194a064f819e9b11885ab5391a.png
[clip_image006]: https://images0.cnblogs.com/blog/62984/201411/191012367961517.png
[clip_image006_clip_image006]: /images/20211223/80dcbbd16da74eedba13179d139e92dc.png
[clip_image007]: https://images0.cnblogs.com/blog/62984/201411/191012376875845.png
[clip_image007_clip_image007]: /images/20211223/6659f5a09a0748b6a82ca78244c80f70.png
[clip_image008]: https://images0.cnblogs.com/blog/62984/201411/191012385315187.png
[clip_image008_clip_image008]: /images/20211223/cbfce9fbd52d4cbea640b8b55f24650b.png
[clip_image009]: https://images0.cnblogs.com/blog/62984/201411/191012395467542.png
[clip_image009_clip_image009]: /images/20211223/44ab4d7bb96b41c5bbe2a01f7e16aed9.png
[clip_image010]: https://images0.cnblogs.com/blog/62984/201411/191012407507543.png
[clip_image010_clip_image010]: /images/20211223/17052abdc9fb462aa9be22435030ba0a.png
[clip_image011]: https://images0.cnblogs.com/blog/62984/201411/191012414842685.png
[clip_image011_clip_image011]: /images/20211223/c421853b10c44f59a811ce2c1a23cc41.png
[clip_image012]: https://images0.cnblogs.com/blog/62984/201411/191012424068770.png
[clip_image012_clip_image012]: /images/20211223/87f07866fa154d7993b65de0811ea059.png
[clip_image013]: https://images0.cnblogs.com/blog/62984/201411/191012430314413.png
[clip_image013_clip_image013]: /images/20211223/bc6d49cbc644487590584974a464c535.png
[clip_image014]: https://images0.cnblogs.com/blog/62984/201411/191012435004570.png
[clip_image014_clip_image014]: /images/20211223/946eff7d92b3453dbab9445c11ce6c2f.png
[clip_image015]: https://images0.cnblogs.com/blog/62984/201411/191012442036955.png
[clip_image015_clip_image015]: /images/20211223/9861d85cac814a8aa8695abad72e3891.png
[clip_image016]: https://images0.cnblogs.com/blog/62984/201411/191012448906111.png
[clip_image016_clip_image016]: /images/20211223/77d945115cc14325912285f273fb454e.png
[clip_image017]: https://images0.cnblogs.com/blog/62984/201411/191012456255954.png
[clip_image017_clip_image017]: /images/20211223/8b1b4d12ab63486c80afac593d063df5.png
[clip_image010_1]: https://images0.cnblogs.com/blog/62984/201411/191012465318810.png
[clip_image010_1_clip_image010_1]: /images/20211223/c23c5409a7534d8791f199a89c163c6d.png
[clip_image018]: https://images0.cnblogs.com/blog/62984/201411/191012475001882.png
[clip_image018_clip_image018]: /images/20211223/7d97874164e549038631ae8403e1c8aa.png
[clip_image019]: https://images0.cnblogs.com/blog/62984/201411/191012483752981.png
[clip_image019_clip_image019]: /images/20211223/25956d42960e4356b54a46b18d364312.png
[clip_image020]: https://images0.cnblogs.com/blog/62984/201411/191012491711636.png
[clip_image020_clip_image020]: /images/20211223/3ff38bdd72ef4599ae38314d85368fa5.png
[clip_image021]: https://images0.cnblogs.com/blog/62984/201411/191012499684992.png
[clip_image021_clip_image021]: /images/20211223/42934eec2176406caf5cc5f5eb451f42.png
[clip_image022]: https://images0.cnblogs.com/blog/62984/201411/191012505939634.png
[clip_image022_clip_image022]: /images/20211223/36907c5d01094f44b41578d3e54477cc.png
[clip_image023]: https://images0.cnblogs.com/blog/62984/201411/191012516409448.png
[clip_image023_clip_image023]: /images/20211223/2b755456319e44c4b4ae473b118d09ce.png
[clip_image024]: https://images0.cnblogs.com/blog/62984/201411/191012526092520.png
[clip_image024_clip_image024]: /images/20211223/4db30f7f4b3840c5b50e8527d02081b7.png
[clip_image018_1]: https://images0.cnblogs.com/blog/62984/201411/191012535784591.png
[clip_image018_1_clip_image018_1]: /images/20211223/0af53ab1ac0445d5899e1d2d58d3aaa7.png
[clip_image025]: https://images0.cnblogs.com/blog/62984/201411/191012544847447.png
[clip_image025_clip_image025]: /images/20211223/b2e40543f23844328b400a7ada010112.png
[clip_image026]: https://images0.cnblogs.com/blog/62984/201411/191012555465788.png
[clip_image026_clip_image026]: /images/20211223/e2980cab91e94efc9d0f6f49841735c5.png
[clip_image027]: https://images0.cnblogs.com/blog/62984/201411/191012565623845.png
[clip_image027_clip_image027]: /images/20211223/950d07f3539a4074bdba9db224a7b2d5.png
[clip_image028]: https://images0.cnblogs.com/blog/62984/201411/191012575004160.png
[clip_image028_clip_image028]: /images/20211223/738dc0d32c6346b285d665738c15da62.png
[clip_image029]: https://images0.cnblogs.com/blog/62984/201411/191012588905700.png
[clip_image029_clip_image029]: /images/20211223/717894ffad3f44dcb56d3f1aa88db850.png
[clip_image030]: https://images0.cnblogs.com/blog/62984/201411/191012597508272.png
[clip_image030_clip_image030]: /images/20211223/bb34b8dec6e5495ea7e6e51e09ebc13a.png
[clip_image031]: https://images0.cnblogs.com/blog/62984/201411/191013012341785.png
[clip_image031_clip_image031]: /images/20211223/f8439d6fd4fd403b9e7c622c8d4569b6.png
[clip_image032]: https://images0.cnblogs.com/blog/62984/201411/191013033593169.png
[clip_image032_clip_image032]: /images/20211223/b9e19a00849443fb8a58988f50979f30.png