備份校驗兩不誤，MySQL自動備份還原校驗設計詳解

作者介紹
龐闊，優朋普樂傳媒運維基礎部經理。負責數據庫運營管理及平臺設計開發，監控設計改進，問題跟蹤處理，機房網絡維護管理，目前四個專利已在專利局申請中。擅長數據庫運維管理及Shell、Perl、PHP編寫。

最近關于數據庫故障出現的問題較多，不論大小公司對數據的備份要求都很高，但對校驗數據備份的有效性要求更為迫切，很多公司對于自動備份和還原都已經形成體系，但對于還原后的備份有效性校驗可能都不太完善，而且目前網上也沒有較為完善的檢驗機制（可能我沒找到）。

對數據庫備份的有效性校驗的方法或樣例選擇，直接關系到備份數據的質量指標。本文將分享我做的一個設計，此設計是直接采用線上執行的SQL提取出select，包括復雜join類型的SQL加上當前存在的庫及表信息，提高了備份校驗的準確性。

這是我在申請數據庫相關專利時推演出來的方案，在尋找一個好的校驗備份還原后的數據衡量指標，偶然地和備份還原進行結合時出現了這個設計。當數據庫實例越來越多時，這個有效性校驗的需求會越來越強。

下面將簡單介紹一下我的校驗數據的設計方案，或許它能給你一個思路或想法，當然我也希望能有其他好的方案出來，共同學習。（注：部分信息做了脫敏處理）

系統處理流程

程序處理流程如下：

根據上面的流程圖，大致分為5個步驟，有6個腳本程序來完成這個流程，每個步驟其實不是很難，實際中可根據自己的業務特定進行完善，下面我簡單介紹此流程中主要的幾個功能。

功能介紹

1. 自動備份功能
   （可自行設置，我是配置的定時任務，平臺在對接中）

2. 自動還原功能
   自動下載備份并還原。

3. SQL及庫表自動上報功能
   1）上報本機數據庫的庫表信息，主要用來比對還原后庫表信息是否一一對應，如果對應正常，否則異常，進行報警處理。
   2）匯報SQL，為保證SQL的真實性，此方法是監聽general_log，分析后獲取Select 類型SQL，并執行此SQL 降獲取到的sql 及查到的值 匯報到數據中心作為樣例SQL使用。

4. 還原后庫表及SQL自動比對功能
   1）還原后自動調用數據庫中心獲取庫表信息，進行一一比對。
   2）獲取SQL信息進行原來和還原后數據值的匹配校驗，如果對應則正常，否則為異常。

注：在下面演示過程中以手動形式，可根據公司具體情況設置為自動。

環境介紹

數據庫機器：172.16.20.5
備份機器：172.16.20.6
還原機器：172.16.20.7
備份工具：mydumper
編程語言：Shell+Perl
備份傳輸工具：rsync

部署

1、備份機器rsync部署

對于數據中心做備份之前采取過如下幾個方案。我簡單概括一下：

NFS：由一塊設備進行網絡遠程掛載，只需安裝NFS服務即可，操作簡單。但是有個問題就是當NFS服務出現問題或網絡中斷時你去使用磁盤會出現掛起的現象。
FTP：也用過FTP來做備份服務，但有時會出現登錄失敗的現象，對于不同目錄權限設置較為復雜，不方便維護；上傳下載編寫腳本也不是太方便。
Rsync：改為Rsync，主要是配置簡單，上傳下載也簡單的多，一條命令即可；對于增量的傳輸很有用。

重要部分如下：
[back5]
path = /opt/mysql_bak/172.16.20.5
comment = www file
ignore errors
read only = false
list = false
uid = root
gid = root

2、數據庫機器和還原機器安裝mydumper

mydumper第三方開用于對MySQL數據庫進行多線程備份和恢復的開源工具。開發人員主要來自MySQL、Facebook和SkySQL公司，目前由Percona公司開發和維護，是Percona Remote DBA項目的重要組成部分；不同于官方的mysqldump、mysqlpump的是對庫表備份和還原采用多線程，對于快速備份和恢復是不錯的選擇；當然還有percona的xtrabackup相當于物理備份的工具，但是耗費空間較大。

安裝請參考官網：https://launchpad.net/mydumper/+download

3、數據庫上執行備份腳本

腳本如下：

4、數據中心表結構設計

在數據中心創建下面的表，這些表主要用來存儲備份時上報的庫表信息和SQL信息，用后續步驟還原校驗時做提供樣例值。

庫表匯報的表結構

SQL 表結構

5、數據庫機器上匯報

1）庫表匯報程序地址：自行下載和修改
https://github.com/kevin6386/db_table_report/blob/master/db_table_report
運行即可。

2）SQL匯報程序
程序地址：https://github.com/kevin6386/db_sql_report/blob/master/db_sql_report
運行即可。

6、數據庫備份還原

下載備份并還原(簡單分解介紹)：

用 rsync 下載備份到本地，并解壓
rsync -zrtoapg –progress root@172.16.20.6::back5/備份文件名 ./

恢復命令：
/usr/local/bin/myloader -u user -p pass -o -d 備份地址 -t 8

7、校驗

此時才是整個流程設計的重點，針對還原后的數據，怎么做校驗才是重要的，而且校驗的樣例或方法直接關系數據備份有效性的指標。

1）還原后數據庫表的校驗

程序地址：https://github.com/kevin6386/db_table_diff/blob/master/db_table_diff

比較結果如下：

郵件截圖

2）還原后數據SQL的校驗

程序地址：https://github.com/kevin6386/db_sql_diff

比較結果如下：

郵件截圖：如果正常則附件會有SQL，否則為空。

異常截圖

出現異常有如下幾種情況：

1. 備份時和general_log提取有時間的差異；當獲取SQL出現在備份前或備份后有數據修改的情況下會出現。（可采用低峰時或很少修改的字段進行提取樣例）

2. 某些表還原異常，數據丟失。（比如我遇到過觸發器的情況，表與表有依賴）

3. 我用從庫的備份比對主庫的SQL。（有可能從庫和主庫不一致）

4. 備份時有丟失的表或記錄。（有時備份的命令問題或漏備份）

附件SQL信息

8、關于備份的匯報

我是匯報每天的備份大小及文件名，然后寫SQL比對今天的備份和前2天的信息。

如下：

總結

設計完這個方案后開始編寫分程序花了一段時間，同時感謝我的同事幫我重復測試這個設計方案，發現之前備份還原過程中出現的問題改善了很多，重要的是不用人工去抽取還原后的數據結果。當這個方案固定后基本上很少有人工的參與，減少了人工還原備份和校驗備份重復的工作；并且可以準確地知道哪部分有問題，減少了對數據庫備份是否正常的擔憂。當然還有很多要完善的方面，歡迎有興趣的朋友在留言區提出建議，一起交流。