mysql 開發進階篇系列 43 邏輯備份與恢復(基於時間和位置的不完全恢復)

一. 概述

         在上篇講到了邏輯備份，使用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