前言

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

drop database test;

可以看到test庫已經(jīng)被刪除。

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

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

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

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

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

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

在實(shí)際應(yīng)用中，恢復(fù)數(shù)據(jù)不是這么簡單的，因?yàn)閭浞莶僮骰旧喜粫?huì)是實(shí)時(shí)的，如果昨天備份數(shù)據(jù)，今天誤刪了數(shù)據(jù)，那么在這之間的數(shù)據(jù)如何恢復(fù)？

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- 導(dǎo)出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 

總結(jié)

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

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

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

最新評(píng)論