目錄
- 今日内容概述
- 今日内容詳細
- 1.資料備份簡述
- 2.備份什麼？
- 3.備份的類型
- 4.備份的工具
- 5.三種備份政策
- 6.備份實戰
- 使用cp進行備份
- 使用mysqldump+複制binary logs備份
- 參數
- 實體備份之Xtrabackup
- Xtrabackup可以做些做什麼？
- Xtrabackup工具支援
- XtraBackup備份有什麼優點
- Xtrabackup 安裝
- Xtrabackup 備份方式（實體備份）
- Xtrabackup 參數說明
- Xtrabackup全量備份與恢複
- 執行個體
- Xtrabackup增量備份與恢複
今日内容概述
1.資料備份簡述
2.備份什麼？
3.備份的類型
4.備份的工具
5.三種備份政策
6.備份實戰
今日内容詳細
1.資料備份簡述
為何要備份資料？
在生産環境中我們資料庫可能會遭遇各種各樣的不測進而導緻資料丢失，大概會有以下幾種情況：
硬體故障
軟體故障
自然災害
人為誤操作（占比最大）
在生産環境中，伺服器硬體壞了可以更換或者維修；軟體崩潰了可以修複或重新安裝，但是資料丢失了如果沒有備份的話就真的麼得了。
資料對于公司的重要程度不言而喻，生産環境中最重要的就是資料了，為了在資料丢失後我們能有機會恢複資料，我們就需要定期的備份資料，這也是運維三大職責之一。
# 之前我們有學習過InnoDB存儲引擎，它有自動的資料恢複功能，在執行一條寫操作并且commit成功時InnoDB存儲引擎會将新資料寫入redo log，如果此時資料庫挂掉，重新開機後依然可以依據redo log來恢複尚未執行完畢的資料，但是！我們這裡講的是資料誤删或者幹脆沒了，redo log可就沒法幫你了，它隻能幫我們恢複尚未完成的操作，已經完成的操作redo log是無法恢複的。是以為了防止資料意外丢失，我們還是要定期做好資料的備份工作。
2.備份什麼？
我們剛才提到了備份，那麼我們到底要備份些什麼呢？
# 一般情況下，我們需要備份下列資料
資料本身（庫，表，記錄）
二進制日志（binlog日志），InnoDB事務日志（redo log,undo log）
代碼（存儲過程、存儲函數、觸發器以及事件排程器）
伺服器的配置檔案
3.備份的類型
按照備份時資料庫的運作狀态，可以分為三種
1.冷備：停庫、停服務進行備份，即當資料庫進行備份時，資料庫不能進行讀寫操作，即資料庫需要下線 # 生産環境中一般不這樣進行備份
2.溫備：不停庫、不停服務進行備份，會（鎖表）阻止使用者的寫入，即當資料庫進行備份時，資料庫的讀操作可以執行，但是不能執行寫操作
3.熱備（建議）：不停庫、不停服務進行備份，也不會（鎖表）阻止使用者的寫入。即當資料庫進行備份時，資料庫的讀寫操作均不受影響。
# MySQL中進行不同類型的備份還要考慮存儲引擎是否支援
MyISAM:熱備不支援，支援冷備和溫備
InnoDB：熱備，溫備和冷備都支援
# 實體與邏輯
按照備份的内容分，可以分為兩種
1.實體備份：直接将底層實體檔案備份
2.邏輯備份：通過特定的工具從資料庫中到處SQL語句或者資料，可能會丢失資料精度
# 全量、差異、增量備份
按照每次備份的資料量，可以分為
1.全量備份/完全備份（Full Backup）：備份整個資料集（即整個資料庫）
2.部分備份：備份部分資料集（例如：隻備份一個表的變化）
部分備份又可以分為：差異備份和增量備份兩種
# 1.差異備份（Differential Backup）
每次備份時，都是基于第一次完全備份的内容，隻備份有差異的資料（新增的、修改的、删除的），例如
第一次備份：完全備份
第二次備份：以目前時間節點的資料為基礎，備份與第一次備份内容的差異
第三次備份：以目前時間節點的資料為基礎，備份與第一次備份内容的差異
...
# 2.增量備份（Incremental Backup ）
每次備份時，都是基于上一次備份的内容（這裡要注意是上一次，而不是第一次），隻備份有差異的資料（新增的、修改的、删除的），是以增量備份的結果是一條鍊，例如：
第一次備份：完全備份
第二次備份：以目前時間節點的資料為基礎，備份與第一次備份内容的差異
第三次備份：以目前時間節點的資料為基礎，備份與第二次備份内容的差異
...
# 上述三種備份方案要如何恢複資料？
全量備份的資料恢複
隻需找出指定時間點的那一個備份檔案即可，即隻需要找到一個檔案即可
差異備份的資料恢複
需要先恢複第一次全量備份的結果，然後再恢複最近一次差異備份的結果，即隻需要找到兩個檔案即可
增量備份的資料恢複
需要先恢複第一次備份的結果，然後在依次恢複每次的增量備份，直到恢複到目前的位置，即需要找到一條備份鍊
# 綜上，對比三種備份方案
1.占用空間：全量 > 差異 > 增量
2.恢複資料的複雜程度：增量 > 差異 > 全量
4.備份的工具
| 備份工具 | 備份速度 | 恢複速度 | 便捷性 | 适用存儲引擎 | 支援的備份類型 | 功能 | 應用場景 |
| cp、tar等（實體） | 快 | 一般 | 所有 | 冷備、全量、差異、增量 | 很弱 | 少量資料備份 | |
| lvm2快照（實體） | 支援幾乎熱備（即差不多是熱備，哈哈），是借助檔案系統管理工具進行的備份 | 中小型資料量的備份 | |||||
| xtrabackup（實體） | 較快 | 是一款非常強大的熱備工具 | 由percona提供，隻支援InnoDB/XtraDB | 熱備、全量、差異、增量 | 強大 | 較大規模的備份 | |
| mysqldump（邏輯） | 慢 | 支援溫備、完全備份、部分備份、對于InnoDB存儲引擎支援熱備 |
如果考慮到增量備份，還需要結合binlog日志（binlog隻屬于增量恢複），需要用到工具mysqlbinlog,相當于邏輯備份的一種。
5.三種備份政策
針對不同的場景，我們需制定不同的備份政策對資料庫進行備份，一般情況下，備份政策應為以下三種
- 直接cp、tar複制資料庫的檔案
- mysqldump+複制bin logs
- lvm2快照+複制bin logs
- xtrabackup
1.如果資料量較小，可以使用第一種方式，直接複制資料庫檔案
2.如果資料量有一定的規模，可以采用第二種方式，先使用mysqldump對資料庫進行完全備份，然後定期備份binary log達到增量備份的效果
3.如果資料量一般，而又不過分影響業務運作，可以使用第三種方式，使用lvm2的快照對資料檔案進行備份，而後定期備份binary log 達到增量備份的效果。
4.如果資料量很大，而又不過分影響業務運作，可以使用第四種方式，使用xtrabackup進行完全備份後，定期使用xtrabackup進行增量備份或差異備份
6.備份實戰
使用cp進行備份
備份步驟
# 1.向所有表施加讀鎖
flush tables with readd lock;
# 2.備份資料檔案
mkdir /mysql_backup
tar -czf mysql_backup.tar.gz /mysql_data/*
mv mysql_backup.tar.gz /mysql_backup
使用mysqldump+複制binary logs備份
mysqldump指令
# 文法
mysqldump -h 伺服器 -u使用者名 -p密碼 選項與參數 > 備份檔案.sql
# 選項
-A/-- all-databasses # 所有庫
-B/--databases db1 db2 # 多個資料庫
db1 # 資料庫名
db1 t1 t2 # db1資料庫的表t1、t2
-F # 備份的同時重新整理binlog
-R # 備份存儲過程和函數資料（有寫就備份，沒有就不備份）
--triggers # 備份觸發器資料（現在都是開發寫觸發器）
-E/--events # 備份事件排程器
-d # 僅表結構
-t # 僅資料
# 完整的語句
mysqldump -uroot -p -A -E -R --triggers > /mysql_backup/MySQL_`date +"%F".sql`
Enter password:
有時檔案太大可以壓縮gzip，但gzip不屬于mysql獨有的指令，是以我們需要管道符
mysqldump -uroot -p -A -E -R --triggers | gzip > /mysql_backup/MySQL_`date +"%F".sql.gz`
Enter password:
導出資料的時候壓縮了，導入的時候就需要解壓，可以使用zcat指令
zcat /mysql_backup/MySQL_2021-10-16.sql.gz | mysql -uroot -p
Enter password:
參數
| 解釋 | |
| -A --all-databases | 導出全部資料庫 |
| -Y --all-tablespaces | 導出全部表空間 |
| --add-drop-database | 每個資料庫建立之前添加drop資料庫語句。 |
| --add-drop-table | 每個資料表建立之前添加drop資料表語句。(預設為打開狀态，使用--skip-add-drop-table取消選項) |
| --add-locks | 在每個表導出之前增加LOCK TABLES并且之後UNLOCK TABLE。(預設為打開狀态，使用--skip-add-locks取消選項) |
| --comments | 附加注釋資訊。預設為打開，可以用--skip-comments取消 |
| --compact | 導出更少的輸出資訊(用于調試)。去掉注釋和頭尾等結構。可以使用選項：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys |
| -c --complete-insert | 使用完整的insert語句(包含列名稱)。這麼做能提高插入效率，但是可能會受到max_allowed_packet參數的影響而導緻插入失敗。 |
| -C --compress | 在用戶端和伺服器之間啟用壓縮傳遞所有資訊 |
| -B --databases | 導出幾個資料庫。參數後面所有名字參量都被看作資料庫名。 |
| --debug | 輸出debug資訊，用于調試。 |
| --debug-info | 輸出調試資訊并退出 |
| --default-character-set | 設定預設字元集，預設值為utf8 |
| --delayed-insert | 采用延時插入方式（INSERT DELAYED）導出資料 |
| -E --events | 導出事件。 |
| --master-data | 在備份檔案中寫入備份時的binlog檔案。值為1時，binlog檔案名和位置沒有注釋，為2時，則在備份檔案中将binlog的檔案名和位置進行注釋。 |
| --flush-logs | 開始導出之前重新整理日志。請注意：假如一次導出多個資料庫(使用選項--databases或者--all-databases)，将會逐個資料庫重新整理日志。除使用--lock-all-tables或者--master-data外。在這種情況下，日志将會被重新整理一次，相應的是以表同時被鎖定。是以，如果打算同時導出和重新整理日志應該使用--lock-all-tables 或者--master-data 和--flush-logs。 |
| --flush-privileges | 在導出mysql資料庫之後，發出一條FLUSH PRIVILEGES 語句。為了正确恢複，該選項應該用于導出mysql資料庫和依賴mysql資料庫資料的任何時候。 |
| --force | 在導出過程中忽略出現的SQL錯誤。 |
| -h --host | 需要導出的主機資訊 |
| --ignore-table | 不導出指定表。指定忽略多個表時，需要重複多次，每次一個表。每個表必須同時指定資料庫和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 …… |
| -x --lock-all-tables | 送出請求鎖定所有資料庫中的所有表，以保證資料的一緻性。這是一個全局讀鎖，并且自動關閉--single-transaction 和--lock-tables 選項。 |
| -l --lock-tables | 開始導出前，鎖定所有表。用READ LOCAL鎖定表以允許MyISAM表并行插入。對于支援事務的表例如InnoDB和BDB，--single-transaction是一個更好的選擇，因為它根本不需要鎖定表。請注意當導出多個資料庫時，--lock-tables分别為每個資料庫鎖定表。是以，該選項不能保證導出檔案中的表在資料庫之間的邏輯一緻性。不同資料庫表的導出狀态可以完全不同。 |
| --single-transaction | 适合innodb事務資料庫的備份。保證備份的一緻性，原理是設定本次會話的隔離級别為Repeatable read，來保證本次會話（也就是dump）時，不會看到其它會話已經送出了的資料。 |
| -F | 重新整理binlog，如果binlog打開了，-F參數會在備份時自動重新整理binlog進行切換。 |
| -n --no-create-db | 隻導出資料，而不添加CREATE DATABASE 語句。 |
| -t --no-create-info | 隻導出資料，而不添加CREATE TABLE 語句。 |
| -d --no-data | 不導出任何資料，隻導出資料庫表結構。 |
| -p --password | 連接配接資料庫密碼 |
| -P --port | 連接配接資料庫端口号 |
| -u --user | 指定連接配接的使用者名。 |
| -R | 備份存儲過程和函數資料（如果開發寫了函數和存儲過程，就備，沒寫就不備） |
| --triggers | 備份觸發器資料（現在都是開發寫觸發器） |
實體備份之Xtrabackup
Xtrabackup 是一個對 InnoDB 做資料備份的工具，據官方介紹，這也是世界上惟一一款開源的能夠對innodb和xtradb資料庫進行熱備的工具；支援線上熱備份(備份時不影響資料讀寫)，是商業備份工具 InnoDB Hotbackup 的一個很好的替代品。
官方文檔：http://www.percona.com/doc/percona-xtrabackup/2.1/
Xtrabackup有兩個主要的工具：
- xtrabackup隻能備份InnoDB和XtraDB兩種資料表，而不能備份MyISAM資料表。
- innobackupex 是參考了 InnoDB Hotbackup 的 innoback 腳本修改而來的.innobackupex 是一個 perl 腳本封裝，封裝了 xtrabackup。主要 是為了友善的同時備份InnoDB和MyISAM引擎的表，但在處理myisam時需要加一個讀鎖。并且加入了一些使用的選項。如slave-info可以記錄備份 恢複後，作為slave需要的一些資訊，根據這些資訊，可以很友善的利用備份來重做slave。
Xtrabackup可以做些做什麼？
線上(熱)備份整個庫的InnoDB、XtraDB表
在xtrabackup的上一次整庫備份基礎上做增量備份(innodb only)，以流的形式産生備份，可以直接儲存到遠端機器上(本機硬碟空間不足時很有用)
MySQL資料庫本身提供的工具并不支援真正的增量備份，二進制日志恢複是point-in-time(時間點)的恢複而不是增量備份
Xtrabackup工具支援
對InnoDB存儲引擎進行增量備份的工作原理如下：
1.首先完成一次全量備份，并記錄下此時檢查點的LSN(Log Sequence Number)
2.在進行增量備份時，比較表空間中每個頁的LSN是否大于上次備份時的LSN，如果是，則備份該頁；同時記錄目前檢查點的LSN。
首先，在 logfile 中找到并記錄最後一個 checkpoint(“last checkpoint LSN”)，然後開始從 LSN 的位置開始拷貝 InnoDB 的 logfile 到xtrabackup_logfile；接着，開始拷貝全部的資料檔案.ibd；在拷貝全部資料檔案結束之後，才停止拷貝logfile。 因為logfile裡面記錄全部的資料修改情況，是以，即時在備份過程中資料檔案被修改過了，恢複時仍然能夠通過解析xtrabackup_logfile保持資料的一緻。
XtraBackup備份有什麼優點
1.無需停止資料庫進行InnoDB熱備，且快速可靠
2.備份期間不間斷事務處理
3.節省硬碟空間以及網絡帶寬
4.可以自動對備份檔案進行驗證
5.快速恢複，保障線上運作時間持久性
Xtrabackup 安裝
mysql 5.7以下版本，可以采用percona xtrabackup 2.4版本。
mysql 8.0以上版本，可以采用percona xtrabackup 8.0版本，xtrabackup8.0也隻支援mysql8.0以上的版本。
比如，接觸過一些金融行業，mysql版本還是多采用mysql 5.7，當然oracle官方對于mysql 8.0的開發支援力度日益加大，新功能新特性疊代不止。生産環境采用mysql 8.0的版本比例會日益增加。
下載下傳位址：https://www.percona.com/downloads/
安裝方式
下載下傳rpm包
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.24/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
yum localinstall -y percona-xtrabackup-24-2.4.24-1.el7.x86_64.rpm
# 安裝完畢後會生成指令
xtrabackup 以前使用該指令
innobackupex 現在使用該指令
innobackupex是xtrabackup的前端配置工具，使用innobackupex備份時, 會調用xtrabackup備份所有的InnoDB表, 複制所有關于表結構定義的相關檔案(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案, 同時還會備份觸發器和資料庫配置檔案資訊相關的檔案, 這些檔案會被儲存至一個以時間命名的目錄。
Xtrabackup 備份方式（實體備份）
1.對于非InnoDB存儲引擎（比如MyISAM）是直接鎖表複制資料檔案，這屬于溫備
2.對于InnoDB存儲引擎（支援事務），不鎖表，cp資料頁最終以資料檔案方式儲存下來，并且把redo log和undo log一并備走，屬于熱備方式。
3.備份時讀取配置檔案/etc/my.cnf
Xtrabackup 參數說明
--host 指定主機
--user 指定使用者名
--password 指定密碼
--port 指定端口
--databases 指定資料庫
--incremental 建立增量備份
--incremental-basedir 指定包含完全備份的目錄
--incremental-dir 指定包含增量備份的目錄
--apply-log 對備份進行預處理操作。一般情況下，在備份完成後，資料尚且不能用于恢複操作，因為備份的資料中可能會包含尚未送出的事務或已經送出但尚未同步至資料檔案中的事務。是以，此時資料檔案仍處理不一緻狀态。“準備”的主要作用正是通過復原未送出的事務及同步已經送出的事務至資料檔案也使得資料檔案處于一緻性狀态。
--redo-only 不復原未送出事務
--copy-back 恢複備份目錄
Xtrabackup全量備份與恢複
使用innobackupex備份時，它會調用xtrabackup備份所有的InnoDB表，複制所有關于表結構定義的相關檔案(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相關檔案，同時還會備份觸發器和資料庫配置資訊相關的檔案，這些檔案會被儲存到一個以時間命名的目錄當中。
1.建立備份目錄，我們将資料庫的檔案目錄中的内容都拷貝到這個備份目錄中
mkdir /mysql_backup
2.進行全量備份
innobackupex --user=root --password=123 /mysql_backup/backup_`date +"%F"`
cd /mysql_backup/backup_2021-10-17/
ll
drwxr-x--- 6 root root 238 10月 17 13:31 2021-10-17_13-31-44
# 我們可以發現，預設會在備份目錄下生成一個以時間戳命名的檔案夾
我們可以去掉時間戳，讓備份的内容直接放置于我們指定的目錄中
innobackupex --user=root --password=123 --no-timestamp /mysql_backup/backup_`date +"%F"`
當然我們也可以建立一個普通使用者進行備份
mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456'; #建立使用者
mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser'; #回收此使用者所有權限
mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost'; #授權重新整理、鎖定表、使用者檢視伺服器狀态
mysql> FLUSH PRIVILEGES; #重新整理授權表
執行個體
1.我們先進行全量備份
2.合并資料，保持資料的一緻性
innobackupex --apply-log /mysql_backup/backup_2021-10-17
3.我們将MySQL存放資料檔案的目錄清空模拟資料丢失
rm -rf /mysql_data/*
4.直接使用--copy-back參數進行恢複
innobackupex --copy-back /mysql_backup/backup_2021-10-17
5.為恢複後的資料授權
chown -R mysql.mysql /mysql_data/
6.恢複完畢後檢查一下資料是否恢複
# 需要注意的點：
1.必須要先進行innobackupex全量備份，并指定備份目錄路徑。
2.在恢複先一定要合并資料，使用--apply-log參數
3.3. 恢複時，直接使用--copy-back參數進行恢複，需要注意的是，在my.cnf中要指定資料檔案目錄的路徑。
Xtrabackup增量備份與恢複
使用innobackupex進行增量備份，每個InnoDB的頁面都會包含一個LSN資訊，每當相關的資料發生改變，相關的頁面的LSN就會自動增長。這便是InnoDB表可以進行增量備份的基礎
innobackupex通過備份上次完全備份之後發生改變的頁面來實作增量備份，意思就是首先需要進行一次全量備份，第一次增量備份時基于全備的，之後的增量備份都是基于上一次增量備份的。
基于全量備份的增量備份與恢複，做一次增量備份（基于目前最新的全量備份）
--incremental 建立增量備份
--incremental-basedir 指定包含完全備份的目錄
--incremental-dir 指定包含增量備份的目錄
# 總結
1.增量備份需要使用參數--incremental指定需要備份到哪個目錄，使用incremental-dir指定全備目錄
2.進行資料備份時，需要使用參數--apply-log redo-only先合并全備資料目錄資料，確定全備資料目錄資料的一緻性。
3.再将增量備份資料使用參數--incremental-dir合并到全備資料當中
4.最後通過最後的全備資料進行資料的恢複
# 需要注意的是，如果有多個增量備份，需要逐一合并到全備資料當中，再進行恢複
增量備份資料恢複
1.先進行全量備份
innobackupex --user=root --password=123 --no-timestamp /mysql_backup/full
2.向資料庫中某張表插入幾條資料
3.進行增量備份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/full/ /mysql_backup/add1
4.我們模拟資料丢失，直接清空資料庫存放檔案的目錄，并且停止資料庫服務
5.恢複資料的準備，第一步合并全量備份資料
innobackupex --apply-log --redo-only /mysql_backup/full/
合并add1到全量備份中（合并最後一個增量備份時不要加--redo-noly）
innobackupex --apply-log --incremental-dir=/mysql_backup/add1/ /mysql_backup/full/
最後一次合并資料
innobackupex --apply-log /mysql_backup/full/
6.恢複資料，并為恢複的資料授予權限
innobackupex --copy-back /mysql_backup/full/
chown -R mysql.mysql /mysql_data/
7.重新開機資料庫服務，登入資料庫檢視資料恢複是否成功
# 多次增量備份資料恢複
場景：周日進行了全量備份，周一，周二以及周三進行了增量備份，周四資料庫當機，資料丢失一部分（這裡我們将資料庫的資料目錄裡的檔案都删掉進行模拟）
1.先進行全量備份
innobackupex --user=root --password=123 --no-timestamp /mysql_backup/full
2.進行周一至周三的增量備份
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/full/ /mysql_backup/add1
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/add1 /mysql_backup/add2
innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/mysql_backup/add2 /mysql_backup/add3
3.資料已經丢失，我們要進行資料恢複前的準備
1）整理全量備份
innobackupex --apply-log --redo-only /mysql_backup/full/
2）合并周一增量備份到全量備份中
innobackupex --apply-log --redo-only --incremental-dir=/mysql_backup/add1 /mysql_backup/full/
# 我們可以對比一下add1與dull的LSN号：last_lsn保持一緻才行
cat /mysql_backup/full/xtrabackup_checkpoints
cat /mysql_backup/add1/xtrabackup_checkpoints
3）合并周二增量備份到全量備份中
innobackupex --apply-log --redo-only --incremental-dir=/mysql_backup/add2 /mysql_backup/full/
4）合并周三增量備份到全量備份中（# 合并最後一個增量備份的時候不要加--redo-only）
innobackupex --apply-log --incremental-dir=/mysql_backup/add3 /mysql_backup/full/
5）最後一次整理增量備份
innobackupex --apply-log /mysql_backup/full/
6）準備就緒，我們恢複資料
innobackupex --copy-back /mysql_backup/full/
# 并為恢複後的資料授權，否則MySQL服務起不來
chown -R mysql.mysql /mysql_data/
7）我們啟動資料庫，登入資料庫，檢視資料是否恢複完畢即可
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)