文/Bruce.Liu1
文章大綱
- 備份概念
1.1. 備份目的
1.2. 備份方式
1.3. 備份類型
1.4. 備份對(duì)象
1.5. 備份策略- mysqldump
2.1. mysqldump原理
2.2. 相關(guān)參數(shù)
2.3. 最佳實(shí)踐- Xtrabackup
3.1. Xtrabackup原理
3.2. 文件介紹
3.3. 相關(guān)參數(shù)
3.4. 最佳實(shí)踐- mydumper
4.1. mydumper原理
4.2. 相關(guān)參數(shù)
4.3. 最佳實(shí)踐
1.備份概念
為什么要備份?我們?cè)囍胍幌? 在生產(chǎn)環(huán)境中什么最重要？如果我們服務(wù)器的硬件壞了可以維修或者換新, 軟件問題可以修復(fù)或重新安裝, 但是如果數(shù)據(jù)沒了呢？這可能是最恐怖的事情了吧,我感覺在生產(chǎn)環(huán)境中應(yīng)該沒有什么比數(shù)據(jù)跟更為重要. 那么我們?cè)撊绾伪ＷC數(shù)據(jù)不丟失、或者丟失后可以快速恢復(fù)呢？只要看完這篇, 大家應(yīng)該就能對(duì)數(shù)據(jù)備份和恢復(fù)能有一定的了解。
當(dāng)年的911事件致使多少個(gè)金融公司遭受巨額損失，據(jù)初步估計(jì)，損失在100億美元以上。辦公室設(shè)在世貿(mào)大樓上的公司就更加倒霉，他們的業(yè)務(wù)幾近癱瘓，大量數(shù)據(jù)丟失，其中著名的公司有美洲銀行、朝日銀行、德國(guó)銀行、國(guó)際信托銀行、肯珀保險(xiǎn)公司、馬什保險(xiǎn)公司、帝國(guó)人壽保險(xiǎn)公司、蓋伊·卡彭特保險(xiǎn)公司、坎特·菲茲杰拉德投資公司、摩根斯坦利金融公司、美國(guó)商品期貨交易所。其中，摩根斯坦利損失最慘，因?yàn)樗谑蕾Q(mào)中心總共租用了29.8萬平米的辦公用地，公司很多職員連辦公地點(diǎn)都沒有。
更多的故障案例：
1. GitLab.com丟失6個(gè)小時(shí)數(shù)據(jù)，操作員將被迫看“10小時(shí)的彩虹貓”，以示懲罰！
2. 攜程內(nèi)部數(shù)據(jù)嚴(yán)重丟失問題
3. 谷歌數(shù)據(jù)中心遭雷劈 部分?jǐn)?shù)據(jù)永久丟失
4. 9.11事件造成的建筑數(shù)據(jù)等財(cái)產(chǎn)直接損失達(dá)340億美元
1.1.備份目的
在生產(chǎn)環(huán)境中我們數(shù)據(jù)庫可能會(huì)遭遇各種各樣的不測(cè)從而導(dǎo)致數(shù)據(jù)丟失，數(shù)據(jù)備份就是針對(duì)以下幾種情況：
- 硬件故障
- 軟件故障
- 自然災(zāi)害
- 黑客攻擊
- 誤操作 (占比最大)
總結(jié)：針對(duì)以上各種災(zāi)難性的場(chǎng)景發(fā)生，一個(gè)良好的備份，能恢復(fù)至故障前一秒正常的運(yùn)行狀態(tài)。
1.2.備份方式
備份方式
冷備（cold backup）：需要關(guān)mysql服務(wù)，讀寫請(qǐng)求均不允許狀態(tài)下進(jìn)行；
1.即：停止數(shù)據(jù)庫、將整個(gè)數(shù)據(jù)文件、日志文件、配置文件等拷貝至一個(gè)鏡像。熱備（hot backup）：備份的同時(shí)，業(yè)務(wù)不受影響。
1.邏輯備份
a) mysqldump：官方自帶的邏輯備份工具
b) mydumper：開源的多線程邏輯備份工具
2.物理備份
a) Enterprise Backup：官方企業(yè)級(jí)備份工具(收費(fèi)產(chǎn)品)
b) Xtrabackup tools：業(yè)界中最著名的開源備份工具，由Percona公司研發(fā)。
引擎支持是否備份方式
| 引擎 | 熱備 | 冷備 |
|---|---|---|
| MyISAM | × | √ |
| InnoDB | √ | √ |
| TokuDB | √ | √ |
備份工具是否支持引擎
- 邏輯的備份工具一般來說都支持各種引擎(邏輯的導(dǎo)出和底層的Engine沒啥關(guān)系!!)
- 物理備份工具就有很大的局限性，著名的Xtrabackup也只是支持MyISAM、innodb引擎
1.3.備份類型
完全備份：
full backup，備份全部數(shù)據(jù)集。增量備份:
incremental backup，上次完全備份或增量備份以來改變了的數(shù)據(jù)，不能單獨(dú)使用，要借助完全備份，備份的頻率取決于數(shù)據(jù)的更新頻率。差異備份：
differential backup，上次完全備份以來改變了的數(shù)據(jù)。
1.4. 備份對(duì)象
一般情況下, 我們需要備份的數(shù)據(jù)分為以下幾種
- 全量數(shù)據(jù)
- 二進(jìn)制日志, InnoDB事務(wù)日志
- 代碼(存儲(chǔ)過程、存儲(chǔ)函數(shù)、觸發(fā)器、事件調(diào)度器)
- 服務(wù)器配置文件
1.5.備份策略
在訂制一個(gè)備份策略之前，首先我們先要向架構(gòu)或業(yè)務(wù)部門了解清楚，我們的業(yè)務(wù)模型/場(chǎng)景類型/安全優(yōu)先級(jí)/以及對(duì)數(shù)據(jù)恢復(fù)的時(shí)間要求等因素， 在我遇到的很多中小型公司/甚至連一些互聯(lián)網(wǎng)金融機(jī)構(gòu)等對(duì)于數(shù)據(jù)安全級(jí)別很高的場(chǎng)景中，對(duì)數(shù)據(jù)庫備份策略也是一無所知，他們常見的做法幾乎都是由信息部門數(shù)據(jù)庫負(fù)責(zé)人或信息部技術(shù)意淫出來的。沒有人會(huì)徹徹底底的觀察場(chǎng)景/業(yè)務(wù)/以及安全級(jí)別等因素。
下面我們舉常見的幾種業(yè)務(wù)場(chǎng)景進(jìn)行分析：
SNS社交網(wǎng)絡(luò)服務(wù)類場(chǎng)景(Social Network Site)(國(guó)內(nèi)常見的SNS如：微博/人人網(wǎng)/豆瓣/論壇)
a) 高并發(fā)
b) 高可靠性99.999%
c) 低數(shù)據(jù)一致性
d) 數(shù)據(jù)恢復(fù)時(shí)間要求高傳統(tǒng)行業(yè)類業(yè)務(wù)場(chǎng)景(電力/物流/零售/服務(wù)/餐飲)
a) 普遍低并發(fā)
b) 高可靠性99.999%
c) 高數(shù)據(jù)一致性
d) 數(shù)據(jù)恢復(fù)時(shí)間一般傳統(tǒng)金融類場(chǎng)景(銀行/證券/股票)
a) 普遍低并發(fā)
b) 高可靠性99.999%
c) 高數(shù)據(jù)一致性
d) 數(shù)據(jù)恢復(fù)時(shí)間一般互聯(lián)網(wǎng)金融/支付類場(chǎng)景(微信/支付寶/P2P/電子商務(wù))
a) 并發(fā)一般
b) 高可靠性99.999%
c) 高數(shù)據(jù)一致性
d) 數(shù)據(jù)恢復(fù)時(shí)間高
以上任何一類業(yè)務(wù)場(chǎng)景中，對(duì)于可靠性都期望是99.999%，如果不知道什么是5個(gè)9，請(qǐng)自己默默的打開鏈接掃掃盲。但他們對(duì)于數(shù)據(jù)一致性要求卻有著重大的不同。那有的同學(xué)會(huì)說了，可靠性和數(shù)據(jù)一致性可不可以兼顧呢？我相信大家都知道魚和熊掌不可兼得的道理吧！
2.mysqldump
MySQL Client管理工具也叫：獨(dú)立的管理實(shí)用程序，程序本身不依賴于其他程序模塊。能夠獨(dú)立運(yùn)行。這是MySQL的特性。
2.1.mysqldump原理
1.連接MySQL數(shù)據(jù)庫,設(shè)置基本session級(jí)環(huán)境變量
2.開啟快照級(jí)事務(wù)(加鎖)并獲取當(dāng)前的GTID
3.釋放鎖資源
4.create savepoint
5.獲取表結(jié)構(gòu)信息/設(shè)置字符集/備份數(shù)據(jù)
6.roll back savepoint
7.重復(fù)步驟5~6直至備份完成
savepoint原理
2.2.相關(guān)參數(shù)
--all-databases , -A 導(dǎo)出全部數(shù)據(jù)庫。
--all-tablespaces , -Y 導(dǎo)出全部表空間。
--no-tablespaces , -y 不導(dǎo)出任何表空間信息。
--add-drop-database 每個(gè)數(shù)據(jù)庫創(chuàng)建之前添加drop數(shù)據(jù)庫語句。
--add-drop-table 每個(gè)數(shù)據(jù)表創(chuàng)建之前添加drop數(shù)據(jù)表語句。(默認(rèn)為打開狀態(tài))
--add-locks 在每個(gè)表導(dǎo)出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默認(rèn)為打開狀態(tài))
--allow-keywords 允許創(chuàng)建是關(guān)鍵詞的列名字。這由表名前綴于每個(gè)列名做到。
--apply-slave-statements 在'CHANGE MASTER'前添加'STOP SLAVE'，并且在導(dǎo)出的最后添加'START SLAVE'。
--character-sets-dir 字符集文件的目錄
--comments 附加注釋信息。默認(rèn)為打開，可以用--skip-comments取消
--compatible 導(dǎo)出的數(shù)據(jù)將和其它數(shù)據(jù)庫或舊版本的MySQL 相兼容。值可以為mysql323、mysql40、PG、oracle、mssql等，用逗號(hào)將它們隔開。它并不保證能完全兼容，而是盡量兼容。
--compact 導(dǎo)出更少的輸出信息(用于調(diào)試)。去掉注釋和頭尾等結(jié)構(gòu)?？梢允褂眠x項(xiàng)：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
--complete-insert, -c 使用完整的insert語句(包含列名稱)。這么做能提高插入效率，但是可能會(huì)受到max_allowed_packet參數(shù)的影響而導(dǎo)致插入失敗。
--compress, -C 在客戶端和服務(wù)器之間啟用壓縮傳遞所有信息
--create-options, -a 在CREATE TABLE語句中包括所有MySQL特性選項(xiàng)。(默認(rèn)為打開狀態(tài))
--databases, -B 導(dǎo)出幾個(gè)數(shù)據(jù)庫。參數(shù)后面所有名字參量都被看作數(shù)據(jù)庫名。
--debug 輸出debug信息，用于調(diào)試。默認(rèn)值為：d:t:o,/tmp/mysqldump.trace
--debug-check 檢查內(nèi)存和打開文件使用說明并退出。
--debug-info 輸出調(diào)試信息并退出
--default-character-set 設(shè)置默認(rèn)字符集，默認(rèn)值為utf8
--delayed-insert 采用延時(shí)插入方式（INSERT DELAYED）導(dǎo)出數(shù)據(jù)
--delete-master-logs master 備份后刪除日志. 這個(gè)參數(shù)將自動(dòng)激活--master-data。
--disable-keys 對(duì)于每個(gè)表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;語句引用INSERT語句。該選項(xiàng)只適合MyISAM表，默認(rèn)為打開狀態(tài)。
--dump-slave 該選項(xiàng)將導(dǎo)致主的binlog位置和文件名追加到導(dǎo)出數(shù)據(jù)的文件中。設(shè)置為1時(shí)，將會(huì)以CHANGE MASTER命令輸出到數(shù)據(jù)文件；設(shè)置為2時(shí)，在命令前增加說明信息。該選項(xiàng)將會(huì)打開--lock-all-tables，除非--single-transaction被指定。該選項(xiàng)會(huì)自動(dòng)關(guān)閉--lock-tables選項(xiàng)。默認(rèn)值為0。
--events, -E 導(dǎo)出事件。
--extended-insert, -e 使用具有多個(gè)VALUES列的INSERT語法。這樣使導(dǎo)出文件更小，并加速導(dǎo)入時(shí)的速度。默認(rèn)為打開狀態(tài)，使用--skip-extended-insert取消選項(xiàng)。
--fields-terminated-by 導(dǎo)出文件中忽略給定字段。與--tab選項(xiàng)一起使用，不能用于--databases和--all-databases選項(xiàng)
--fields-enclosed-by 輸出文件中的各個(gè)字段用給定字符包裹。與--tab選項(xiàng)一起使用，不能用于--databases和--all-databases選項(xiàng)
--fields-optionally-enclosed-by 輸出文件中的各個(gè)字段用給定字符選擇性包裹。與--tab選項(xiàng)一起使用，不能用于--databases和--all-databases選項(xiàng)
--fields-escaped-by 輸出文件中的各個(gè)字段忽略給定字符。與--tab選項(xiàng)一起使用，不能用于--databases和--all-databases選項(xiàng)
--flush-logs 開始導(dǎo)出之前刷新日志。
--flush-privileges 在導(dǎo)出mysql數(shù)據(jù)庫之后，發(fā)出一條FLUSH PRIVILEGES 語句。為了正確恢復(fù)，該選項(xiàng)應(yīng)該用于導(dǎo)出mysql數(shù)據(jù)庫和依賴mysql數(shù)據(jù)庫數(shù)據(jù)的任何時(shí)候。
--force ,-f 在導(dǎo)出過程中忽略出現(xiàn)的SQL錯(cuò)誤。
--help 顯示幫助信息并退出。
--hex-blob 使用十六進(jìn)制格式導(dǎo)出二進(jìn)制字符串字段。如果有二進(jìn)制數(shù)據(jù)就必須使用該選項(xiàng)。影響到的字段類型有BINARY、VARBINARY、BLOB。
--host, -h 需要導(dǎo)出的主機(jī)信息
--ignore-table 不導(dǎo)出指定表。指定忽略多個(gè)表時(shí)，需要重復(fù)多次，每次一個(gè)表。每個(gè)表必須同時(shí)指定數(shù)據(jù)庫和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
--include-master-host-port 在--dump-slave產(chǎn)生的'CHANGE MASTER TO..'語句中增加'MASTER_HOST=<host>，MASTER_PORT=<port>'
--insert-ignore 在插入行時(shí)使用INSERT IGNORE語句.
--lines-terminated-by 輸出文件的每行用給定字符串劃分。與--tab選項(xiàng)一起使用，不能用于--databases和--all-databases選項(xiàng)。
--lock-all-tables, -x 提交請(qǐng)求鎖定所有數(shù)據(jù)庫中的所有表，以保證數(shù)據(jù)的一致性。這是一個(gè)全局讀鎖，并且自動(dòng)關(guān)閉--single-transaction 和--lock-tables 選項(xiàng)。
--lock-tables, -l 開始導(dǎo)出前，鎖定所有表。用READ LOCAL鎖定表以允許MyISAM表并行插入。
--log-error 附加警告和錯(cuò)誤信息到給定文件
--master-data 該選項(xiàng)將binlog的位置和文件名追加到輸出文件中。如果為1，將會(huì)輸出CHANGE MASTER 命令；如果為2，輸出的CHANGE MASTER命令前添加注釋信息。該選項(xiàng)將打開--lock-all-tables 選項(xiàng)，除非--single-transaction也被指定（在這種情況下，全局讀鎖在開始導(dǎo)出時(shí)獲得很短的時(shí)間；其他內(nèi)容參考下面的--single-transaction選項(xiàng)）。該選項(xiàng)自動(dòng)關(guān)閉--lock-tables選項(xiàng)。
--max_allowed_packet 服務(wù)器發(fā)送和接受的最大包長(zhǎng)度。
--net_buffer_length TCP/IP和socket連接的緩存大小。
--no-autocommit 使用autocommit/commit 語句包裹表。
--no-create-db, -n 只導(dǎo)出數(shù)據(jù)，而不添加CREATE DATABASE 語句。
--no-create-info, -t 只導(dǎo)出數(shù)據(jù)，而不添加CREATE TABLE 語句。
--no-data, -d 不導(dǎo)出任何數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)。
--no-set-names, -N 等同于--skip-set-charset
--opt 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 該選項(xiàng)默認(rèn)開啟, 可以用--skip-opt禁用.
--order-by-primary 如果存在主鍵，或者第一個(gè)唯一鍵，對(duì)每個(gè)表的記錄進(jìn)行排序。
--password, -p 連接數(shù)據(jù)庫密碼
--pipe(windows系統(tǒng)可用) 使用命名管道連接mysql
--port, -P 連接數(shù)據(jù)庫端口號(hào)
--protocol 使用的連接協(xié)議，包括：tcp, socket, pipe, memory.
--quick, -q 不緩沖查詢，直接導(dǎo)出到標(biāo)準(zhǔn)輸出。默認(rèn)為打開狀態(tài)，使用--skip-quick取消該選項(xiàng)。
--quote-names,-Q 使用(`)引起表和列名。默認(rèn)為打開狀態(tài)，使用--skip-quote-names取消該選項(xiàng)。
--replace 使用REPLACE INTO 取代INSERT INTO.
--result-file, -r 直接輸出到指定文件中。該選項(xiàng)應(yīng)該用在使用回車換行對(duì)（\\r\\n）換行的系統(tǒng)上（例如：DOS，Windows）。該選項(xiàng)確保只有一行被使用。
--routines, -R 導(dǎo)出存儲(chǔ)過程以及自定義函數(shù)。
--set-charset 添加'SET NAMES default_character_set'到輸出文件。默認(rèn)為打開狀態(tài)，使用--skip-set-charset關(guān)閉選項(xiàng)。
--single-transaction 該選項(xiàng)在導(dǎo)出數(shù)據(jù)之前提交一個(gè)BEGIN SQL語句，BEGIN 不會(huì)阻塞任何應(yīng)用程序且能保證導(dǎo)出時(shí)數(shù)據(jù)庫的一致性狀態(tài)。它只適用于多版本存儲(chǔ)引擎，僅InnoDB。本選項(xiàng)和--lock-tables 選項(xiàng)是互斥的，因?yàn)長(zhǎng)OCK TABLES 會(huì)使任何掛起的事務(wù)隱含提交。要想導(dǎo)出大表的話，應(yīng)結(jié)合使用--quick 選項(xiàng)。
--dump-date 將導(dǎo)出時(shí)間添加到輸出文件中。默認(rèn)為打開狀態(tài)，使用--skip-dump-date關(guān)閉選項(xiàng)。
--skip-opt 禁用–opt選項(xiàng).
--socket,-S 指定連接mysql的socket文件位置，默認(rèn)路徑/tmp/mysql.sock
--tab,-T 為每個(gè)表在給定路徑創(chuàng)建tab分割的文本文件。注意：僅僅用于mysqldump和mysqld服務(wù)器運(yùn)行在相同機(jī)器上。
--tables ，定需要導(dǎo)出的表名，需要配合--databases (-B)參數(shù)一起使用
--triggers 導(dǎo)出觸發(fā)器。該選項(xiàng)默認(rèn)啟用，用--skip-triggers禁用它。
--tz-utc 在導(dǎo)出頂部設(shè)置時(shí)區(qū)TIME_ZONE='+00:00' ，以保證在不同時(shí)區(qū)導(dǎo)出的TIMESTAMP 數(shù)據(jù)或者數(shù)據(jù)被移動(dòng)其他時(shí)區(qū)時(shí)的正確性。
--user, -u 指定連接的用戶名。
--verbose, --v 輸出多種平臺(tái)信息。
--version, -V 輸出mysqldump版本信息并退出
--where, -w 只轉(zhuǎn)儲(chǔ)給定的WHERE條件選擇的記錄。請(qǐng)注意如果條件包含命令解釋符專用空格或字符，一定要將條件引用起來。
--xml, -X 導(dǎo)出XML格式.
--plugin_dir 客戶端插件的目錄，用于兼容不同的插件版本。
--default_auth 客戶端插件默認(rèn)使用權(quán)限。
2.3.最佳實(shí)踐
導(dǎo)出表結(jié)構(gòu)
mysqldump --no-data --trigger=false mysql > mysql_01.sql
導(dǎo)出存儲(chǔ)過程
mysqldump -f -Rtdn --trigger=false mysql > mysql_02.sql
導(dǎo)出觸發(fā)器
mysqldump -f -tdn --trigger mysql > mysql_03.sql
導(dǎo)出事件
mysqldump -f -Etdn --trigger=false mysql > mysql_04.sql
導(dǎo)出數(shù)據(jù)
mysqldump -f --single-transaction --trigger=false -t mysql > mysql_05.sql
基于條件導(dǎo)出
mysqldump -t --single-transaction --where='cname="'jreey'"' tydb tab01 > /tmp/tab01.sql
導(dǎo)出某張表
mysqldump -S /data1/db3316/my3316.sock -pxxxxx --default-character-set=utf8 --single-transaction --set-gtid-purged=off --databases intelligent_os --tables beacon_base_pos > /tmp/beacon_base_pos.sql
實(shí)例一：數(shù)據(jù)庫邏輯導(dǎo)出于恢復(fù)
備份一個(gè)數(shù)據(jù)庫
mysqldump -S /data1/db3307/my3307.sock --default-character-set=utf8 --master-data=2 --no-create-db --single-transaction --set-gtid-purged=off -q --extended-insert --databases zabbix > /data1/mysqlbackup/m3307-20170306-dump.sql
恢復(fù)
mysql -S /data1/db3307/my3307.sock -e "create database zabbix"
mysql -S /data1/db3307/my3307.sock < /data1/mysqlbackup/m3307-20170306-dump.sql
補(bǔ)充：導(dǎo)出導(dǎo)入平面文件”
select * INTO OUTFILE '/tmp/errorlog.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from error_log;
load data infile '/tmp/errorlog.txt' into table test.error_log FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
3.Xtrabackup
xtrabackup是Percona公司CTO Vadim參與開發(fā)的一款基于InnoDB的在線熱備工具，具有開源，免費(fèi)，支持在線熱備，備份恢復(fù)速度快，占用磁盤空間小等特點(diǎn)，并且支持不同情況下的多種備份形式。（該產(chǎn)品的出現(xiàn)解決了業(yè)界內(nèi)MySQL社區(qū)版本中物理備份的空白）xtrabackup的官方下載地址為http://www.percona.com/software/percona-xtrabackup。
Xtrabackup是由percona提供的mysql數(shù)據(jù)庫備份工具，據(jù)官方介紹，這也是世界上惟一一款開源的能夠?qū)nnodb和xtradb數(shù)據(jù)庫進(jìn)行熱備的工具。特點(diǎn)：
- 備份過程快速、可靠；
- 備份過程不會(huì)打斷正在執(zhí)行的事務(wù)；
- 能夠基于壓縮等功能節(jié)約磁盤空間和流量；
- 自動(dòng)實(shí)現(xiàn)備份檢驗(yàn)；
- 還原速度快；
xtrabackup包含兩個(gè)主要的工具，即xtrabackup和innobackupex：
- xtrabackup只能備份innodb和xtradb兩種引擎的表，而不能備份myisam引擎的表；
- innobackupex是一個(gè)封裝了xtrabackup的Perl腳本，支持同時(shí)備份innodb和myisam，但在對(duì)myisam備份時(shí)需要加一個(gè)全局的讀鎖。還有就是myisam不支持增量備份。
3.1.Xtrabackup原理
3.1.1.全備份|恢復(fù)原理
- 備份原理
1.備份開始前開啟一個(gè)后臺(tái)檢測(cè)進(jìn)程，實(shí)時(shí)檢測(cè)mysql redo的變化寫入到xtrabackup_logfile文件
2.拷貝innodb engine涉及的ibd ibdata1數(shù)據(jù)文件(拷貝的單位是page,而不是文件)
3.數(shù)據(jù)庫級(jí)只讀鎖(FLUSH TABLES WITH READ LOCK)
4.拷貝除innodb enging之外的數(shù)據(jù)文件(多數(shù)指MyIASM的表)
5.獲取當(dāng)前數(shù)據(jù)庫binary log position
6.釋放數(shù)據(jù)庫級(jí)只讀鎖(釋放全局級(jí)鎖)
7.等待redo變化寫入xtrabackup_logfile并關(guān)閉Xtrbackup進(jìn)程
建議: xtrabackup工具最好只備份innodb engine的表,因?yàn)檫@樣,就會(huì)節(jié)省掉步驟四的物理copy時(shí)間,從而大大減少,數(shù)據(jù)庫鎖的時(shí)間.
- 恢復(fù)原理
1.Xtrabackup內(nèi)嵌的innodb實(shí)例，回放xtrabackup_log日志至備份集
2.將提交的事務(wù)信息變更應(yīng)用到innodb數(shù)據(jù)/表空間，同時(shí)回滾未提交的事務(wù)(這一過程類似innodb的實(shí)例恢復(fù)）。
3.根據(jù)配置文件(my.cnf)恢復(fù)數(shù)據(jù)文件、日志文件到指定路徑
3.1.2.增量備份|恢復(fù)原理
- 備份原理
"增量"備份的過程主要是通過拷貝innodb中有變更的"頁"（這些變更的數(shù)據(jù)頁指的是"頁"的LSN大于xtrabackup_checkpoints中給定的LSN）。增量備份是基于全備的，第一次增備的數(shù)據(jù)必須要基于上一次的全備，之后的每次增備都是基于上一次的增備，最終達(dá)到一致性的增備。
1.備份開始前開啟一個(gè)后臺(tái)檢測(cè)進(jìn)程，實(shí)時(shí)檢測(cè)mysql redo的變化寫入到xtrabackup_logfile文件
2.拷貝上次全備份以后LSN變化的數(shù)據(jù)
3.數(shù)據(jù)庫級(jí)只讀鎖(FLUSH TABLES WITH READ LOCK)
4.拷貝除innodb enging之外的數(shù)據(jù)文件(多數(shù)指MyIASM的表)
5.獲取當(dāng)前數(shù)據(jù)庫binary log position
6.釋放數(shù)據(jù)庫級(jí)只讀鎖(釋放全局級(jí)鎖)
7.等待redo變化寫入xtrabackup_logfile并關(guān)閉Xtrbackup進(jìn)程
注意：innobackupex增量備份過程中的"增量"處理，其實(shí)主要是相對(duì)innodb而言，對(duì)myisam和其他存儲(chǔ)引擎而言，它仍然是全拷貝(全備份)
- 恢復(fù)原理
1.全備份以只"重做"的方式回放xtrabackup_log日志至備份集(為后面增量數(shù)據(jù)的合并做基礎(chǔ))
2.依次合并增量備份至完整備份集(此時(shí)的merge可以commit、rollback)
3.Xtrabackup內(nèi)嵌的innodb實(shí)例，回放xtrabackup_log日志至備份集
4.將提交的事務(wù)信息變更應(yīng)用到innodb數(shù)據(jù)/表空間，同時(shí)回滾未提交的事務(wù)(這一過程類似innodb的實(shí)例恢復(fù)）。
5.根據(jù)配置文件(my.cnf)恢復(fù)數(shù)據(jù)文件、日志文件到指定路徑
和全備恢復(fù)類似，也需要兩步，一是數(shù)據(jù)文件的恢復(fù)，如圖1，這里的數(shù)據(jù)來源由3部分組成：全備份，增量備份和xtrabackup log。圖2是對(duì)未提交事務(wù)的回滾
- 如何選擇備份集
3.2.文件介紹
backup-my.cnf
備份集的配置選項(xiàng)文件xtrabackup_binlog_info
當(dāng)前正在使用的二進(jìn)制日志文件及至備份的位置。xtrabackup_binlog_pos_innodb
包含二進(jìn)制日志文件及用于InnoDB或XtraDB表的二進(jìn)制日志文件的當(dāng)前positionxtrabackup_checkpoints
備份類型（如完全或增量）、備份狀態(tài)（如是否已經(jīng)為prepared狀態(tài)）和LSN(日志序列號(hào))范圍信息；xtrabackup_info
備份集概要xtrabackup_logfile
備份過程中記錄redo變化的日志文件
3.3.相關(guān)參數(shù)
--defaults-file=# 該選項(xiàng)傳遞給xtrabackup子進(jìn)程，從指定文件讀取缺省選項(xiàng)
--apply-log 從備份恢復(fù)
--redo-only 選項(xiàng)用于準(zhǔn)備全庫備份和合并處最有一個(gè)備份外的所有增量備份。該選項(xiàng)強(qiáng)制跳過rollback階段，只進(jìn)行redo。這是有必要使用的，如果備份后，要使用增量改變的。
--copy-back 從備份目錄拷貝數(shù)據(jù)和索引文件到datadir目錄
--move-back 移動(dòng)之前的所有備份從一個(gè)備份目錄到他們的原始位置
--incremental 建立一個(gè)增量備份，傳遞給xtrabackup的子進(jìn)程。該參數(shù)可以和參數(shù)--incremental-lsn or --incremental-basedir配合使用。
--incremental-basedir=DIRECTORY 指定一個(gè)包換全庫備份的目錄作為增量備份的基礎(chǔ)數(shù)據(jù)庫。
--incremental-dir=DIRECTORY 指定增量備份與全庫備份合并去建立一個(gè)新的全備份的目錄。
--remote-host=HOSTNAME 備份到遠(yuǎn)程主機(jī)上，使用ssh
--stream=[tar|cpio(notimplemented)] 指定備份標(biāo)準(zhǔn)輸出格式
--tmpdir=DIRECTORY 默認(rèn)與tmpdir相同。使用—remote-host或—stream參數(shù)后，傳輸日志文件將存放在臨時(shí)目錄下
--use-memory=MB 傳遞給xtrabackup子進(jìn)程?；謴?fù)使用內(nèi)存大小
--parallel=NUMBER-OF-THREADS 選項(xiàng)傳遞給xtrabackup子進(jìn)程，指定數(shù)據(jù)傳輸線程總數(shù)。默認(rèn)為1
--compress[=LEVEL] 選項(xiàng)傳遞給xtrabackup子進(jìn)程。壓縮級(jí)別在0-9.1快速壓縮，9最佳壓縮，0不壓縮。默認(rèn)為1.
--compress-threads[=NUMBER] 壓縮時(shí)的并行線程總數(shù)，一般來說要和--parallel保持一致
--throttle=iOS 選項(xiàng)傳遞給xtrabackup子進(jìn)程，限制IO線程數(shù)量
--sleep=MS 選項(xiàng)傳遞給xtrabackup子進(jìn)程。每拷貝1MB數(shù)據(jù)暫停多少M(fèi)S時(shí)間
--include=REGEXP 選項(xiàng)傳遞給xtrabackup子進(jìn)程。使用正則進(jìn)行匹配
--databases=LIST 指定備份數(shù)據(jù)庫
--tables-file=FILE 指定需要備份的表
--uncompress 選項(xiàng)傳遞給xtrabackup子進(jìn)程。對(duì)壓縮過的InnoDB數(shù)據(jù)文件不進(jìn)行壓縮
--export 僅使用于prepare選項(xiàng)。選項(xiàng)傳遞給xtrabackup子進(jìn)程。
--slave-info 備份復(fù)制從服務(wù)端，主從信息記錄在ibbackup_slave_info文件中
--no-timestamp 不在備份根目錄下創(chuàng)建以當(dāng)前時(shí)間戳為名稱的新的備份目錄
--ibbackup=IBBACKUP-BINARYibbackup 二進(jìn)制路徑
--no-lock 禁止表級(jí)鎖。全部是InnoDB引擎表和不關(guān)系二進(jìn)制日志位置下使用
--scpopt=SCP-OPTIONS 指定scp參數(shù)
3.4.最佳實(shí)踐
xtrabackup官方網(wǎng)站：https://www.percona.com/
xtrabackup下載站點(diǎn)：https://www.percona.com/downloads/XtraBackup/LATEST/
3.4.1.完全備份恢復(fù)
- 備份數(shù)據(jù)庫
innobackupex --defaults-file=/etc/mysql/my10059.cnf -S /var/wd/db10059/my10059.sock -p 123 --use-memory=256M --no-timestamp /data1/mysqlbackup/xtra_full_135156_10059
- 恢復(fù)數(shù)據(jù)庫
innobackupex --defaults-file=/data1/mysqlbackup/xtra_full_135156_10059/backup-my.cnf --apply-log /data1/mysqlbackup/xtra_full_135156_10059
innobackupex --defaults-file=/etc/mysql/my10059.cnf --move-back /data1/mysqlbackup/xtra_full_135156_10059
chown -R mysql:mysql /var/wd/db10059/
3.4.2.增量備份恢復(fù)
- 備份數(shù)據(jù)庫
- 全備份
innobackupex --defaults-file=/etc/my.cnf -u root -S /data1/db3306/my3306.sock -p123 --no-timestamp /data1/xtra_full_168160_3306
- 增量備份
innobackupex --defaults-file=/etc/my.cnf -S /data1/db3306/my3306.sock -p 123 --no-timestamp --incremental /data1/xtra_incr_168160_3306 --incremental-basedir=/data1/xtra_full_168160_3306
- 恢復(fù)數(shù)據(jù)庫
- 全備份只"重做"回放xtrabackup log
innobackupex --apply-log --redo-only /data1/xtra_full_168160_3306
- merge增量備份集(如果有多個(gè)，順序merge)
innobackupex --apply-log --redo-only xtra_full_168160_3306 --incremental-dir=xtra_incr_168160_3306
- 完整的回放xtrabackup log
innobackupex --defaults-file=/data1/xtra_full_168160_3306/backup-my.cnf --apply-log /data1/xtra_incr_168160_3306
innobackupex --defaults-file=/etc/my.cnf --move-back xtra_full_168160_3306
- 通過binlog恢復(fù)誤刪除數(shù)據(jù)
mysqlbinlog --start-position=423234035 16703402-bin.000561 | mysql -S /data1/db3402/my3402.sock -pxxx
mysqlbinlog 16703402-bin.000562 16703402-bin.000563 16703402-bin.000564 16703402-bin.000565 16703402-bin.000566 | mysql -S /data1/db3402/my3402.sock -pxxx
mysqlbinlog 16703402-bin.000567 16703402-bin.000568 16703402-bin.000569 16703402-bin.000570 16703402-bin.000571 16703402-bin.000572 | mysql -S /data1/db3402/my3402.sock -pxxx
mysqlbinlog 16703402-bin.000573 16703402-bin.000574 16703402-bin.000575 16703402-bin.000576 16703402-bin.000577 | mysql -S /data1/db3402/my3402.sock -pxxx
mysqlbinlog --stop-position=657188280 16703402-bin.000578 | mysql -S /data1/db3402/my3402.sock -pxxxx
3.4.3.備份新特性
-
"流"方式壓縮備份
Xtrabackup對(duì)備份的數(shù)據(jù)文件支持“流”功能，即可以將備份的數(shù)據(jù)通過STDOUT傳輸給tar程序進(jìn)行歸檔，而不是默認(rèn)的直接保存至某備份目錄中。要使用此功能，僅需要使用--stream選項(xiàng)即可。
innobackupex --defaults-file=/etc/mysql/my10059.cnf -p 123 --use-memory=256M --no-timestamp --stream=tar /data1/mysqlbackup/xtra_full_gzip135156_10059 | gzip > /data1/mysqlbackup/xtra_full_gzip135156_10059.tar.gz
- "innodb page"方式壓縮備份
innobackupex --defaults-file=/etc/mysql/my10059.cnf -S /var/wd/db10059/my10059.sock -p 123 --compress --use-memory=256M --no-timestamp /data1/mysqlbackup/xtra_full_135156_10059
- 部分備份
innobackupex --defaults-file=/etc/mysql/my10059.cnf -S /var/wd/db10059/my10059.sock -p 123 --databases='sys performance_schema mysql custom_db' --use-memory=256M --no-timestamp /data1/mysqlbackup/xtra_full_135156_10059
- 并行備份
innobackupex --defaults-file=/etc/mysql/my10059.cnf -S /var/wd/db10059/my10059.sock -p 123 --parallel=6 --use-memory=256M --no-timestamp /data1/mysqlbackup/xtra_full_135156_10059
4.mydumper
mydumper（&myloader）是用于對(duì)MySQL數(shù)據(jù)庫進(jìn)行多線程備份和恢復(fù)的開源 (GNU GPLv3)工具。開發(fā)人員主要來自MySQL、Facebook和SkySQL公司，目前由Percona公司開發(fā)和維護(hù)，是 Percona Remote DBA 項(xiàng)目的重要組成部分，包含在Percona XtraDB Cluster中。mydumper的第一版0.1發(fā)布于2010.3.26，最新版本0.9.1發(fā)布于2015.11.06。
Mydumper主要特性：
- 開源 (GNU GPLv3)
- 輕量級(jí)C語言寫的
- 快速的文件壓縮
- 支持導(dǎo)出binlog
- 執(zhí)行速度比mysqldump快10倍
- 多線程恢復(fù)(適用于0.2.1以上版本)
- 事務(wù)性和非事務(wù)性表一致的快照(適用于0.2.2以上版本)
- 以守護(hù)進(jìn)程的工作方式，定時(shí)快照和連續(xù)二進(jìn)制日志(適用于0.5.0以上版本)
4.1.mydumper原理
mydumper作為一個(gè)實(shí)用工具，能夠良好支持多線程工作，可以并行的多線程的從表中讀入數(shù)據(jù)并同時(shí)寫到不同的文件里，這使得它在處理速度方面快于傳統(tǒng)的mysqldump N倍。
原理分析
mydumper的工作過程：
- 主線程對(duì)備份實(shí)例加讀鎖，阻塞寫操作以建立一致性數(shù)據(jù)備份快照(FLUSH TABLES WITH READ LOCK)
- 讀取當(dāng)前時(shí)間點(diǎn)的二進(jìn)制日志文件名和日志寫入的位置并記錄在metadata文件中，以供即使點(diǎn)恢復(fù)使用
- 創(chuàng)建工作線程，初始化備份任務(wù)隊(duì)列，并向隊(duì)列中推送數(shù)據(jù)庫元數(shù)據(jù)（schema）、非InnoDB表和InnoDB表的備份任務(wù)；
- 工作線程負(fù)責(zé)將備份任務(wù)隊(duì)列中的任務(wù)按順序取出并完成備份(dump線程數(shù)可以指定，默認(rèn)是4)
- 分別建立與備份實(shí)例連接，將session的事務(wù)級(jí)別設(shè)置為repeatable-read，用于實(shí)現(xiàn)可重復(fù)讀；
- 在主線程仍持有全局讀鎖時(shí)開啟事務(wù)進(jìn)行快照讀，這樣保證了讀到的一致性數(shù)據(jù)與主線程相同，實(shí)現(xiàn)了備份數(shù)據(jù)的一致性；
- 按序從備份任務(wù)隊(duì)列中取出備份任務(wù)，工作線程先進(jìn)行MyISAM等非InnoDB表備份
- 非事物引擎?zhèn)浞萃旰?，主線程釋放全局只讀鎖(UNLOCK TABLES)
- 完成InnoDB表備份，事務(wù)結(jié)束，關(guān)閉鏈接
表數(shù)據(jù)拆分方式如下所述：
- mydumper優(yōu)先選擇主鍵索引的第一列作為chunk劃分字段
- 若不存在主鍵索引，則選擇第一個(gè)唯一索引作為劃分依據(jù)
- 若還不存在，則選擇區(qū)分度（Cardinality）最高的任意索引。
- 如果還是無法滿足，則只能進(jìn)行表級(jí)的并行備份
范圍拆分方式：
- 在確定了chunk劃分字段后，先獲取該字段的最大和最小值
- 再通過執(zhí)行“explain select field from db.table”來估計(jì)該表的記錄數(shù)
- 最后根據(jù)所設(shè)的每個(gè)任務(wù)（文件）記錄數(shù)來將該表劃分為多個(gè)chunk。
備份所生成的文件
目錄中包含一個(gè)metadata文件：
- 記錄了備份數(shù)據(jù)庫在備份時(shí)間點(diǎn)的二進(jìn)制日志文件名，日志的寫入位置
- 如果是在從庫進(jìn)行備份，還會(huì)記錄備份時(shí)同步至主庫的二進(jìn)制日志文件及寫入位置
每個(gè)表有兩個(gè)備份文件：
- database.table-schema.sql 表結(jié)構(gòu)文件
- database.table.sql 表數(shù)據(jù)文件
- 如果對(duì)表文件分片，將生成多個(gè)備份數(shù)據(jù)文件，可以指定行數(shù)或指定大小分片
注意：mydumper導(dǎo)出時(shí)不寫binlog，復(fù)制環(huán)境中需要注意。
4.2.相關(guān)參數(shù)
- mydumper參數(shù)介紹：
-B, --database 需要備份的庫
-T, --tables-list 需要備份的表，用，分隔
-o, --outputdir 輸出目錄
-s, --statement-size Attempted size of INSERT statement in bytes, default 1000000
-r, --rows 試圖分裂成很多行塊表
-c, --compress 壓縮輸出文件
-e, --build-empty-files 即使表沒有數(shù)據(jù)，還是產(chǎn)生一個(gè)空文件
-x, --regex 支持正則表達(dá)式
-i, --ignore-engines 忽略的存儲(chǔ)引擎，用，分隔
-m, --no-schemas 不導(dǎo)出表結(jié)構(gòu)
-k, --no-locks 不執(zhí)行臨時(shí)共享讀鎖 警告：這將導(dǎo)致不一致的備份
-l, --long-query-guard 長(zhǎng)查詢，默認(rèn)60s
--kill-long-queries kill掉長(zhǎng)時(shí)間執(zhí)行的查詢(instead of aborting)
-D, --daemon 啟用守護(hù)進(jìn)程模式
-I, --snapshot-interval dump快照間隔時(shí)間，默認(rèn)60s，需要在daemon模式下
-L, --logfile 日志文件
-h, --host
-u, --user
-p, --password
-P, --port
-S, --socket
-t, --threads 使用的線程數(shù)，默認(rèn)4
-C, --compress-protocol 在mysql連接上使用壓縮
-V, --version
-v, --verbose 更多輸出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
- myloader參數(shù)介紹：
-d, --directory 導(dǎo)入備份目錄
-q, --queries-per-transaction 每次執(zhí)行的查詢數(shù)量, 默認(rèn)1000
-o, --overwrite-tables 如果表存在刪除表
-B, --database 需要還原的庫
-e, --enable-binlog 啟用二進(jìn)制恢復(fù)數(shù)據(jù)
-h, --host
-u, --user
-p, --password
-P, --port
-S, --socket
-t, --threads 使用的線程數(shù)量，默認(rèn)4
-C, --compress-protocol 連接上使用壓縮
-V, --version
-v, --verbose 更多輸出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
4.3.最佳實(shí)踐
yum install gcc gcc-c++ make cmake glib2-devel zlib-devel pcre-devel openssl-devel mysql-devel
cd mydumper-0.9.1
cmake .
make
cp -p mydumper /usr/local/bin
cp -p myloader /usr/local/bin
ldd mydumper
- 備份數(shù)據(jù)庫
mydumper -S /data1/db3307/my3307.sock -u root --tz-utc --threads 32 -o /data1/mysqlbackup/full_3307_mydumper
- 恢復(fù)數(shù)據(jù)庫
myloader -S /data1/db3307/my3307.sock -u root --threads 32 -o -d /data1/mysqlbackup/full_3307_mydumper
- 常見錯(cuò)誤
** (mydumper:142452): WARNING **: Broken table detected, please review: wanda.view_s06_ffan_mem_reg_cnt_minute
** (mydumper:142452): WARNING **: Broken table detected, please review: wanda.view_s06_ffan_wifi_mobile_to_plaza_uniq_real_time
** (mydumper:142452): CRITICAL **: Could not read data from imc.MSG_BLACK_BATCH: Table definition has changed, please retry transaction
** (mydumper:142452): CRITICAL **: Error dumping table (scm_db.gerritapp_plan_jenkins) data: Table 'scm_db.gerritapp_plan_jenkins' doesn't exist
** (mydumper:142452): CRITICAL **: Error dumping schemas (wanda.view_s06_ffan_mem_reg_cnt_hour): SELECT command denied to user ''@'%' for column 'plaza_id' in table 's06_ffan_mem_reg_real_time'
** (mydumper:142452): CRITICAL **: Error dumping schemas (wanda.view_s06_ffan_mem_reg_info_uniq_real_time): SELECT command denied to user ''@'%' for column 'mobile' in table 's06_ffan_mem_reg_info_real_time'
** (myloader:1408769): CRITICAL **: Error restoring invoice.INVOICE_STOREINFO from file invoice.INVOICE_STOREINFO-schema.sql: Invalid default value for 'PAUSE_TIME'
** (myloader:1408769): CRITICAL **: Error restoring invoice.INVOICE_TASK from file invoice.INVOICE_TASK-schema.sql: Invalid default value for 'INVOICE_START_TIME'
** (myloader:1408769): CRITICAL **: Error restoring pubsub.logs from file pubsub.logs-schema.sql: Invalid default value for 'operatorTime'
出現(xiàn)問題的View
CREATE ALGORITHM=UNDEFINED DEFINER=`mycat`@`10.%` SQL SECURITY DEFINER VIEW `view_s06_ffan_mem_reg_cnt_hour` AS select `mycat`.`s06_ffan_mem_reg_real_time`.`plaza_id` AS `plaza_id`,date_format(`mycat`.`s06_ffan_mem_reg_real_time`.`create_time`,'%Y-%m-%d %H') AS `reg_time`,count(1) AS `total`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'app') then 1 end)) AS `app`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'web') then 1 end)) AS `web`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'html5') then 1 end)) AS `html5`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'wifi') then 1 end)) AS `wifi`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'card') then 1 end)) AS `card`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'sms') then 1 end)) AS `sms`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'thrid') then 1 end)) AS `thrid`,count((case when (`mycat`.`s06_ffan_mem_reg_real_time`.`channel` = 'offline') then 1 end)) AS `offline` from `s06_ffan_mem_reg_real_time` where (`mycat`.`s06_ffan_mem_reg_real_time`.`create_time` >= '2015-12-08') group by `mycat`.`s06_ffan_mem_reg_real_time`.`plaza_id`,`reg_time`;
- 案例分享
1.8T數(shù)據(jù)庫服務(wù)器，32核，希捷pci-E SSD卡
導(dǎo)出時(shí)間25分鐘(意不意外、驚不驚喜、刺不刺激)
導(dǎo)入時(shí)間5小時(shí)15分鐘
掃描下方二維碼關(guān)注本人微信號(hào)！歡迎大家交流學(xué)習(xí)！
