mysql数据库逻辑备份与恢复_[数据库]mysql 开发进阶篇系列 43 逻辑备份与恢复(基于时间和位置的不完全恢复)...

[数据库]mysql 开发进阶篇系列 43 逻辑备份与恢复(基于时间和位置的不完全恢复)

0 2018-09-27 03:00:54

一. 概述

在上篇讲到了逻辑备份，使用mysqldump工具来备份一个库,并使用完全恢复还原了数据库。在结尾也讲到了误操作是不能用完全恢复的。解决办法是:我们需要恢复到误操作之前的状态，然后跳过误操作语句。再恢复后面执行的语句，完成我们的恢复，这种恢复叫“不完全恢复”。在mysql 中，不完全恢复分为基于时间点的恢复和基于位置的恢复。

1.1 基于时间点恢复步骤　　下面来模拟基于时间点恢复，操作步骤如下：

(1)先逻辑备份一个库。

(2)模拟用户操作一些数据。

(3)误操作发生，记住时间点。

(4)还原备份库。

(5)使用mysqlbinlog --stop-datetime恢复到误操作前的时间点。

(6)跳过误操作时间点，使用mysqlbinlog --start-datetime继续执行后面的binlog，完成恢复。

1.2  基于时间点恢复演示

下面以test库的testbackup表为例，该表有6条数据，如下图所示：-- 共6条数据SELECT * FROM testbackup

-- 当前log文件序号是:mysql-bin.000040 SHOW MASTER STATUS

步骤1：备份test库，先定位到/usr/local/mysql/data目录下，备份完成后此时的log文件序号mysql-bin.000041，备份脚本下图所示：[root@hsr data]# pwd/usr/local/mysql/data[root@hsr data]# mysqldump -uroot -p --single-transaction -F test > test.dmpEnter password: [root@hsr data]# ls -l test.dmp-rw-r--r-- 1 root root 321260 9月 26 09:49 test.dmp

步骤2： 进行正常用户操作，新增了二条数据，此时表中共8条数据。INSERT INTO testbackup VALUES(7,'田七')INSERT INTO testbackup VALUES(8,'小康')

步骤3：误操作发生，删除了一条数据id为3的,此时表中共7条数据，误删除时间点是10：01分钟DELETE FROM testbackup WHERE id=3

步骤4：还原数据库后，此时testbackup表只有最初的6条数据[root@hsr data]# mysql -uroot -p test < test.dmpEnter password:

步骤：5和6-- 切换到binlog日志目录下[root@hsr mysql]# pwd/var/lib/mysql-- 恢复到误操作前的时间点[root@hsr mysql]# mysqlbinlog --stop-datetime="2018-09-26 9:59:59" mysql-bin.000041 | mysql -uroot -pEnter password: -- 跳过误操作时间点,继续执行后面的binlog[root@hsr mysql]# mysqlbinlog --start-datetime="2018-09-26 10:02:59" mysql-bin.000041 | mysql -uroot -pEnter password:

最后经过上面的步骤，误操作恢复完成了，最后查看该表的数据如下。SELECT * FROM testbackup

1.3 基于位置的恢复

与时间点的恢复不同，基于位置的恢复更加精确，因为一个时间点可能有很多条sql语句同时执行。下面模拟操作步骤：1，2，3，4操作步骤与上面相同。

(5) 分析误位置行号

方法1：对于大日志文件，将误操作时间点范围内(2-5分钟)的binlog日志复制到另一个小文件中，方便查找分析位置行号。

方法2：对于小日志文件，使用SHOW BINLOG EVENTS 查看误操作位置行号，前提是需要在my.cnf中设置binlog_rows_query_log_events=1

(6) 使用mysqlbinlog --stop-position恢复到误操作前的位置号。

(7) 跳过误操作位置号,使用mysqlbinlog --start-position继续执行后面的binlog，完成恢复。

1.4基于位置的恢复演示-- 共8条数据SELECT * FROM testbackup-- 当前mysql-bin.000041 SHOW MASTER STATUS

步骤1：备份test库，先定位到/usr/local/mysql/data目录下，备份完成后此时的log文件序号mysql-bin.000042，备份脚本下图所示：[root@hsr data]# pwd/usr/local/mysql/data[root@hsr data]# mysqldump -uroot -p --single-transaction -F test > test2.dmpEnter password: [root@hsr data]# ls -l test2.dmp-rw-r--r-- 1 root root 321286 9月 26 14:02 test2.dmp

步骤2：进行正常用户操作，新增了二条数据，此时表中共10条数据。INSERT INTO testbackup VALUES(9,'小王') INSERT INTO testbackup VALUES(10,'小李')

步骤3：误操作发生，删除了一条数据id为5的,此时表中共9条数据，误删除时间点是14：06分钟DELETE FROM testbackup WHERE id=5

步骤4：还原数据库后，此时testbackup只有最初的8条数据[root@hsr data]# mysql -uroot -p test < test2.dmpEnter password:

步骤5：分析误位置位置行号-- 方法1：[root@hsr mysql]# pwd/var/lib/mysql[root@hsr mysql]# touch tmp.sqlmysqlbinlog --base64-output=decode-row -v --start-datetime="2018-09-26 14:05:59" --stop-datetime="2018-09-26 14:07:59" mysql-bin.000042 > tmp.sql[root@hsr mysql]# more tmp.sql

查看tmp.sql文件，找到删除的时间是14:06:22时,开始位置是1077，结束位置是1155。

-- 方法2:SHOW BINLOG EVENTS IN 'mysql-bin.000042';

这里找到真正删除行是1077， commit提交后下一行1155。

步骤6-7：[root@hsr mysql]# mysqlbinlog --stop-position="1077" mysql-bin.000042 | mysql -uroot -pWARNING: The range of printed events ends with a row event or a table map event that does not have the STMT_END_F flag set.

This might be because the last statement was not fully written to the log, or because you are using a --stop-position or --stop-datetime

that refers to an event in the middle of a statement. The event(s) from the partial statement have not been written to output.Enter password: [root@hsr mysql]# mysqlbinlog --start-position="1155" mysql-bin.000042 | mysql -uroot -pEnter password: [root@hsr mysql]#

最后经过上面的步骤，误操作恢复完成了，最后查看该表的数据如下。SELECT * FROM testbackup

本文网址：http://www.shaoqun.com/a/417846.html

*特别声明：以上内容来自于网络收集，著作权属原作者所有，如有侵权，请联系我们：admin@shaoqun.com。

MYSQL

0