二進制日志詳解：
二進制日志通常作為備份的重要資源，所以再說備份之前先總結一下二進制日志的相關內容
1. 二進制日志的內容
引起mysql服務器改變的任何操作。
復制功能依賴于此日志。
從服務器通過復制主服務器的二進制日志完成主從復制，在執行之前保存于中繼日志中。
從服務器通?？梢躁P閉二進制日志以提升性能。
2. 二進制文件的文件表現形式：
默認在安裝目錄下，存在mysql-bin.00001, mysql-bin.00002的二進制文件
另外還有mysql-bin.index用來記錄被mysql管理的二進制文件列表
如果需要刪除二進制日志時，切勿直接刪除二進制文件，這樣會使得mysql管理混亂
3. 二進制文件查看相關mysql命令。
SHOW MASTER STATUS ; 查看正在使用的二進制文件
MariaDB [(none)]> SHOW MASTER STATUS ; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 245 | | | +------------------+----------+--------------+------------------+
FLUSH LOGS; 手動滾動二進制日志
MariaDB [(none)]> FLUSH LOGS; MariaDB [(none)]> SHOW MASTER STATUS ; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 245 | | | +------------------+----------+--------------+------------------+ 滾動以后，mysql重新創建一個新的日志mysql-bin.000004
SHOW BINARY LOGS 顯示使用過的二進制日志文件
MariaDB [(none)]> SHOW BINARY LOGS ; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 30373 | | mysql-bin.000002 | 1038814 | | mysql-bin.000003 | 288 | | mysql-bin.000004 | 245 |
SHOW BINLOG EVENTS 以表的形式查看二進制文件
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count] MariaDB [(none)]> SHOW BINLOG EVENTS IN 'mysql-bin.000001' \G; *************************** 99. row *************************** Log_name: mysql-bin.000001 Pos: 30225 Event_type: Query Server_id: 1 End_log_pos: 30354 Info: use `mysql`; DROP TEMPORARY TABLE `tmp_proxies_priv` /* generated by server */
4. MySQL二進制文件讀取工具mysqlbinlog
Usage: mysqlbinlog [options] log-files --start-datetime --stop-datetime --start-position --stop-position
# mysqlbinlog --start-position 30225 --stop-position 30254 mysql-bin.000001 截取一下結果： # at 30225 #151130 12:43:35 server id 1 end_log_pos 30354 Querythread_id=1exec_time=0error_code=0 use `mysql`/*!*/; SET TIMESTAMP=1448858615/*!*/; SET @@session.pseudo_thread_id=1/*!*/
根據以上截取結果第二行，進行解釋二進制日志內容 1） 時間點： 151130 12:43:35 2） 服務器ID： server id 1 服務器ID主要用于標記日志產生的服務器，主要用于雙主模型中，互為主從，確保二進制文件不會被相互循環復制 3） 記錄類型： Query 4) 線程號： thread_id = 1 5) 語句的時間戳和寫入二進制日志文件的時間差； exec_time=0 6) 事件內容 7） 事件位置 #at 30225 8) 錯誤代碼 error_code=0 9) 事件結束位置 end_log_pos也就是下一事件開始的位置
5. 二進制日志格式
由bin_log_format={statement|row|mixed}定義
1） statement: 基于語句，記錄生成數據的語句
缺點在于如果當時插入信息為函數生成，有可能不同時間點執行結果不一樣，
例如： INSERT INTO t1 VALUE (CURRENT_DATE());
2） row: 基于行數據
缺點在于，有時候數據量會過大
3） mixed： 混合模式，又mysql自行決定何時使用statement, 何時使用row 模式
6. 二進制相關參數總結
1） log_bin = {ON|OFF}
還可以是個文件路徑，主要用于控制全局binlog的存放位置和是否開啟
2） log_bin_trust_function_creators
是否記錄在
3） sql_log_bin = {ON|OFF}
會話級別是否關閉binlog， 如果關閉當前會話內的操作將不會記錄
4） sync_binlog 是否馬上同步事務類操作到二進制日志中
5） binlog_format = {statement|row|mixed} 二進制日志的格式，上面單獨提到了
6） max_binlog_cache_size =
二進制日志緩沖空間大小，僅用于緩沖事務類的語句；
7） max_binlog_stmt_cache_size =
語句緩沖，非事務類和事務類共用的空間大小
8） max_binlog_size =
二進制日志文件上限，超過上限后則滾動
9) 刪除二進制日志
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }
MariaDB> PURGE BINARY LOGS TO 'mysql-bin.010'; MariaDB> PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26'; 建議：切勿將二進制日志與數據文件放在一同設備；
二進制日志備份和恢復：
為什么做備份：
1. 災難恢復
2. 審計，數據庫在過去某一個時間點是什么樣的
3. 測試
備份的目的：
1. 用于恢復
2. 備份結束后，需要周期性的做恢復測試
備份類型：
1. 根據備份時，服務器是否在線
1） 冷備(cold backup)： 服務器離線，讀寫操作都不能進行
2） 溫備份： 全局施加共享鎖，只能讀不能寫
3） 熱備(hot backup)：數據庫在線，讀寫照樣進行
2. 根據備份時的數據集分類
1） 完全備份(full backup)
2） 部分備份(partial backup)
3. 根據備份時的接口
1） 物理備份（physical backup）： 直接復制數據文件 ，打包歸檔
特點：
不需要額外工具，直接歸檔命令即可，但是跨平臺能力比較差
如果數據量超過幾十個G，則適用于物理備份
2） 邏輯備份(logical backup)： 把數據抽取出來保存在sql腳本中
特點：
可以使用文本編輯器編輯
導入方便，直接讀取sql語句即可
邏輯備份恢復時間慢，占據空間大
無法保證浮點數的精度
恢復完數據庫后需要重建索引
4. 根據備份整個數據還是變化數據
1） 完全備份 full backup
2） 增量備份 incremental backup
在不同時間點起始備份一段數據
比較節約空間
3） 差異備份 differential backup
備份從每個時間點到上一次全部備份之間的數據，隨著時間增多二增多
比較容易恢復
對于很大的數據庫，可以考慮主從模型，備份從服務器的內容。
5. 備份策略，需要考慮因素如下
備份方式
備份實踐
備份成本
鎖時間
時長
性能開銷
恢復成本
恢復時長
所能夠容忍丟失的數據量
6. 備份內容
1） 數據庫中的數據
2） 配置文件
3） mysql中的代碼： 存儲過程，存儲函數，觸發器
4） OS 相關的配置文件，chrontab 中的備份策略腳本
5） 如果是主從復制的場景中： 跟復制相關的信息
6） 二進制日志文件需要定期備份，一旦發現二進制文件出現問題，需馬上對數據進行完全備份
7. 常用備份工具
1）mysqldump: 邏輯備份工具
innodb： 熱備，溫備
MyISAM, Aria: 溫備
單線程備份恢復比較慢
2）mysqldumper: 多線程的mysqldump
3）vm-snapshot:
接近于熱備的工具：因為要先請求全局鎖，而后創建快照，并在創建快照完成后釋放全局鎖；
使用cp、tar等工具進行物理備份；
備份和恢復速度較快；
很難實現增量備份，并且請求全局需要等待一段時間，在繁忙的服務器上尤其如此；
4）SELECT clause INTO OUTFILE '/path/to/somefile'
LOAD DATA INFILE '/path/from/somefile'
部分備份工具， 不會備份關系定義，僅備份表中的數據；
邏輯備份工具，快于mysqldump，因為不備份表格式信息
5）Innobase: 商業備份工具, innobackup
InnoDB熱備，增量備份；
MyISAM溫備，不支持增量，只有完全備份
屬于物理備份，速度快；
6）Xtrabackup: 由Percona提供的開源備份工具
InnoDB熱備，增量備份；
MyISAM溫備，不支持增量；
7）mysqlhotcopy: 接近冷備，基本沒用
mysqldump工具基本使用
1. mysqldump [OPTIONS] database [tables…]
還原時庫必須存在，不存在需要手動創建
--all-databases: 備份所有庫 --databases db1 db2 ...: 備份指定的多個庫，如果使用此命令，恢復時將不用手動創建庫 --lock-all-tables：請求鎖定所有表之后再備份，對MyISAM、InnoDB、Aria做溫備 --lock-table: 對正在備份的表加鎖，但是不建議使用，如果其它表被修改，則備份后表與表之間將不同步 --single-transaction: 能夠對InnoDB存儲引擎實現熱備； 啟動一個很大的大事物，基于MOCC可以保證在事物內的表版本一致 自動加鎖不需要，再加--lock-table, 可以實現熱備 備份代碼： --events: 備份事件調度器代碼 --routines: 備份存儲過程和存儲函數 --triggers：備份觸發器 備份時滾動日志： --flush-logs: 備份前、請求到鎖之后滾動日志； 方恢復備份時間點以后的內容 復制時的同步位置標記：主從架構中的，主服務器數據。效果相當于標記一個時間點。 --master-data=[0|1|2] 0: 不記錄 1：記錄為CHANGE MASTER語句 2：記錄為注釋的CHANGE MASTER語句
2. 使用mysqldump備份大體過程：
1) 請求鎖：–lock-all-tables或使用–singe-transaction進行innodb熱備；
2) 滾動日志：–flush-logs
3) 選定要備份的庫：–databases
4) 記錄二進制日志文件及位置：–master-data=
FLUSH TABLES5 WITH READ LOCK;
3. 恢復：
恢復過程無需寫到二進制日志中
建議：關閉二進制日志，關閉其它用戶連接；
4. 備份策略：基于mysqldump
備份：mysqldump+二進制日志文件；
周日做一次完全備份：備份的同時滾動日志
周一至周六：備份二進制日志；
恢復：
完全備份+各二進制日志文件中至此刻的事件
5. 實例：
1） 完全備份mysql數據庫，并實現還原
備份之前的數據庫
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | students | | test | | testdb | +--------------------+ 此時要確保二進制日志開啟，我實驗的時候發現不知道為什么沒開，于是在配置文件中server段加一行 # vim /etc/my.cnf 中添加log_bin=/data/mysqldata/mysql_bin 然后重啟服務 # service mysqld restart # ls /data/mysqldata/mysql_bin.* 可以看到一下三個文件，證明二進制已然開啟 mysql_bin.000001 mysql_bin.000002 mysql_bin.index 考慮到遠程連接，為安全考慮，最好不用管理員賬號備份，所以需要創建一個用戶專門用作mysqldump備份 mysql> GRANT SELECT,SHOW DATABASES,LOCK TABLES,RELOAD,EVENT ON *.* TO 'dumpper'@'192.168.37.%' IDENTIFIED BY 'dumpper'; Query OK, 0 rows affected (0.00 sec)
然后使用mysqldump進行備份
# mysqldump --events --master-data=2 --all-databases --lock-all-tables --flush-logs -udumpper -h192.168.37.129 -pdumpper > /tmp/dump_bak.sql
這里的選項對應以上給dumpper用戶賦予的權限
SHOW DATABASES ---> --all-databases
LOCK TABLES ---> --lock-all-tables
RELOAD ---> --flush-logs
EVENT ---> --events
SUPPER ---> --master-data 主要授予SHOW MASTER STATUS權限
在hellodb.students表中插入一行，再刪除一個hellodb 然后嘗試恢復
mysql> INSERT hellodb.students (Name,Age,Gender) VALUE ('Linghu Chong',28,'M');
mysql> DROP DATABASE hellodb;
此時需要關閉session級別的二進制日志使得，恢復內容不記錄日志
mysql> SET SESSION sql_log_bin='OFF';
mysql> SOURCE /tmp/dump_bak.sql;
此時數據庫恢復到，插入Linghu Chong 這一行之前，然后通過二進制日志恢復直到數據庫被刪除之前的內容
由于設置了--master-data選項，所以在備份文件中可以找到如下一行
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000005', MASTER_LOG_POS=106;
這一行標記了新的二進制文件從那個點起始，通過查看二進制日志mysql_bin.000005可以得知，還需要恢復106到134也就是hellodb被刪除之前的數據
# mysqlbinlog --start-position 106 --stop-position 271 /data/mysqldata/mysql_bin.000005 > binlog106_271.sql
mysql>SOURCE /tmp/binlog106_271.sql
mysql> SELECT * FROM hellodb.mysql 可以看到最后一行，以前插入的數據重新恢復
| 26 | Linghu Chong | 28 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
2) 編寫腳本，并加入crontab, 為了看效果比較快，這里設定每小時執行一次
腳本如下：
#!/bin/bash
#
## Using mysqldump to backup the all databases
function backup {
prefix=$1
outputdir=$2
[ -d $outputdir ] ||(echo 'No output dir, creating one!' && mkdir -p $outputdir)
now=`/bin/date +'%Y_%b_%d_%k%M'`
/usr/local/mysql/bin/mysqldump --events --master-data=2 --all-databases --single-transaction --flush-logs -udumpper -h192.168.98.129 -pdumpper > $outputdir/${prefix}_${now}.sql
}
function main {
case $# in
2)
backup $1 $2
;;
*)
echo 'Usage: ./mysqldump_wrapper.sh prefix outputdir'
;;
esac
}
main $*
周期任務計劃表 # crontab -e 添加如下內容 * * * * * /usr/local/mysql/mysqldump_wrapper.sh back /tmp/mysqlback ## 如果正常的話，在備份目錄中，每分鐘將備份一次 -rw-r--r-- 1 root root 521824 Dec 1 11:42 back_2015_Dec_01_1142.sql -rw-r--r-- 1 root root 521824 Dec 1 11:43 back_2015_Dec_01_1143.sql rw-r--r-- 1 root root 521824 Dec 1 11:44 back_2015_Dec_01_1144.sql
lvm-snapshot：基于LVM快照的備份
關于快照：
1. 事務日志跟數據文件必須在同一個卷上；
2. 剛剛創立的快照卷，里面沒有任何數據，所有數據均來源于原卷
3. 一旦原卷數據發生修改，修改的數據將復制到快照卷中，此時訪問數據一部分來自于快照卷，一部分來自于原卷
4. 當快照使用過程中，如果修改的數據量大于快照卷容量，則會導致快照卷崩潰。
5. 快照卷本身不是備份，只是提供一個時間一致性的訪問目錄。
基于快照備份幾乎為熱備：
1. 創建快照卷之前，要請求MySQL的全局鎖；在快照創建完成之后釋放鎖；
2. 如果是Inoodb引擎， 當flush tables 后會有一部分保存在事務日志中，卻不在文件中。 因此恢復時候，需要事務日志和數據文件
但釋放鎖以后，事務日志的內容會同步數據文件中，因此備份內容并不絕對是鎖釋放時刻的內容，由于有些為完成的事務已經完成，但在備份數據中因為沒完成而回滾。 因此需要借助二進制日志往后走一段
基于快照備份注意事項：
1. 事務日志跟數據文件必須在同一個卷上；
2. 創建快照卷之前，要請求MySQL的全局鎖；在快照創建完成之后釋放鎖；
3. 請求全局鎖完成之后，做一次日志滾動；做二進制日志文件及位置標記(手動進行)；
備份與恢復的大體步驟
備份
1. 請求全局鎖，并滾動日志
mysql> FLUSH TABLES WITH READ LOCK; mysql> FLUSH LOGS;
2. 做二進制日志文件及位置標記(手動進行)；
# mysql -e 'show master status' > /path/to/orignal_volume
3. 創建快照卷
# lvcreate -L -s -n -p r /path/to/some_lv
4.釋放全局鎖
5. 掛載快照卷并備份
6.備份完成之后，刪除快照卷
恢復：
1. 二進制日志保存好；
提取備份之后的所有事件至某sql腳本中；
2. 還原數據，修改權限及屬主屬組等，并啟動mysql
3. 做即時點還原
4. 生產環境下， 一次大型恢復后，需要馬上進行一次完全備份。
備份與恢復事例：
環境， 實現創建了一個test_vg卷組，里面有個mylv1用來裝mysql數據，掛載到/data/mysqldata
備份：
1. 創建備份專用的用戶，授予權限FLUSH LOGS 和 LOCK TABLES
MariaDB > GRANT RELOAD,LOCK TABLES,SUPER ON *.* TO 'lvm'@'192.168.98.%' IDENTIFIED BY 'lvm'; MariaDB > FLUSH PRIVILEGES;
2. 記錄備份點
# mysql -ulvm -h192.168.98.129 -plvm -e 'SHOW MASTER STATUS' > /tmp/backup_point.txt
3. 創建快照卷并掛載快照卷
# lvcreate -L 1G -s -n lvmbackup -p r /dev/test_vg/mylv1 # mount -t ext4 /dev/test_vg/lvmbackup /mnt/
4. 釋放鎖
# mysql -ulvm -h192.168.98.129 -plvm -e 'UNLOCK TABLES' ## 做一些模擬寫入工作 MariaDB [test]> create database testdb2
5. 復制文件
# cp /data/mysqldata /tmp/backup_mysqldata -r
6. 備份完成卸載，刪除快照卷
# umount /mnt # lvmremove /dev/test_vg/lvmbackup
還原： 假如整個mysql服務器崩潰，并且目錄全部被刪除
1. 數據文件復制回源目錄
# cp -r /tmp/backup_mysqldata/* /data/mysqldata/ MariaDB [test]> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | mysqldata | | openstack | | performance_schema | | test | +--------------------+ 此時還沒有testdb2， 因為這個是備份之后創建的，因此需要通過之前記錄的二進制日志位置向后還原
2. 查看之前記錄的記錄點。向后還原
# cat /tmp/backup_point.txt FilePositionBinlog_Do_DBBinlog_Ignore_DB mysql-bin.000001245 # mysqlbinlog /data/binlog/mysql-bin.000001 --start-position 245 > tmp.sql MariaDB [test]> source /data/mysqldata/tmp.sql MariaDB [test]> show databases ; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | mysqldata | | openstack | | performance_schema | | test | | testdb2 | +--------------------+ 8 rows in set (0.00 sec) testdb2 已經被還原回來。
使用Xtrabackup進行MySQL備份：
安裝
1. 簡介
Xtrabackup是由percona提供的mysql數據庫備份工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb數據庫進行熱備的工具。特點：
1) 備份過程快速、可靠；
2) 備份過程不會打斷正在執行的事務；
3) 能夠基于壓縮等功能節約磁盤空間和流量；
4) 自動實現備份檢驗；
5) 還原速度快；
2. 安裝
其最新版的軟件可從 http://www.percona.com/software/percona-xtrabackup/ 獲得。本機使用2.2.12版本
# yum install percona-toolkit-2.2.16-1.noarch.rpm # yum install percona-xtrabackup-2.3.2-1.el6.x86_64.rpm
完全備份
如果要使用一個最小權限的用戶進行備份，則可基于如下命令創建此類用戶：
Usage: innobackupex --user=DBUSER --password=DBUSERPASS /path/to/BACKUP-DIR/ --user: 需要創建一個擁有最小權限的用戶 MariaDB [(none)]> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrauser'@'localhost' IDENTIFIED BY 'xtrauser' ; MariaDB [(none)]> FLUSH PRIVILEGES ;
/path/to/BACKUP_DIR
備份出來的數據存放目錄，外加包含一些xtrabackup的元數據
使用innobakupex備份時，其會調用xtrabackup備份所有的InnoDB表，復制所有關于表結構定義的相關文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關文件，同時還會備份觸發器和數據庫配置信息相關的文件。這些文件會被保存至一個以時間命令的目錄中。
事例：
# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup/ ... 151202 14:52:01 Executing UNLOCK TABLES 151202 14:52:01 All tables unlocked 151202 14:52:01 Backup created in directory '/tmp/xtrabackup//2015-12-02_14-51-56' MySQL binlog position: filename 'mysql-bin.000001', position '952' 151202 14:52:01 [00] Writing backup-my.cnf 151202 14:52:01 [00] ...done 151202 14:52:01 [00] Writing xtrabackup_info 151202 14:52:01 [00] ...done xtrabackup: Transaction log of lsn (1752057) to (1752057) was copied. 151202 14:52:01 completed OK!
當看到最后這一行時候，說明備份已經完成
在備份的同時，innobackupex還會在備份目錄中創建如下文件：
1. xtrabackup_checkpoints: 備份類型（如完全或增量）、備份狀態（如是否已經為prepared狀態）和LSN(Log Serial Number日志序列號)范圍信息；
# cat xtrabackup_checkpoints backup_type = full-backuped from_lsn = 0 to_lsn = 1752057 last_lsn = 1752057 compact = 0 recover_binlog_info = 0
在mysql中，存儲數據的數據塊會有按照順序的ID， 如果某一塊數據被修改，將會賦予新的ID。 根據這些ID，可以標記數據的新老成都。xtrabackup也就是使用這些ID來進行備份，和增量備份
每個InnoDB頁(通常為16k大小)都會包含一個日志序列號，即LSN。LSN是整個數據庫系統的系統版本號，每個頁面相關的LSN能夠表明此頁面最近是如何發生改變的。
2. xtrabackup_binlog_info: mysql服務器當前正在使用的二進制日志文件及至備份這一刻為止二進制日志事件的位置。
# cat xtrabackup_binlog_info mysql-bin.000001952
3. xtrabackup_info: 包含很多xtrabackup工具信息以及所備份的數據庫信息
# cat xtrabackup_info uuid = 3073ff65-98c1-11e5-9af1-000c29622425 name = tool_name = innobackupex tool_command = --user=xtrauser --password=... /tmp/xtrabackup/ tool_version = 2.3.2 ibbackup_version = 2.3.2 server_version = 5.5.46-MariaDB-log start_time = 2015-12-02 14:51:59 end_time = 2015-12-02 14:52:01 lock_time = 0 binlog_pos = filename 'mysql-bin.000001', position '952' innodb_from_lsn = 0 innodb_to_lsn = 1752057 partial = N incremental = N format = file compact = N compressed = N encrypted = N
4. backup-my.cnf —— 備份命令用到的配置選項信息；
# cat backup-my.cnf # This MySQL options file was generated by innobackupex. # The MySQL server [mysqld] innodb_checksum_algorithm=innodb innodb_log_checksum_algorithm=innodb innodb_data_file_path=ibdata1:10M:autoextend innodb_log_files_in_group=2 innodb_log_file_size=5242880 innodb_fast_checksum=false innodb_page_size=16384 innodb_log_block_size=512 innodb_undo_directory=. innodb_undo_tablespaces=0
在使用innobackupex進行備份時，還可以使用–no-timestamp選項來阻止命令自動創建一個以時間命名的目錄；如此一來，innobackupex命令將會創建一個BACKUP-DIR目錄來存儲備份數據。
準備(prepare)一個完全備份
一般情況下，在備份完成后，數據尚且不能用于恢復操作，因為備份的數據中可能會包含尚未提交的事務或已經提交但尚未同步至數據文件中的事務。
因此，此時數據文件仍處理不一致狀態。“準備”的主要作用正是通過回滾未提交的事務及同步已經提交的事務至數據文件也使得數據文件處于一致性狀態。
innobakupex命令的–apply-log選項可用于實現上述功能。如下面的命令： 實際上就是把未完成的事務提交，準備工作需要在還原之前才執行，在這之前都能執行準備工作。
Usage: innobackupex --apply-log /path/to/BACKUP-DIR 事例： # innobackupex --apply-log /tmp/xtrabackup/2015-12-02_15-10-53/ xtrabackup: starting shutdown with innodb_fast_shutdown = 1 InnoDB: FTS optimize thread exiting. InnoDB: Starting shutdown... InnoDB: Shutdown completed; log sequence number 1752598 151202 15:19:05 completed OK! 出現這幾行，說明準備完成 在實現“準備”的過程中，innobackupex通常還可以使用--use-memory選項來指定其可以使用的內存的大小，默認通常為100M。如果有足夠的內存可用，可以多劃分一些內存給prepare的過程，以提高其完成速度。
從一個完全備份中恢復數據
注意：恢復不用啟動MySQL
innobackupex命令的–copy-back選項用于執行恢復操作，其通過復制所有數據相關的文件至mysql服務器DATADIR目錄中來執行恢復過程。innobackupex通過backup-my.cnf來獲取 DATADIR目錄的相關信息。
usage: innobackupex --copy-back /path/to/BACKUP-DIR # innobackupex --copy-back /tmp/xtrabackup/2015-12-02_15-10-53/ 如果執行正確，其輸出信息的最后幾行通常如下： 151202 15:23:32 [01] Copying ./mysql/time_zone_transition.MYI to /data/mysqldata/mysql/time_zone_transition.MYI 151202 15:23:32 [01] ...done 151202 15:23:32 completed OK!
當數據恢復至DATADIR目錄以后，還需要確保所有數據文件的屬主和屬組均為正確的用戶，如mysql，否則，在啟動mysqld之前還需要事先修改數據文件的屬主和屬組。如：
# chown -R mysql:mysql /data/mysqldata
我發現的坑：
這里我發現一個坑，如果備份時，在配置文件中沒有明確定義innodb_log_size大小，這里可能無法啟動服務。
原因是，xtrabackup的默認innodb_log_size可能與mysql不一致。 這里使用的xtrabackup是比較新的版本2.3.2
默認生成的日志大小為50331648而不是5242880，所以會出現問題。 解決方法是手工設定一下日志大小，寫在配置文件中。
使用innobackupex進行增量備份
每個InnoDB的頁面都會包含一個LSN信息，每當相關的數據發生改變，相關的頁面的LSN就會自動增長。這正是InnoDB表可以進行增量備份的基礎，即innobackupex通過備份上次完全備份之后發生改變的頁面來實現。
1. 備份過程：
要實現第一次增量備份，可以使用下面的命令進行：
usage: innobackupex –incremental /backup –incremental-basedir=BASEDIR
BASEDIR：
指的是完全備份所在的目錄，此命令執行結束后，innobackupex命令會在/backup目錄中創建一個新的以時間命名的目錄以存放所有的增量備份數據。另外，在執行過增量備份之后再一次進行增量備份時，其–incremental-basedir應該指向上一次的增量備份所在的目錄。
需要注意的是，增量備份僅能應用于InnoDB或XtraDB表，對于MyISAM表而言，執行增量備份時其實進行的是完全備份。
舉例:
先做一次完全備份，在之前完全備份的基礎上做兩次增量，之間創建兩個數據庫
全備份：
# innobackupex --user=xtrauser --password=xtrauser --no-timestamp /tmp/xtrabackup/full_backup MariaDB [(none)]> CREATE DATABASE testdb1；
第一次增量：
# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/full_backup MariaDB [(none)]> CREATE DATABASE testdb2；
第二次增量：
# innobackupex --user=xtrauser --password=xtrauser --incremental /tmp/xtrabackup/ --incremental-basedir=/tmp/xtrabackup/2015-12-02_17-15-35
2. 準備過程
“準備”(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指的是第二次增量備份的目錄，其它依次類推，即如果有多次增量備份，每一次都要執行如上操作；
事例：
# innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup # innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup/ --incremental-dir=/tmp/xtrabackup/2015-12-02_17-15-35 # innobackupex --apply-log --redo-only /tmp/xtrabackup/full_backup/ --incremental-dir=/tmp/xtrabackup/2015-12-02_17-17-14
3. 恢復過程：與完全備份類似，直接copy-back完全備份的那個目錄。 此時所有的增量已經正好到完全備份的目錄中
# rm -rf /data/mysqldata/* # innobackupex --copy-back /tmp/xtrabackup/full_backup/ # chown -R mysql:mysql /data/mysqldata MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | hellodb | | mysql | | performance_schema | | test | | test1 | | testdb1 | | testdb2 | +--------------------+ 8 rows in set (0.04 sec) 可以看到后面創建的兩個testdb1, testdb2 全部恢復回來
Xtrabackup的“流”及“備份壓縮”功能
Xtrabackup對備份的數據文件支持“流”功能，即可以將備份的數據通過STDOUT傳輸給tar程序進行歸檔，而不是默認的直接保存至某備份目錄中。要使用此功能，僅需要使用–stream選項即可。如：
usage: innobackupex –stream=tar /backup | gzip > /backup/`date +%F_%H-%M-%S`.tar.gz
事例：
# innobackupex --user=xtrauser --password=xtrauser --stream=tar /tmp/xtrabackup/ | gzip > /tmp/xtrabackup/`date +%F_%H-%M-%S`.tar.gz
甚至也可以使用類似如下命令將數據備份至其它服務器：
usage: innobackupex –stream=tar /backup | ssh user@hostname "cat – > /backups/`date +%F_%H-%M-%S`.tar"
# innobackupex --user=xtrauser --password=xtrauser --stream=tar /tmp/xtrabackup | ssh root@192.168.98.129 "cat - > /tmp/`date +%F_%H-%M-%S`.tar"
這里有個bug， 會進入無限log記錄，好像是新版本的問題，目前還不知道怎么解決。
此外，在執行本地備份時，還可以使用–parallel選項對多個文件進行并行復制。此選項用于指定在復制時啟動的線程數目。當然，在實際進行備份時要利用此功能的便利性，也需要啟用innodb_file_per_table選項或共享的表空間通過innodb_data_file_path選項存儲在多個ibdata文件中。對某一數據庫的多個文件的復制無法利用到此功能。其簡單使用方法如下：
usage： innobackupex –parallel /path/to/backup
同時，innobackupex備份的數據文件也可以存儲至遠程主機，這可以使用–remote-host選項來實現： 貌似此功能在2.1以后就被取消了
usage: innobackupex –remote-host=root@hostname /path/IN/REMOTE/HOST/to/backup
導入或導出單張表
默認情況下，InnoDB表不能通過直接復制表文件的方式在mysql服務器之間進行移植，即便使用了innodb_file_per_table選項。而使用Xtrabackup工具可以實現此種功能，不過，此時需要“導出”表的mysql服務器啟用了innodb_file_per_table選項（嚴格來說，是要“導出”的表在其創建之前，mysql服務器就啟用了innodb_file_per_table選項），并且“導入”表的服務器同時啟用了innodb_file_per_table和innodb_expand_import選項。
在創建數據庫之前，在配置文件中server段下面寫入innodb_file_per_table=1, 記得是1不是on也不是yes,這個坑出現在5.1版本中，我看了后面的mariadb沒有這個問題
1) “導出”表
導出表是在備份的prepare階段進行的，因此，一旦完全備份完成，就可以在prepare過程中通過–export選項將某表導出了：
usage: innobackupex –apply-log –export /path/to/backup
事例：
# innobackupex --user=xtrauser --password=xtrauser /tmp/xtrabackup # innobackupex --apply-log --export /tmp/2015-12-03_12-37-35/
此命令會為每個innodb表的表空間創建一個以.exp結尾的文件，這些以.exp結尾的文件則可以用于導入至其它服務器。
# ls *.exp classes.exp coc.exp courses.exp scores.exp students.exp teachers.exp toc.exp
2 )“導入”表
使用show CREATE TABLE mytable; 來查看原始表創建命令
要在mysql服務器上導入來自于其它服務器的某innodb表，需要先在當前服務器上創建一個跟原表表結構一致的表，而后才能實現將表導入：
事例： 這里以students 表為例
mysql> SHOW CREATE TABLE hellodb.students \G;
*************************** 1. row ***************************
Table: students
Create Table: CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
在testdb庫中創建這個表
mysql> CREATE TABLE `students` (
`StuID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`Name` varchar(50) NOT NULL,
`Age` tinyint(3) unsigned NOT NULL,
`Gender` enum('F','M') NOT NULL,
`ClassID` tinyint(3) unsigned DEFAULT NULL,
`TeacherID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`StuID`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8
然后將此表的表空間刪除：
mysql> ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
接下來，將來自于“導出”表的服務器的students表的students.ibd和students.exp文件復制到當前服務器的數據目錄，然后使用如下命令將其“導入”：
注意權限。。。。
# cp /tmp/2015-12-03_12-37-35/hellodb/students{.ibd,.exp} /data/mysqldata/testdb/
# chown mysql.mysql /data/mysqldata/testdb/students.*
mysql> ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
好像這里也有bug 明天試一下
嘗試結果如下：
1) 在mysql 5.1 版本中無法實現
2) mariadb 5.5 也不行
3) 只有在mysql 5.6中可以成功實現
最后這里是馬哥對于備份和恢復的幾點經驗之談，請允許我無恥的總結在這里
備份注意：
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中添加的選項，并重啟；
原創文章，作者：以馬內利，如若轉載，請注明出處：http://www.www58058.com/9781
