基于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的數據也恢復了。

至此，我們的恢復就完成了。

總結

以上是生活随笔為你收集整理的mysql 日志节点恢复_基于binlog二进制日志的MySQL恢复笔记的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。