MySQL備份與恢複

標籤：一段   編輯器   新功能   lvm   prim   許可權   cal   lis   檔案儲存體   

一、備份的目的

• 災難恢複。發生災難時，用於恢複損壞的資料
• 審計。資料庫在過去某一個時間點是什麼樣的
• 測試。測試新功能是否可用

二、備份的分類1.物理備份（physical backup）

直接複製資料檔案，打包歸檔，不需要額外工具直接歸檔命令即可，但是跨平台能力比較差；如果資料量超過幾十個G，則適用於物理備份

• 冷備（cold backup）：伺服器離線，讀寫操作都不能進行
• 溫備（warm backup）：伺服器線上，全域施加共用鎖定，只能讀不能寫
• 熱備（hot backup）：資料庫線上，備份的同時，讀寫照樣進行

註：MyISAM不支援熱備，InnoDB支援熱備，但是需要專門的工具

2.邏輯備份（logical backup）

把資料幫浦出來儲存在sql指令碼中，可以使用文字編輯器編輯，並且匯入方便，直接讀取sql語句即可；但邏輯備份恢復慢，佔據空間大，無法保證浮點數的精度，並且恢複完資料庫後需要重建索引

三、備份的方式

• 全量備份（full backup）：備份MySQL中所有的庫和表，佔用空間較大，是增量備份和差異備份的前提條件
• 增量備份（incremental backup）：備份與上次全量、增量、差異備份後不同的資料內容，比較節約空間。備份資料少、備份速度快、恢複速度慢
• 差異備份（differential backup）：備份從目前時間點到上次完整備份之間不同的資料內容，備份資料多、備份速度慢、恢複速度快

四、備份策略1.備份需要考慮的因素

備份方式、備份實踐、備份成本、鎖時間、備份時間長度、效能開銷、恢複成本、恢複時間長度、所能夠容忍丟失的資料量

2.不同環境的解決方案

針對不同的情境下, 我們應該制定不同的備份策略對資料庫進行備份, 一般情況下, 備份策略一般為以下三種

• 直接cp，tar複製資料庫檔案
• mysqldump+複製BIN LOGS
• vm2快照+複製BIN LOGS
• xtrabackup

（1）如果資料量較小，可以使用第一種方式，直接複製資料庫檔案

（2）如果資料量還行，可以使用第二種方式，先使用mysqldump對資料庫進行完全備份，然後定期備份BINARY LOG達到增量備份的效果

（3）如果資料量一般，而又不過分影響業務運行，可以使用第三種方式，使用lvm2的快照對資料檔案進行備份，而後定期備份BINARY LOG達到增量備份的效果

（4）如果資料量很大，而又不過分影響業務運行，可以使用第四種方式，使用xtrabackup進行完全備份後，定期使用xtrabackup進行增量備份或差異備份

3.備份的內容

• 資料庫中的資料
• 資料庫的設定檔
• mysql中的代碼：預存程序，儲存函數，觸發器
• OS相關的設定檔，chrontab中的備份策略指令碼
• 如果是主從複製的情境中，複製相關的資訊
• 二進位記錄檔需要定期備份，一旦發現二進位檔案出現問題，需馬上對資料進行完全備份

4.MySQL常用備份工具

• mysqldump：邏輯備份工具, 適用於所有的儲存引擎, 支援溫備、完全備份、部份備份、對於InnoDB儲存引擎支援熱備
• Xtrabackup（通常用innobackupex工具）：強大的InnoDB/XtraDB熱備工具, 支援完全備份、增量備份, 由percona提供，屬物理備份，速度快
• lvm-snapshot：接近於熱備的工具，因為要先請求全域鎖，而後建立快照，並在建立快照完成後釋放全域鎖；很難實現增量備份，並且請求全域需要等待一段時間，在繁忙的伺服器上尤其如此，幾乎熱備, 需藉助檔案系統管理工具進行備份
• mysqldumper：多線程的mysqldump
  SELECT clause INTO OUTFILE ‘/path/to/somefile‘ LOAD DATA INFILE ‘/path/from/somefile‘
• mysqlhotcopy：接近冷備，基本沒用，僅支援MyISAM儲存引擎
• cp、tar等歸檔複製工具：物理備份工具, 適用於所有的儲存引擎, 冷備、完全備份、部份備份

五、備份案例1.使用tar或cp冷備份並恢複（1）資料類比

mysql -u root -p                                        //登入MySQLmysql>create database hiahia;          //建立名為hiahia的資料庫mysql>create table hiahia.user (user_name char(12),user_phone char(11),primary key (user_phone));//在hiahia庫下建立user表，表中兩個欄位，其中user_phone為主鍵mysql>insert into hiahia.user values (‘Zhangsan‘,‘2691376416‘);mysql>insert into hiahia.user values (‘Lisi‘,‘2691376417‘);mysql>select * from hiahia.user;        //查看錶中的資料

（2）備份與恢複【cp命令】

mysql>flush tables with read lock;          //向所有表施加讀鎖，防止過程中有寫入mkdir /Backup/                                    //建立備份資料庫存放目錄chown -R mysql:mysql /Backup/       //更改所有者及所有組cp -a /usr/local/mysql/data/* /Backup/   //保留許可權拷貝來源資料檔案.rm -rf /usr/local/mysql/data/*????????????????? //刪除資料庫的所有檔案，類比故障mysql -u root -pmysql>select * from hiahia.user;             //驗證資料情況cp -a /Backup/* /usr/local/mysql/data/  //將備份的資料檔案拷貝回去mysql -u root -pmysql>select * from hiahia.user;             //驗證資料情況

（3）備份與恢複【tar命令】

mysql>flush tables with read lock;          //向所有表施加讀鎖，防止過程中有寫入mkdir /Backup/                                    //建立備份資料庫存放目錄chown -R mysql:mysql /Backup/       //更改所有者及所有組cd /usr/local/mysql/data/                          //進入MySQL資料存放區目錄tar Jcvpf /Backup/mysql-$(date +%F).tar.xz ./*//使用xz壓縮目前的目錄下所有內容並將壓縮檔放入/Backup目錄，以日期取名檔案rm -rf /usr/local/mysql/data/*                  //刪除資料庫的所有檔案，類比故障mysql -u root -pmysql>select * from hiahia.user;             //驗證資料情況tar -Jxvpf /Backup/mysql-2018-03-21.tar.xz -C /usr/local/mysql/data///通過tar備份檔案恢複資料到/usr/local/mysql/data/目錄下mysql -u root -pmysql>select * from hiahia.user;             //驗證資料情況

2.使用mysqldump全量備份恢複（1）資料類比

mysql -u root -p                                        //登入MySQLmysql>create database hiahia;          //建立名為hiahia的資料庫mysql>create table hiahia.user (user_name char(12),user_phone char(11),primary key (user_phone));//在hiahia庫下建立user表，表中兩個欄位，其中user_phone為主鍵mysql>insert into hiahia.user values (‘Zhangsan‘,‘2691376416‘);mysql>insert into hiahia.user values (‘Lisi‘,‘2691376417‘);mysql>select * from hiahia.user;        //查看錶中的資料

（2）mysqldump全量備份

mkdir /Backup/                                           //建立備份資料庫存放目錄chown -R mysql:mysql /Backup/              //更改所有者及所有組

（2.1）備份庫

mysqldump -u 使用者名稱 -p 密碼 資料庫名 >/備份路徑/備份檔案名mysqldump -u root -p hiahia >/Backup/hiahia-$(date +%F).sql//備份hiahia庫，並將匯出的sql語句檔案重新導向匯出到/Backup目錄下

（2.2）備份庫下的表

mysqldump -u 使用者名稱 -p 密碼 資料庫名 表名 >/備份路徑/備份檔案名mysqldump -u root -p hiahia user >/Backup/hiahia-user-$(date +%F).sql//備份hiahia庫下的user表，並將匯出的sql語句檔案重新導向匯出到/Backup目錄下

（2.3）備份多個庫

mysqldump -u 使用者名稱 -p 密碼 --databases 庫名1 [庫名2] ... >/備份路徑/備份檔案名mysqldump -u root -p --databases hiahia mysql >/Backup/hiahia-mysql-$(date +%F).sql//備份hiahia、mysql兩個庫，並將匯出的sql語句檔案重新導向匯出到/Backup目錄下

（2.4）備份整個庫

mysqldump -u 使用者名稱 -p 密碼 --all-databases >/備份路徑/備份檔案名mysqldump -u root -p --all-databases >/Backup/all-$(date +%F).sql//將這個MySQL庫備份，並將匯出的sql語句檔案重新導向匯出到/Backup目錄下

（2.5）備份表結構

mysqldump -u 使用者名稱 -p [密碼] -d 資料庫名 表名 >/備份路徑/備份檔案名mysqldump -u root -p -d hiahia user >/Backup/hiahia-desc-user-$(date +%F).sql//只備份hiahia庫下user表的結構（即表的頭部），並將匯出的sql語句檔案重新導向匯出到/Backup目錄下

（3）mysqldump全量恢複（3.1）使用source恢複

• 登入MySQL資料庫

• 執行source 備份sql指令檔路徑

（3.2）使用mysql命令恢複

mysql -u root -p [密碼] </庫備份指令碼路徑

（3.3）恢複表

mysql -u root -p                                        //登入MySQL資料庫，密碼為123mysql>drop table hiahia.user;                 //刪除user表，類比表故障mysql>select * from hiahia.user;             //驗證資料情況mysql>use hiahia;                               //進入要恢複的庫mysql>source /Backup/hiahia-user-2018-03-21.sql//通過備份的表sql檔案恢複資料mysql>select * from hiahia.user;             //驗證資料情況

（3.4）恢複庫

mysql -u root -p                                        //登入MySQL資料庫，密碼為123mysql>drop database hiahia;                   //刪除hiahia庫，類比庫故障mysql>show databases;                      //驗證資料情況

註：使用mysql恢複庫時，如庫已不存在，需自行建立同名庫，再通過備份庫檔案恢複，並且在恢複時需指定恢複到哪個庫

mysql -u root -p                                        //登入MySQL資料庫，密碼為123mysql>create database hiahia;          //建立庫mysql -u root -p hiahia </Backup/hiahia-2018-03-21.sql//通過備份庫sql檔案恢複庫資料到hiahia庫下mysql -u root -p                                        //登入MySQL資料庫，密碼為123mysql>show databases;                      //驗證資料情況

六、MySQL增量備份與恢複

mysqldump完全備份的缺點：備份資料中有重複資料、備份時間與恢復長

1.增量備份

備份自上一次備份之後增加或變化的檔案或者內容

特點

• 重複資料，備份量不大，時間短
• 但恢複麻煩，需要上次完整備份及完整備份之後所有的增量備份才能恢複，而且要對所有增量備份進行逐個反推恢複
• 但MySQL中並未直接提供增量備份方法，但可通過MySQL提供的二進位日誌（Binary logs）間接實現增量備份

2.二進位日誌

• 儲存所有更新資料庫的操作
• MySQL啟動後便開始記錄，當達到max_binlog_size選項值後或收到flush logs命令後自動重新建立新的記錄檔
• 因此只需定時執行flush logs命令，再將新組建記錄檔檔案儲存到安全位元置，即可完成這一時間段增量備份

3.案例：MySQL增量備份（1）資料類比

mysql -u root -p                                        //登入MySQLmysql>create database hiahia;          //建立名為hiahia的資料庫mysql>create table hiahia.user (user_name char(12),user_phone char(11),primary key (user_phone));//在hiahia庫下建立user表，表中兩個欄位，其中user_phone為主鍵mysql>insert into hiahia.user values (‘Zhangsan‘,‘2691376416‘);mysql>insert into hiahia.user values (‘Lisi‘,‘2691376417‘);mysql>select * from hiahia.user;        //查看錶中的資料

（2）開啟MySQL二進位日誌

• 方式一：編輯my.cnf設定檔在[mysqld]項下加入log-bin=filepath，重啟mysqld服務即可（預設即為開啟）

• 方式二：運行"mysqld --log-bin=filepath"，然後重啟mysqld服務

註：filepath為二進位檔案儲存路徑，如不指定路徑只指定名稱，預設儲存在資料目錄下（源碼預設：/usr/local/mysql/data/；rpm/yum：/var/lib/mysql）

（3）完整備份

增量備份前，必須有完整備份才可以mysqldump -u root -p hiahia user >/Backup/hiahia-user-$(date +%F).sql//備份hiahia庫下的user表，並將匯出的sql語句檔案重新導向匯出到/Backup目錄下

（4）增量備份

mkdir /Backup/                                           //建立備份資料庫存放目錄chown -R mysql:mysql /Backup/              //更改所有者及所有組ls -l /usr/local/mysql/data///增量備份前，查詢原二進位記錄檔名，以便區分新舊二進位記錄檔mysql -u root -p                                               //登入MySQL，密碼為123mysql>flush logs;                                        //產生新的二進位日誌，下面操作全部記錄到新記錄檔中，方便增量備份mysql>insert into hiahia.user values (‘hehe‘,‘13452231231‘);//類比資料更改mysql> insert into hiahia.user values (‘haha‘,‘13456223123‘);mysql>insert into hiahia.user values (‘huohuo‘,‘12345621231‘);mysql>insert into hiahia.user values (‘heihei‘,‘136741234312‘);mysqladmin -u root -p flush-logs//通過命令，再次產生新的記錄檔，以便將新增資料記錄日誌截取為獨立的記錄檔ls -l /usr/local/mysql/data///查詢新增二進位記錄檔，新增記錄檔裡即記錄新增量資料情況mysqlbinlog /usr/local/mysql/data/mysql-bin.000005//使用mysqlbinlog命令查看新記錄二進位日誌內容

註：如mysqlbinlog查看時出現"unknown variable ...."，可以使用mysqlbinlog --no-defaults 二進位記錄檔，該方式查看

cp /usr/local/mysql/data/mysql-bin.000005 /Backup/

4.案例：MySQL增量恢複

增量恢複情境

• 人為SQL語句破壞資料庫
• 在下次全量備份前發生系統故障導致資料庫資料丟失
• 主從架構中，主庫資料發生故障

資料丟失分類

• 只丟失完全備份之後的資料
• 包括完整備份在內的所有資料

（1）只丟失完全備份之後的資料（恢複過程）

mysqladmin -u root -p flush-logs//產生新的二進位日誌，避免恢複時將所有的操作都記錄，導致資料出錯（建議備份前和恢複前都輸入一次）mysqlbinlog 二進位記錄檔 | mysql -u root -p//將二進位增量檔案逐個推倒恢複資料庫，直到恢複到完整備份時刻

（2）包括完整備份在內的所有資料（恢複過程）

mysqladmin -u root -p flush-logs//產生新的二進位日誌，避免恢複時將所有的操作都記錄，導致資料出錯（建議備份前和恢複前都輸入一次）mysql -u root -p 資料庫名 </庫備份指令碼路徑.//恢複完整備份資料mysqlbinlog 二進位記錄檔 | mysql -u root -p

（3）基於時間點與位置的恢複

預設增量恢複是恢複整個二進位日誌中內容，當然也可以利用二進位日誌實現基於某個時間點及位置的恢複，達到精確恢複

（3.1）基於時間點恢複

指定匯入停止時間點，可只恢複到該時間點前面資料，從而跳過某個發生錯誤的時間點實現資料恢複；也可指定匯入開始時間點，從該時間點到記錄檔結尾全部匯入mysqlbinlog --stop-datetime=‘日期 時間‘ 二進位記錄檔 | mysql -u root -pmysqlbinlog --start-datetime=‘日期 時間‘ 二進位記錄檔 | mysql -u root -p

（3.2）基於位置恢複

如採用基於時間點恢複，可能出現某個時間點既同時存在正確操作又存在錯誤操作的情況，因此基於位置的恢複可以更好的控制每個操作都會記錄一個end_log_pos值，使用基於位置恢複可以基於某條語句恢複mysqlbinlog --stop-position=‘end_log_pos‘ 二進位記錄檔 | mysql -u root -pmysqlbinlog --start-position=‘end_log_pos‘ 二進位記錄檔 | mysql -u root -p

5.案例：MySQL增量恢複（丟失完全備份後）（1）丟失完全備份之後的資料

mysql -u root -p                                               //登入MySQL，密碼為123mysql>delete from hiahia.user where user_name=‘hehe‘;//類比增量資料丟失mysql>delete from hiahia.user where user_name=‘haha‘;mysql> delete from hiahia.user where user_name=‘huohuo‘;mysql>delete from hiahia.user where user_name=‘heihei‘;mysql>select * from hiahia.user;                           //驗證資料情況mysqladmin -u root -p flush-logs                  //產生新的二進位日誌mysqlbinlog /Backup/mysql-bin.000005 | mysql -u root -p//通過備份的增量二進位記錄檔恢複增量資料，如有多個增量記錄檔逐個恢複mysql -u root -p                                               //登入MySQL，密碼為123mysql>select * from hiahia.user;                    //驗證資料情況

（2）丟失完整備份在內資料

mysql -u root -p                                               //登入MySQL，密碼為123mysql>drop table hiahia.user;                        //刪除user表，類比表故障mysql>select * from hiahia.user;                    //驗證資料情況mysql>mysql -u root -p hiahia </Backup/hiahia-user-2018-03-21.sql//通過備份表sql檔案恢複表資料到hiahia庫下mysql -u root -p                                               //登入MySQL，密碼為123mysql>select * from hiahia.user;                    //驗證資料情況，完整備份恢複mysqladmin -u root -p flush-logs                  //產生新的二進位日誌mysqlbinlog /Backup/mysql-bin.000005 | mysql -u root -p//通過備份的增量二進位記錄檔恢複增量資料，如有多個增量記錄檔逐個恢複mysql -u root -p                                               //登入MySQL，密碼為123mysql>select * from hiahia.user;                    //驗證資料情況

（3）基於時間點恢複

mysql -u root -p                                               //登入MySQL，密碼為123mysql>delete from hiahia.user where user_name=‘hehe‘;//類比增量資料丟失mysql>delete from hiahia.user where user_name=‘haha‘;mysql> delete from hiahia.user where user_name=‘huohuo‘;mysql>delete from hiahia.user where user_name=‘heihei‘;mysql>select * from hiahia.user;                    //驗證資料情況mysqladmin -u root -p flush-logs                  //產生新的二進位日誌mysqlbinlog /Backup/mysql-bin.000005//通過查看二進位日誌確認恢復點，如該處為"180321 19:58:40"mysqlbinlog /Backup/mysql-bin.000005 --stop-datetime=‘2018-03-21 19:58:40‘ | mysql -u root -p//只恢複‘2018-03-21 19:58:40‘之前資料mysql -u root -p                                               //登入MySQL，密碼為123mysql>select * from hiahia.user;                    //驗證資料情況mysqlbinlog /Backup/mysql-bin.000005//通過查看二進位日誌確認恢復點，如該處為從"180321 19:58:45"mysqlbinlog /Backup/mysql-bin.000005 --start-datetime=‘2018-03-21 19:58:45‘ | mysql -u root -p//只恢複‘2018-03-21 19:58:45‘之後資料mysql -u root -p                                               //登入MySQL，密碼為123mysql>select * from hiahia.user;                    //驗證資料情況

註："stop-datetime或start-datetime"不要相同時間點匯入多次，會重複性匯入

（4）基於位置恢複

mysql -u root -p                                               //登入MySQL，密碼為123mysql>delete from hiahia.user where user_name=‘hehe‘;//類比增量資料丟失mysql>delete from hiahia.user where user_name=‘haha‘;mysql> delete from hiahia.user where user_name=‘huohuo‘;mysql>delete from hiahia.user where user_name=‘heihei‘;mysql>select * from hiahia.user;                           //驗證資料情況mysqladmin -u root -p flush-logs                         //產生新的二進位日誌mysqlbinlog /Backup/mysql-bin.000005//通過查看二進位日誌確認恢複pos，如該處為從"577"mysqlbinlog /Backup/mysql-bin.000005 --stop-position=‘577‘ | mysql -u root -p//只恢複‘577‘之前資料mysql -u root -p                                           //登入MySQL，密碼為123mysql>select * from hiahia.user;                           //驗證資料情況mysqlbinlog /Backup/mysql-bin.000005//通過查看二進位日誌確認恢復點，如該處為從"718"mysqlbinlog /Backup/mysql-bin.000005 --start-position=‘718‘ | mysql -u root -p//只恢複‘718‘之後資料mysql -u root -p                                               //登入MySQL，密碼為123mysql>select * from hiahia.user;                    //驗證資料情況

MySQL備份與恢複