mysql使用binlog备份恢复_利用mysqldump和binlog进行数据库备份和恢复操作

写在前面

mysqldump用于备份数据，进行全库的全量备份，或者单库备份。

binlog用于增量备份，其增量备份的前提是建立在全量备份的基础之上的。

即一次完整的恢复需要mysqldump备份文件+binlog才能正确还原回数据。

———————————————————————————————————————————————————————————

一、使用mysqldump备份全库+binlog备份并进行恢复。

备注：此处binlog的格式为Mixed的，自己感觉设置为Mixed格式好一点。

1、首先创建test库和对应的表tb1、tb2

2、进行全库备份

3、此时我们在往tb2表内添加一些数据，这时插入的记录记录在binlog中。

4、这时我们模拟误删操作，对tb2进行全表update

5、查看当前binlog文件名称【此时建议刷新下binlog---> flush logs】，这次的update同样也记录到binlog中。我这次测试没有刷新binlog，若刷新了binlog，则对应的操作应该在上一个binlog文件内。

此时我们进行数据恢复

、、、

恢复思路：从全库备份中恢复test库，在利用binlog进行数据的增量恢复。

、、、

1、全备中恢复指定库

从 mysqldump 全备中恢复指定库，使用--one-database 简写-o的参数，极大地方便了我们恢复数据的灵活性

mysql -uroot -pTcdn@2007 -o test < all.sql

查看数据已经恢复到使用mysqldump备份前的状态了。

2、在使用binlog进行增量备份，恢复7、8、9字段的数据

登录数据，查看binlog的events

我们看到在position位置为2304的时候进行了update操作，因此我们从start-position=123到stop-position=2132这段时间恢复，即跳过update操作。把mysqldump到产生binlog的这段时间所有的操作在回放以便。

使用mysqlbinlog 指定起始pos和结束pos进行恢复。

这样我们就把数据恢复了。

二、现在我们在测试使用mysqldump进行单库备份+binlog增量备份进行数据测试。

1、首先和前面一样，创建两个表tb1、tb2

2、然后我们在进行单库备份

这样我们就有了这样一个针对test库的一个备份(和生产环境备份策略一致)

3、同样我们进行数据插入和全表update操作。

此时模拟误删操作了，并且此时我们刷新下binlog(原因为将误删操作截止到这个binlog，以便我们查找信息)

4、下面我们恢复数据。

此时看到已经恢复了部分数据，这是基于mysqldump的备份恢复的。

下来我们在利用binlog日志进行数据恢复

5、登录数据库内，查看binlog的events

我们看到在position位置为7485的时候进行了update操作，因此我们从start-position=123到stop-position=7313这段时间恢复，即跳过update操作。把mysqldump到产生binlog的这段时间所有的操作在回放以便。

使用mysqlbinlog 指定起始pos和结束pos进行恢复。

此时数据已经全部恢复了。

三、以上使用mysqldump备份数据的时候我们都是备份的全库或者单库的。而在实际的生产环境中我们一般也是这两种方式进行备份，我理解就是备份的比较粗略。没有细化到表。

但是一般我们误删的操作基本都是针对表或表里面的字段进行操作的。

因此我这边测试下了，备份的时候使用备份单库，但是恢复的时候把有问题的表从库中抽出来，然后在恢复。最后结合binlog进行数据还原。

1、首先test库内有两张表tb1、tb2

2、此时我们进行单库备份，即备份数据库test

3、在tb2表内插入数据，并进行数据误删操作(全表update)

此时刷新binlog

4、此时恢复我们先从单库备份test.sql中把tb2这张表抽出来。使用sed -n即可

sed -n -e '/Table structure for table tb2/,/UNLOCK TABLES/p' test.sql > tb2_$(date +%F).sql

然后我们在进行恢复(和前面一样的方式，只是后面的数据文件换了)

5、此时我们在使用binlog这种方式进行增量恢复，进入到数据库内查看binlog的events。

mysql> show binlog events in "mysql-bin.000010";

我们看到在position位置为16556的时候进行了update操作，因此我们从start-position=123到stop-position=16384这段时间恢复，即跳过update操作。把mysqldump到产生binlog的这段时间所有的操作在回放以便。

使用mysqlbinlog 指定起始pos和结束pos进行恢复。

此时数据已经恢复了。

总结

数据库备份使用mysqldump+binlog的这种放手进行全量+增量的方式备份操作。

无论使用mysqldump的方式进行全库、单库、单表(此类型未测试，但感觉可以)备份。都能恢复数据库。在加上binlog这种增量的备份进行增量还原即可完成数据库的迁移，即出现误操作的时候可以使用这种方式尝试进行恢复。