前言

很多年前，被公司外派到一家單位駐場開發(fā)一個OA項目，兩個開發(fā)對接各部門的需求，需求還要及時生效（一邊開發(fā)一邊使用）。有一次生產環(huán)境的一個bug本地沒辦法復現(xiàn)，由于沒有測試人員，也就不存在測試環(huán)境，所以本地連了生產庫去調試。不出意外的話要出意外了：在調試的過程中，我倆當作開發(fā)環(huán)境很自然的把數(shù)據(jù)給刪了。

作為一名只會CRUD的小白怎么會恢復數(shù)據(jù)這么高級的操作，不過還好，經過我倆一小時的百度，在各種ctrl+c、ctrl+v的命令操作下，最終成功的把數(shù)據(jù)恢復了。

如果我當時了解數(shù)據(jù)備份恢復，也不至于這么手忙腳亂的，所以程序員掌握數(shù)據(jù)的備份恢復操作還是很重要的。最近正好在輸出MySQL系列文章，所以在這里記錄一下MySQL數(shù)據(jù)備份和恢復的方法及操作，希望可以幫助到跟我一樣的小伙伴。

數(shù)據(jù)備份恢復工具

MySQL自帶了一個數(shù)據(jù)備份的客戶端mysqldump，使用mysqldump可以基于現(xiàn)狀生成一組SQL語句（建表語句、insert語句），在數(shù)據(jù)丟失時可以通過執(zhí)行這些SQL語句恢復到原始狀態(tài)，從而達到備份恢復效果。但是，當數(shù)據(jù)量很大的時候，這種方式就不是很適合了，因為mysqldump是單線程執(zhí)行，過多的SQL執(zhí)行會使整個恢復過程過于緩慢。

所以，基于此痛點，就誕生了一款開源的多線程備份恢復工具 mydumper，其特點就是多線程、快， 具體可以前往博客進行了解，這篇博客介紹的非常的詳細，這里就不多贅述。

以上兩種工具都屬于邏輯備份，何為邏輯備份？就是數(shù)據(jù)通過SQL語句的形式進行備份和恢復，總的來說執(zhí)行速度會很慢。

還有一種物理備份方式，簡單來說就是直接將表數(shù)據(jù).ibd文件、binlog、redolog等物理文件直接copy備份，相對邏輯備份來講物理備份速度會快很多，目前常用的物理備份工具有PXB(Percona XtraBackup) 以及MySQL8.0推出的新特性 Clone Plugin ，感興趣的可以自行前往了解。

數(shù)據(jù)備份策略

為了避免誤操作導致數(shù)據(jù)被刪除，通常在生產環(huán)境中會制定數(shù)據(jù)備份策略，比如用什么工具，備份周期是一天一次還是一周一次，每次備份是全量還是增量等，這個取決于數(shù)據(jù)的重要性、數(shù)據(jù)的變動頻率、備份成本等方面的需求。

下面將基于MySQL自帶的mysqldump進行數(shù)據(jù)備份，并演示一下數(shù)據(jù)被誤刪后的恢復操作。

數(shù)據(jù)備份恢復演示

備份前先看一下當前的數(shù)據(jù)情況。

備份數(shù)據(jù)

在使用mysqldump的時候根據(jù)自己的備份需求加一堆參數(shù)，比如下面這條命令：

mysqldump -uroot -pLeYk2qwd -h 127.0.0.1 -P3306 -A -R  --triggers --master-data=2  --single-transaction  > /backup/full.sql

• -u -p -h -P就不用說了，畢竟作為一個客戶端，連接MySQL服務還是需要用戶名密碼驗證的。
• -A 是用來備份這個MySQL實例所有的庫，如果要備份單個庫，參數(shù)為 ‘-B db1 db2’
• -R 是用來備份存儲過程及函數(shù)。
• --triggers 用來備份觸發(fā)器。
• --master-data=2 的作用是：在備份時記錄binlog的狀態(tài)信息，這個后面會用到。
• --single-transaction 的作用是：直接備份可能會因為時間過長而導致鎖等待問題。為了避免這種情況，該參數(shù)對InnoDB引擎的表數(shù)據(jù)進行快照備份，減少鎖等待的同時也保證了數(shù)據(jù)一致性。

更多的參數(shù)使用請參考官方文檔。

執(zhí)行上面的命令后就會得到一份sql備份文件。

一般數(shù)據(jù)量級在100G左右，備份時間大約在30分鐘左右，所以數(shù)據(jù)量很大的情況下建議物理備份。

模擬數(shù)據(jù)誤刪

執(zhí)行備份命令成功后進行刪庫或刪表操作，模擬誤刪場景

drop database test;

可以看到test庫已經被刪除。

恢復備份的數(shù)據(jù)

接下來就可以執(zhí)行恢復數(shù)據(jù)命令，將剛才備份的/backup/full.sql進行恢復，命令如下：

set sql_log_bin=0;
source /backup/full.sql;

set sql_log_bin=0;是將binlog日志記錄進行關閉，否則數(shù)據(jù)恢復時所執(zhí)行的sql語句也會被記錄到binlog中，binlog是不需要記錄恢復的操作。

命令執(zhí)行成功后，剛才被刪的庫以及表數(shù)據(jù)就被恢復了。

恢復未備份的數(shù)據(jù)

在實際應用中，恢復數(shù)據(jù)不是這么簡單的，因為備份操作基本上不會是實時的，如果昨天備份數(shù)據(jù)，今天誤刪了數(shù)據(jù)，那么在這之間的數(shù)據(jù)如何恢復？

這個時候就體現(xiàn)出binlog的作用了，之前的文章介紹過，binlog會記錄所有的增刪改操作，所以，未備份的數(shù)據(jù)就可以通過binlog進行恢復。如何恢復呢？

上面說到，mysqldump命令中有一個參數(shù)：--master-data=2，加上這個參數(shù)后，會在備份的sql文件中記錄此次備份的數(shù)據(jù)位于binlog的位置，如下圖

MASTER_LOG_FILE 的意思是此次的備份已經到‘mysql-bin.000004’這個文件了，備份最末端的數(shù)據(jù)在文件中的偏移量為MASTER_LOG_POS=2548。

基于這個信息，我們可以知道： 未備份的數(shù)據(jù)位于binlog偏移量為MASTER_LOG_POS至誤刪操作的偏移量。

通過命令mysqlbinlog /data/mysql/mysql-bin.000004 或者 show binlog events in 'mysql-bin.000004'可以看到未備份數(shù)據(jù)的偏移量。如下圖

為了演示“恢復未備份的數(shù)據(jù)”，我在account表中添加幾條數(shù)據(jù)，然后再進行「刪庫->恢復備份的數(shù)據(jù)->恢復未備份的數(shù)據(jù)」的操作。備份狀態(tài)如下圖

再次執(zhí)行恢復命令后，會發(fā)現(xiàn)新添加的這兩條數(shù)據(jù)不存在。

此時，備份的數(shù)據(jù)和binlog的狀態(tài)對應如下圖

然后先執(zhí)行以下命令將未備份的數(shù)據(jù)SQL語句導出來

mysqlbinlog --start-position=2770 --stop-position=3327 /data/mysql/mysql-bin.000004 >/backup/bin.sql 

再登錄到mysql服務執(zhí)行以下命令即可恢復到刪庫前的狀態(tài)。

set sql_log_bin=0;
source /backup/bin.sql
set sql_log_bin=1;

至此，在誤刪操作后，數(shù)據(jù)就恢復成功了。

可能會有人問“binlog也被刪除了呢？怎么恢復？”，這個就涉及到主從復制、高可用模式了。

在這要說明一下，MySQL5.7后默認開啟了GTID（全局事務標識符）特性，用于簡化 MySQL 主從復制和故障恢復，也可以應用到剛才的恢復未備份的數(shù)據(jù)中。跟基于偏移量導出binlog相比，執(zhí)行基于gtid的sql可以保證唯一性、冪等性，功能更豐富。操作與偏移量相似，這里就不演示了，貼一個相關的命令作為參考

-- 導出gtid為1至10，不包括6和9的sql語句，
mysqlbinlog --skip-gtids --include-gtids='xxxxxxx-1xxxx-xxxx-0xxxxxx:1-10' --exclude-gtids='xxxxxxx5-axxxx-1xxx-8xxx-0xxxx:6','48xxxx5-axxx-1xxa-xxxxxx:9' mysql-bin.000004 >/backup/bin.sql 

總結

mysqldump只是進行了數(shù)據(jù)的備份，無法做到完全的恢復，在恢復數(shù)據(jù)時還要借助binlog對未及時備份的數(shù)據(jù)進行恢復。

雖然現(xiàn)在許多公司傾向于使用云端的高可用性集群數(shù)據(jù)庫，忽略了對備份恢復操作的關注，但為了安全起見，仍需掌握數(shù)據(jù)備份與恢復的操作。這樣可以在突發(fā)情況下，可以采取應對措施，減少事故帶來的損失。

以上就是MySQL數(shù)據(jù)被誤刪的解決方法的詳細內容，更多關于MySQL數(shù)據(jù)被誤刪的資料請關注腳本之家其它相關文章！

最新評論