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


    一. 概述

             在上篇讲到了逻辑备份,使用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.dmp
    Enter 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.dmp
    Enter 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 -p
    Enter password: 
    
    -- 跳过误操作时间点,继续执行后面的binlog
    [root@hsr mysql]# mysqlbinlog --start-datetime="2018-09-26 10:02:59" mysql-bin.000041 | mysql -uroot -p
    Enter 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.dmp
    Enter 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.dmp
    Enter password:

          
        步骤5:分析误位置位置行号

    -- 方法1:
    [root@hsr mysql]# pwd
    /var/lib/mysql
    [root@hsr mysql]# touch tmp.sql
    mysqlbinlog  --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 -p
    WARNING: 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 -p Enter password: [root@hsr mysql]#

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

    SELECT * FROM testbackup

          

  • 相关阅读:
    WIKI·常用数据库默认端口
    .Net·VS·三板斧解决断点无法命中的多种情况
    异常处理·EF·准备命令定义时发生错误。有关详细信息,请参阅内部异常
    H.264的三种开源编码器比较
    Color Basics
    重建Windows 7的图标缓存
    Prebuilt binaries of PCL (point cloud library) for Linux
    清除ubuntu内核列表中的旧内核
    H.264开源解码器评测
    MATLAB中设置figure的边框
  • 原文地址:https://www.cnblogs.com/MrHSR/p/9705107.html
Copyright © 2020-2023  润新知