基於binlog二進位日誌的MySQL恢復筆記
- 2019 年 10 月 5 日
- 筆記
基於binlog二進位日誌的MySQL恢復筆記
剛好複習到這裡，順手做個小實驗，記錄下。
總的操作流程：
step0、關掉資料庫的對外訪問【防止用戶操作繼續寫入這個庫】
step1、mysqlbinlog 導出相關時間段資料庫的二進位日誌
step2、編輯today.sql找到誤操作的那幾條數據，刪除並保存。
step3、執行全備份恢復 mysql -e 'source /root/backup.sql;'
step4、用二進位日誌恢復今天的修改 mysql -e 'source /root/today.sql;'
step5、登錄mysql，驗證數據是否回來了。
原始資料庫某表的內容如下：
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
備份了下數據，備份為backup.sql，模擬當做前一天的全備份文件。
然後隨便進行了一些操作，如下：
INSERT INTO students VALUES(30,'lee',21,'M',2,1); DELETE FROM students where `StuID`=10; update students set age=10 where `StuID`=1;
執行後效果如下：
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 10 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 30 | lee | 21 | M | 2 | 1 |
+-------+---------------+-----+--------+---------+-----------+
假如突然DBA發現剛才的DELETE不應該執行，必需要恢復這條數據。
step0、這時候，首先，我們要關掉資料庫對外訪問的許可權，防止有用戶寫入數據，干擾恢復操作。
(可以修改前端的web連接資料庫的文件，將其改到其它從節點，雖然用戶無法寫，但最起碼比網站無法訪問要強些)
step1、先提取出誤操作這段時間的二進位日誌，取名為today.sql，如下：
mysqlbinlog --start-datetime='2016-07-12 23:51:48' --stop-datetime='2016-07-12 23:53:00' /usr/local/mariadb/var/mysql-bin.000033 > /root/today.sql
step2、編輯這個today.sql，找到剛才的那條DELETE操作，剔除DELETE語句，保存退出。
step3、用全備份backup.sql恢複數據：
mysql -e 'source backup.sql;'
恢復完的效果如下：
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
+-------+---------------+-----+--------+---------+-----------+
15 rows in set (0.00 sec)
可以看到第一天數據又恢復了最原始的狀態，第10條數據又恢復回來了，但是INSERT的那條數據卻沒有了，因此我們還要使用二進位日誌繼續恢復。
step4、繼續用二進位日誌恢復：
mysql -e 'source today.sql;'
step5、查看恢復後的結果：
恢復完的效果如下：
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 10 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 30 | lee | 21 | M | 2 | 1 |
+-------+---------------+-----+--------+---------+-----------+
16 rows in set (0.00 sec)
可以看到，第一條數據的Age已經改為我們要求的值了，INSERT的最後一條數據也恢復回來了， 誤刪除的StuID為10的數據也恢復了。
至此，我們的恢復就完成了。