MySQL備份理論及mysqldump用法

為什么備份：

• 災(zāi)難恢復(fù)：硬件故障（冗余）、軟件故障（bug）、自然災(zāi)害、******、誤操作、...
• 測(cè)試：測(cè)試時(shí)，為了模仿真實(shí)環(huán)境中用戶訪問情況，通常需要用真實(shí)數(shù)據(jù)去做測(cè)試。

備份恢復(fù)的原則：

1. 策略正確：平時(shí)要設(shè)計(jì)好備份還原所涉及到的人員，確保能做正確的事。
2. 執(zhí)行不出問題：平時(shí)做演練，以確保出現(xiàn)問題時(shí)，能做正確的事情。
3. 出問題時(shí)做正確的事情。

異地災(zāi)備

• 必要性：防止同一臺(tái)機(jī)器、同一個(gè)網(wǎng)絡(luò)環(huán)境中、同一個(gè)物理機(jī)房不可用導(dǎo)致服務(wù)不可用。
• 備份項(xiàng)：配置文件，周邊配置，周期性計(jì)劃任務(wù)。

備份注意事項(xiàng)：

1. 能容忍最多丟失多少數(shù)據(jù)：決定了使用的備份手段和工具
2. 恢復(fù)數(shù)據(jù)需要在多長(zhǎng)時(shí)間內(nèi)完成
   • 電商站點(diǎn)若發(fā)生故障，數(shù)據(jù)恢復(fù)時(shí)，一小時(shí)損失可能數(shù)以億計(jì)數(shù)據(jù)。若用二進(jìn)制文件恢復(fù)，可能恢復(fù)時(shí)長(zhǎng)極長(zhǎng)，且可能因業(yè)務(wù)量大，單條語(yǔ)句寫入二進(jìn)制文件順序不同導(dǎo)致數(shù)據(jù)與真實(shí)數(shù)據(jù)不一致。
3. 需要恢復(fù)哪些數(shù)據(jù)
   • 線上生產(chǎn)數(shù)據(jù)集，線上認(rèn)證，配置等
4. 數(shù)據(jù)備份后需要經(jīng)常測(cè)試備份的可用性，另一方面也可以增強(qiáng)恢復(fù)操作的效率，在真正需要恢復(fù)數(shù)據(jù)時(shí)做到有條不紊。

數(shù)據(jù)備份的類型

1. 根據(jù)備份的數(shù)據(jù)集的范圍可分為完全備份和部分備份

   • 完全備份：備份整個(gè)數(shù)據(jù)集
   • 部分備份：備份整個(gè)數(shù)據(jù)集中的一部分，如部分表。

2. 全量備份、增量備份、差異備份

   • 全量備份：備份全部數(shù)據(jù)
   • 增量備份：備份自上一次完全備份或增量備份以來(lái)變化的那部分?jǐn)?shù)據(jù)
   • 差異備份：僅備份自上一次完全備份以來(lái)變化的那部數(shù)據(jù)

3. 根據(jù)備份方式分為物理備份和邏輯備份

   • 物理備份：直接復(fù)制數(shù)據(jù)文件
   • 邏輯備份：從數(shù)據(jù)庫(kù)導(dǎo)出數(shù)據(jù)另存在一個(gè)或多個(gè)文件中
4. 根據(jù)數(shù)據(jù)服務(wù)是否在線可分為熱備、溫備、冷備
   • 熱備：讀寫操作均可進(jìn)行的狀態(tài)下所做的備份
   • 溫備：可讀但不可寫狀態(tài)下進(jìn)行的備份；
   • 冷備：讀寫操作均不可進(jìn)行的狀態(tài)下所做的備份；最可靠，但卻是最不可行的。可借助主從復(fù)制實(shí)現(xiàn)。

備份時(shí)需要考慮到的因素：

1. 要不要鎖定資源，鎖定資源多長(zhǎng)時(shí)間？
2. 備份過(guò)程的時(shí)長(zhǎng)？
3. 備份時(shí)的服務(wù)器負(fù)載？
4. 恢復(fù)過(guò)程的時(shí)長(zhǎng)？

備份工具

1. mysqldump

   • mysql服務(wù)自帶的備份工具,邏輯備份工具.
   • 可以做到完全、部分備份
   • 對(duì)InnoDB支持熱備，對(duì)MyISAM存儲(chǔ)引擎支持溫備。

2. cp/tar

   • lvm2：快照（請(qǐng)求一個(gè)全局鎖），之后立即釋放鎖，達(dá)到幾乎熱備的效果；物理備份；
     • 注意：不能僅備份數(shù)據(jù)文件；要同時(shí)備份事務(wù)日志；
     • 前提：要求數(shù)據(jù)文件和事務(wù)日志位于同一個(gè)邏輯卷；

3. Innobackup/xtrabackup

   • 由Percona提供，開源工具，支持對(duì)InnoDB做熱備，物理備份工具；
   • 支持完全備份、部分備份；完全備份、增量備份；完全備份、差異備份；
4. mysqlhotcopy：幾乎冷備
5. select：據(jù)說(shuō)相當(dāng)LOW
   • 備份：SELECT cluase INTO OUTFILE 'FILENAME';
   • 恢復(fù)：CREATE TABLE
   • 導(dǎo)入：LOAD DATA

mysqldump用法

1. 特點(diǎn)：支持邏輯備份、完全備份、部分備份；

   • 邏輯備份：數(shù)據(jù)服務(wù)要正常運(yùn)行，監(jiān)聽在某個(gè)端口，mysqldump可以通過(guò)端口訪問mysql。
   • 通過(guò)SELECT語(yǔ)句查詢后導(dǎo)出至一個(gè)文件，恢復(fù)時(shí)通過(guò)INSERT語(yǔ)句把所有查詢結(jié)果插入至數(shù)據(jù)庫(kù)。
   • 有可能會(huì)丟失精度

2. mysqldump用法：

   • SYNOPSIS：
     • mysqldump [OPTIONS] database [tables]
     • mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] 備份指定表
     • mysqldump [OPTIONS] --all-databases [OPTIONS] 備份所有表

   • OPTIONS

     • --all-databases 備份所有庫(kù)
     • --databases db1,db2.... 備份指定一到多個(gè)數(shù)據(jù)庫(kù)：
     • db 備份單個(gè)數(shù)據(jù)庫(kù)中的所有目標(biāo)對(duì)象（不會(huì)自動(dòng)創(chuàng)建庫(kù)，只是備份庫(kù)中的表、代碼、存儲(chǔ)過(guò)程、存儲(chǔ)函數(shù)、觸發(fā)器以及事件調(diào)度器等）
     • db table1，table2... 備份指定的表

     MyISAM存儲(chǔ)引擎：最多支持溫備，備份時(shí)要鎖定所有要備份表（若是備份哪個(gè)表鎖定哪個(gè)表，還原時(shí)各表時(shí)間點(diǎn)不一致）

     • -x, --lock-all-tables 鎖定所有庫(kù)的所有表，讀鎖
     • -l, --lock-tables 鎖定指定庫(kù)所有表

     InnoDB存儲(chǔ)引擎：支持溫備和熱備；

     • --single-transaction 創(chuàng)建一個(gè)事務(wù)，基于此快照?qǐng)?zhí)行備份；必須加上這個(gè)選項(xiàng)才支持熱備
       • 在REPEATABLE-READ隔離級(jí)別，mysqldump連進(jìn)數(shù)據(jù)庫(kù)時(shí)看到的視圖是一致的，都是啟動(dòng)事務(wù)那一刻的狀態(tài)。在這種情況下，啟動(dòng)一個(gè)事務(wù)，備份的所有數(shù)據(jù)都是這個(gè)時(shí)間點(diǎn)的。

     其它選項(xiàng)：

     • -R, --routines 備份指定庫(kù)的存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)；
     • --triggers 備份指定庫(kù)的觸發(fā)器
     • -E, --events 備份事件調(diào)度器
     • --master-data[=#] 在備份的目標(biāo)文件中記錄一個(gè)CHANGE MASTER TO語(yǔ)句，記錄備份的那一刻處于哪個(gè)二進(jìn)制文件的哪個(gè)位置。
       • mysqldump導(dǎo)出數(shù)據(jù)時(shí)，當(dāng)這個(gè)參數(shù)的值為1的時(shí)候，mysqldump出來(lái)的文件就會(huì)包括CHANGE MASTER TO這個(gè)語(yǔ)句，CHANGE MASTER TO后面緊接著就是file和position的記錄，在slave上導(dǎo)入數(shù)據(jù)時(shí)就會(huì)執(zhí)行這個(gè)語(yǔ)句，salve就會(huì)根據(jù)指定這個(gè)文件位置從master端復(fù)制binlog。默認(rèn)情況下這個(gè)值是1。
       • 記錄下來(lái)一個(gè)注釋的CHANGE MASTER TO 語(yǔ)句，僅為我們參考使用，不打算用它來(lái)設(shè)定從服務(wù)器復(fù)制線程的啟動(dòng)通常就設(shè)置為2。
     • --flush-logs 鎖定表完成后，即進(jìn)行日志刷新操作；