一、mysql備份、恢復基礎
備份:存儲的數據副本
還原：把副本應用到線上系統，僅能恢復至備份操作時刻的數據狀態。
硬件上的冗余并不能有效恢復邏輯操作上的錯誤。
備份：數據備份、服務配置文件備份、系統環境備份。程序的運行依賴于一定的系統環境，僅提供數據本身并不能確?；謴蛿祿笙到y可用，數據備份的目的在于確保系統出現不可預料的災難事故之后能夠快速恢復運行，降低損失。
演練：良好的制度，災難恢復演練
測試備份的可用性
增強恢復操作效果
時間點恢復：mysql需要借助于binary log
2、為什么備份
災難恢復：硬件故障、軟件故障、自然災害、黑客攻擊、誤操作
3、備份時應該注意事項
能容忍最多丟失多少數據
恢復數據需要在多上時間內完成
需要恢復哪些數據
備份時考慮因素：
鎖定 資源多長時間
備份過程時長
備份時的服務器負載
恢復過程時長
4、備份類型
1）根據備份的數據集范圍劃分：
完全備份：整個數據庫
部分備份：數據集的一部分，比如數據表
2）根據備份方式劃分：
完全備份:
增量備份:第一次的備份數據是從上一個完全備份的時間點至當前時間點之間的變化數據，第二次的備份數據是從上一個備份時間點至當前備份時間點之間變化的數據。其數據不會產生冗余，但恢復起來復雜度較大。
差異備份:從上一次完全備份至下一次完全備份之間的數據，每次的備份數據是上一次完全備份到當前備份時間點的數據的變化?？赡軙a生數據冗余。
在上一個備份時間點與下一個備份時間點之間，若服務器發生故障，此時，使用備份文件僅能恢復至上一個備份時間點備份的數據。在上一個備份時間點至故障時間點之間的數據恢復依賴于二進制日志來實現恢復。
3）根據數據服務是否上線
熱備：讀寫操作均可進行的狀態下所做的備份
溫備：可讀但不可寫狀態下進行的備份
冷備：讀寫操作均不可進行的狀態下所做的備份
4）物理備份、邏輯備份
物理備份：復制數據文件
邏輯備份：從數據庫中導出數據另存在一個或多個文件中
5、備份策略
完全+增量+二進制日志
完全+差異+二進制日志
備份手段：物理+邏輯
6、備份的內容
1）數據
2）二進制日志、innodb的事務日志
3）代碼（存儲過程、存儲函數、觸發器、時間調度器）
4）服務器的配置文件（定時任務、服務配置文件等）
5）根復制相關的配置信息
7、備份工具
1）mysqldump：mysql服務自動的備份工具，邏輯備份工具。適用于數據集較小的場景，10G以下。
完全備份、部分備份
innodb引擎：支持熱備
myisam引擎：支持溫備
aira引擎：支持溫備
缺點：備份和恢復過程較慢
2）lvm-snapshot結合cp/tar
接近于熱備的工具，因為要先請求全局鎖，而后創建快照，濱海個在創建快照完成后釋放全局鎖。之后，使用cp、tar等工具進行物理備份。
優點：備份、恢復速度較快
缺點：很難實現增量備份，并且請求全局鎖需要等待一段時間，在繁忙的服務器上由其如此。
3）xtrabackup：由percona提供，開源工具，支持對innodb做熱備，物理備份工具，速度快?？煽?，備份后會自動檢查備份是否可用；還原速度快。
完全備份、部分備份
完全備份、增量備份
完全備份、差異備份
對innodb：支持熱備、增量備份
對myisam：支持溫備、不支持增量
二、mysql備份工具之mysqldump
mysqldump+二進制文件進行備份
客戶端工具。需要連接至mysql服務器，可用于備份庫，或庫下面的某些表
注：導出的是單個庫，在恢復時默認不會自動創建庫，在恢復還原庫時，庫必須得事先存在。
導出多個庫在恢復是，不需創建庫。
用法：mysqldump [options ] [db_name [tbl_name]]
常用選項：
–all-databases：備份所有庫
–databases db1 db2…..：備份指定的多個庫，即便導出單個庫，也不需要事先創建
–lock-tables：在備份開始前，請求鎖定目標表，之后在備份，以實現數據一致
–lock-all-tables：請求鎖定表之后在備份，能實現對innodb、myisam、aria做溫備，當存儲引擎不是innodb時建議使用
–single-transaction：對innodb引擎實現熱備。與–lock-all-tables不同時使用。
備份代碼常用的選項：
–events：備份時間調度器代碼
–routines：備份存儲過程和存儲函數
–triggers：備份觸發器
備份時滾動二進制日志：
–flush-logs：備份前，請求到鎖之后，滾動日志
復制時的同步位置標記
–master-data=[0|1|2]
0：表示不吉利
1：表示記錄為Change Master語句
2：表示記錄為注釋的Change Master語句。
注：在備份前要對備份的目標庫或表請求鎖
2、使用mysqldump備份步驟
1）請求鎖或使用熱備：–lock-all-tables或–single-transaction
2）滾動日志
3）請求數據庫
4）指定二進制日志文件及其位置：–master-data
~]# mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --master-data=2 --flush-logs > /tmp/hellodb.new.bak.sql[object Object]
意為當前備份時間點之后的數據變化會記錄在二進制日志master.000006文件中的第106位置之后。要恢復數據至進行備份之后的某事件點時，重放106位置之后的日志即可。
2.1、手動請求全局鎖并備份數據的的方式
1）mysql命令：flush tables with read lock；把表中的數據寫入磁盤并為表加上讀鎖。若數據過大，該操作可能會被阻塞一段時間；
2）fulsh logs；
3）之后使用mysqldump進行備份
4）之后unlock tables；解鎖。
2.2、直接使用mysqldump進行備份
~]# mysqldump -uroot -hlocalhost -p --databases hellodb --lock-all-tables --master-data=2 --flush-logs > /tmp/hellodb.new.bak.sql
或
~]# mysqldump -uroot -hlocalhost -p --databases hellodb --single-transaction --master-data=2 --flush-logs > /tmp/hellodb.new.bak.sql
3、恢復方式
恢復過程沒必要記錄在二進制文件中。
因此，執行恢復操作時需要臨時關閉二進制日志。
3.1建議關閉二進制日志記錄，關閉其他用戶對服務器的寫操作（關閉其他用戶連接）
mysql> set session sql_log_bin=0; mysql> show global variables like "%log_bin"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin| ON | | sql_log_bin| OFF | +---------------+-------+
3.2 導出上一次備份時間點到故障時間點之間的二進制日志至某文件中
注：引起故障的語句要刪除
]# mysqlbinlog --start-position=106 --stop-position=265 master.000006 >/tmp/hellodb.bin.sql
3.3 導入數據備份
mysql> source /tmp/hellodb.bak;
3.4 導入二進制日志
mysql> source /tmp/hellodb.bin.sql;
3.5 檢查數據恢復成功與否
3.6 啟用二進制日志記錄
mysql> set session sql_log_bin=1;
備份策略：
每周一做一次完全備份：備份的同時滾動日志。
周一至周六備份二進制日志。
三、mysql備份工具之lvm-snapshot
基于lvm快照的備份
要求：
1）、事務日志跟數據文件必須在同一個卷上
2）、創建快照卷之前，要請求mysql的全局鎖；在快照創建完成之后，釋放鎖。
3）、請求全局鎖，完成之后，做一次日志滾動；日志滾動之后，記錄事件位置。
備份步驟：
1）、請求全局鎖，并滾動日志
mysql> flush tables with read lock; mysql> flush logs;
2）、做二進制日志文件及位置標記（手動進行）
#mysql -e "show master status" >/path/to/file
3）、創建快照卷
#lvcreate -L -s -n -p r /path/to/some_lv
4）、釋放全局鎖
mysql> unlock tables;
5）、掛載快照卷并備份
cp
6）、備份完成之后，刪除快照卷
恢復步驟：
1）、提取備份之后的二進制文件至某sql腳本中
2）、還原數據、修改權限及屬主屬組等，并啟動mysql
3）、做即時點還原
四、mysql備份工具之xtrabackup
備份的實現
1、完全備份
# innobackupex –user=DBUSER
–password=DBUSERPASS /path/to/BACKUP-DIR/
如果要使用一個最小權限的用戶進行備份，則可基于如下命令創建此類用戶：
mysql> CREATE USER ’bkpuser’@’localhost’IDENTIFIED BY ’s3cret’; mysql> REVOKE ALL PRIVILEGES, GRANT OPTION FROM ’bkpuser’; mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO ’bkpuser’@’localhost’; mysql> FLUSH PRIVILEGES;
使用innobakupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。這些文件會被保存至一個以時間命令的目錄中。
在備份的同時，innobackupex還會在備份目錄中創建如下文件：
(1)xtrabackup_checkpoints —— 備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(日志序列號)范圍信息；
每個InnoDB頁(通常為16k大小)都會包含一個日志序列號，即LSN。LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。
(2)xtrabackup_binlog_info —— mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。
(3)xtrabackup_binlog_pos_innodb —— 二進制日志文件及用于InnoDB或XtraDB表的二進制日志文件的當前position。
(4)xtrabackup_binary —— 備份中用到的xtrabackup的可執行文件；
(5)backup-my.cnf —— 備份命令用到的配置選項信息；
在使用innobackupex進行備份時，還可以使用–no-timestamp選項來阻止命令自動創建一個以時間命名的目錄；如此一來，innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據。
2、準備(prepare)一個完全備份
一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態?！皽蕚洹钡闹饕饔谜峭ㄟ^回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。
innobakupex命令的–apply-log選項可用于實現上述功能。如下面的命令：
# innobackupex –apply-log /path/to/BACKUP-DIR
如果執行正確，其最后輸出的幾行信息通常如下：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1 1204079:01:36 InnoDB: Starting shutdown... 1204079:01:40 InnoDB: Shutdown completed; log sequence number 92036620 120407 09:01:40innobackupex: completed OK!
在實現“準備”的過程中，innobackupex通常還可以使用–use-memory選項來指定其可以使用的內存的大小，默認通常為100M。如果有足夠的內存可用，可以多劃分一些內存給prepare的過程，以提高其完成速度。
3、從一個完全備份中恢復數據
注意：恢復不用啟動MySQL
innobackupex命令的–copy-back選項用于執行恢復操作，其通過復制所有數據相關的文件至mysql服務器DATADIR目錄中來執行恢復過程。innobackupex通過backup-my.cnf來獲取DATADIR目錄的相關信息。
# innobackupex –copy-back /path/to/BACKUP-DIR
如果執行正確，其輸出信息的最后幾行通常如下：
innobackupex: Starting to copy InnoDB log files
innobackupex: in '/backup/2012-04-07_08-17-03'
innobackupex: back to original InnoDB log
directory '/mydata/data'
innobackupex: Finished copying back files.
120407 09:36:10innobackupex: completed OK!
請確保如上信息的最行一行出現“innobackupex: completed OK!”。
當數據恢復至DATADIR目錄以后，還需要確保所有數據文件的屬主和屬組均為正確的用戶，如mysql，否則，在啟動mysqld之前還需要事先修改數據文件的屬主和屬組。如：
# chown -Rmysql:mysql /mydata/data/
4、使用innobackupex進行增量備份
每個InnoDB的頁面都會包含一個LSN信息，每當相關的數據發生改變，相關的頁面的LSN就會自動增長。這正是InnoDB表可以進行增量備份的基礎，即innobackupex通過備份上次完全備份之后發生改變的頁面來實現。
要實現第一次增量備份，可以使用下面的命令進行：
# innobackupex --incremental /backup --incremental-basedir=BASEDIR
其中，BASEDIR指的是完全備份所在的目錄，此命令執行結束后，innobackupex命令會在/backup目錄中創建一個新的以時間命名的目錄以存放所有的增量備份數據。另外，在執行過增量備份之后再一次進行增量備份時，其–incremental-basedir應該指向上一次的增量備份所在的目錄。
需要注意的是，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份。
“準備”(prepare)增量備份與整理完全備份有著一些不同，尤其要注意的是：
(1)需要在每個備份(包括完全和各個增量備份)上，將已經提交的事務進行“重放”?！爸胤拧敝螅械膫浞輸祿⒑喜⒌酵耆珎浞萆?。
(2)基于所有的備份將未提交的事務進行“回滾”。
于是，操作就變成了：
# innobackupex –apply-log –redo-only BASE-DIR
接著執行：
# innobackupex –apply-log –redo-only BASE-DIR
–incremental-dir=INCREMENTAL-DIR-1
而后是第二個增量：
# innobackupex –apply-log –redo-only BASE-DIR
–incremental-dir=INCREMENTAL-DIR-2
其中BASE-DIR指的是完全備份所在的目錄，而INCREMENTAL-DIR-1指的是第一次增量備份的目錄，INCREMENTAL-DIR-2指的是第二次增量備份的目錄，其它依次類推，即如果有多次增量備份，每一次都要執行如上操作；
最后一次增量備份不需要加上–redo-only選項
5、Xtrabackup的“流”及“備份壓縮”功能
Xtrabackup對備份的數據文件支持“流”功能，即可以將備份的數據通過STDOUT傳輸給tar程序進行歸檔，而不是默認的直接保存至某備份目錄中。要使用此功能，僅需要使用–stream選項即可。如：
# innobackupex –stream=tar /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz
甚至也可以使用類似如下命令將數據備份至其它服務器：
# innobackupex –stream=tar /backup | ssh user@www.magedu.com "cat -> /backups/`date +%F_%H-%M-%S`.tar"
此外，在執行本地備份時，還可以使用–parallel選項對多個文件進行并行復制。此選項用于指定在復制時啟動的線程數目。當然，在實際進行備份時要利用此功能的便利性，也需要啟用innodb_file_per_table選項或共享的表空間通過innodb_data_file_path選項存儲在多個ibdata文件中。對某一數據庫的多個文件的復制無法利用到此功能。其簡單使用方法如下：
# innobackupex –parallel /path/to/backup
同時，innobackupex備份的數據文件也可以存儲至遠程主機，這可以使用–remote-host選項來實現：
# innobackupex –remote-host=root@www.magedu.com
/path/IN/REMOTE/HOST/to/backup
6、導入或導出單張表
默認情況下，InnoDB表不能通過直接復制表文件的方式在mysql服務器之間進行移植，即便使用了innodb_file_per_table選項。而使用Xtrabackup工具可以實現此種功能，不過，此時需要“導出”表的mysql服務器啟用了innodb_file_per_table選項（嚴格來說，是要“導出”的表在其創建之前，mysql服務器就啟用了innodb_file_per_table選項），并且“導入”表的服務器同時啟用了innodb_file_per_table和innodb_expand_import選項。
(1)“導出”表
導出表是在備份的prepare階段進行的，因此，一旦完全備份完成，就可以在prepare過程中通過–export選項將某表導出了：
# innobackupex –apply-log –export
/path/to/backup
此命令會為每個innodb表的表空間創建一個以.exp結尾的文件，這些以.exp結尾的文件則可以用于導入至其它服務器。
(2)“導入”表
要在mysql服務器上導入來自于其它服務器的某innodb表，需要先在當前服務器上創建一個跟原表表結構一致的表，而后才能實現將表導入：
mysql> CREATE TABLE mytable (…) ENGINE=InnoDB;
然后將此表的表空間刪除：
mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
接下來，將來自于“導出”表的服務器的mytable表的mytable.ibd和mytable.exp文件復制到當前服務器的數據目錄，然后使用如下命令將其“導入”：
mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
7、使用Xtrabackup對數據庫進行部分備份
Xtrabackup也可以實現部分備份，即只備份某個或某些指定的數據庫或某數據庫中的某個或某些表。但要使用此功能，必須啟用innodb_file_per_table選項，即每張表保存為一個獨立的文件。同時，其也不支持–stream選項，即不支持將數據通過管道傳輸給其它程序進行處理。
此外，還原部分備份跟還原全部數據的備份也有所不同，即你不能通過簡單地將prepared的部分備份使用–copy-back選項直接復制回數據目錄，而是要通過導入表的方向來實現還原。當然，有些情況下，部分備份也可以直接通過–copy-back進行還原，但這種方式還原而來的數據多數會產生數據不一致的問題，因此，無論如何不推薦使用這種方式。
(1)創建部分備份
創建部分備份的方式有三種：正則表達式(–include), 枚舉表文件(–tables-file)和列出要備份的數據庫(–databases)。
(a)使用–include
使用–include時，要求為其指定要備份的表的完整名稱，即形如databasename.tablename，如：
# innobackupex –include='^mageedu[.]tb1' /path/to/backup
(b)使用–tables-file
此選項的參數需要是一個文件名，此文件中每行包含一個要備份的表的完整名稱；如：
# echo -e 'mageedu.tb1\nmageedu.tb2' >
/tmp/tables.txt
# innobackupex –tables-file=/tmp/tables.txt /path/to/backup
(c)使用–databases
此選項接受的參數為數據名，如果要指定多個數據庫，彼此間需要以空格隔開；同時，在指定某數據庫時，也可以只指定其中的某張表。此外，此選項也可以接受一個文件為參數，文件中每一行為一個要備份的對象。如：
# innobackupex –databases="mageedu
testdb" /path/to/backup
(2)整理(preparing)部分備份
prepare部分備份的過程類似于導出表的過程，要使用–export選項進行：
# innobackupex –apply-log –export /pat/to/partial/backup
此命令執行過程中，innobackupex會調用xtrabackup命令從數據字典中移除缺失的表，因此，會顯示出許多關于“表不存在”類的警告信息。同時，也會顯示出為備份文件中存在的表創建.exp文件的相關信息。
(3)還原部分備份
還原部分備份的過程跟導入表的過程相同。當然，也可以通過直接復制prepared狀態的備份直接至數據目錄中實現還原，不要此時要求數據目錄處于一致狀態。
五、數據備份注意要點
1、將數據和備份放在不同的磁盤設備上：異機或異地備份較為理想
2、備份的數據應該周期性地進行還原測試
3、每次災難恢復后都應該立即做一次完全備份
4、針對不同規?；蚣墑e的數據量，要定制好備份策略。
5、二進制日志應該跟數據文件在不同的磁盤上，并周期性地備份好二進制日志文件。
六、從備份中恢復應該遵循的步驟
1、停止MySQL服務器
2、記錄服務器的配置和文件權限
3、將數據從備份移到MySQL數據目錄，其執行方式依賴于工具
4、改變配置和文件權限
5、以限制方式訪問模塊重啟服務器：mysqld
–skip-networking可跳過網絡功能
方法：編輯my.cnf文件，添加如下項
skip-networking
socket=/tmp/mysql-recovery.sock
6、（如果有）載入邏輯備份并檢查和重放二進制日志
7、檢查已經還原的數據，確保沒有問題
8、重新以完全訪問模式重啟服務器
注釋前面在my.cnf中添加的選項并重啟即可。
七、只備份庫中的部分表
在恢復之前要創建表結構。
原創文章，作者：M20-1鐘明波，如若轉載，請注明出處：http://www.www58058.com/59654
