288
備份校驗兩不誤，MySQL自動備份還原校驗設計詳解
作者介紹
龐闊，優朋普樂傳媒運維基礎部經理。負責數據庫運營管理及平台設計開發，監控設計改進，問題跟蹤處理，機房網絡維護管理，目前四個專利已在專利局申請中。擅長數據庫運維管理及Shell、Perl、PHP編寫。
背景
最近關於數據庫故障出現的問題較多，不論大小公司對數據的備份要求都很高，但對校驗數據備份的有效性要求更為迫切，很多公司對於自動備份和還原都已經形成體係，但對於還原後的備份有效性校驗可能都不太完善，而且目前網上也沒有較為完善的檢驗機製（可能我沒找到）。
對數據庫備份的有效性校驗的方法或樣例選擇，直接關係到備份數據的質量指標。本文將分享我做的一個設計，此設計是直接采用線上執行的SQL提取出select，包括複雜join類型的SQL加上當前存在的庫及表信息，提高了備份校驗的準確性。
這是我在申請數據庫相關專利時推演出來的方案，在尋找一個好的校驗備份還原後的數據衡量指標，偶然地和備份還原進行結合時出現了這個設計。當數據庫實例越來越多時，這個有效性校驗的需求會越來越強。
下麵將簡單介紹一下我的校驗數據的設計方案，或許它能給你一個思路或想法，當然我也希望能有其他好的方案出來，共同學習。（注：部分信息做了脫敏處理）
係統處理流程
程序處理流程如下：
根據上麵的流程圖，大致分為5個步驟，有6個腳本程序來完成這個流程，每個步驟其實不是很難，實際中可根據自己的業務特定進行完善，下麵我簡單介紹此流程中主要的幾個功能。
功能介紹
-
自動備份功能
（可自行設置，我是配置的定時任務，平台在對接中）
-
自動還原功能
自動下載備份並還原。
-
SQL及庫表自動上報功能
1）上報本機數據庫的庫表信息，主要用來比對還原後庫表信息是否一一對應，如果對應正常，否則異常，進行報警處理。
2）匯報SQL，為保證SQL的真實性，此方法是監聽general_log，分析後獲取Select 類型SQL，並執行此SQL 降獲取到的sql 及查到的值 匯報到數據中心作為樣例SQL使用。
-
還原後庫表及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相當於物理備份的工具，但是耗費空間較大。
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，否則為空。
異常截圖
出現異常有如下幾種情況：
-
備份時和general_log提取有時間的差異；當獲取SQL出現在備份前或備份後有數據修改的情況下會出現。（可采用低峰時或很少修改的字段進行提取樣例）
-
某些表還原異常，數據丟失。（比如我遇到過觸發器的情況，表與表有依賴）
-
我用從庫的備份比對主庫的SQL。（有可能從庫和主庫不一致）
-
備份時有丟失的表或記錄。（有時備份的命令問題或漏備份）
附件SQL信息
8、關於備份的匯報
我是匯報每天的備份大小及文件名，然後寫SQL比對今天的備份和前2天的信息。
如下：
總結
設計完這個方案後開始編寫分程序花了一段時間，同時感謝我的同事幫我重複測試這個設計方案，發現之前備份還原過程中出現的問題改善了很多，重要的是不用人工去抽取還原後的數據結果。當這個方案固定後基本上很少有人工的參與，減少了人工還原備份和校驗備份重複的工作；並且可以準確地知道哪部分有問題，減少了對數據庫備份是否正常的擔憂。當然還有很多要完善的方麵，歡迎有興趣的朋友在留言區提出建議，一起交流。
原文發布時間為：2017-03-28
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-16 11:31:52