MySQL 數據備份_ZenDei技術網路在線

備份類型常見的備份有冷備份、溫備份、熱備份，還有什麼物理備份、邏輯備份、增量備份、差異備份等等。冷備份：需要服務停止，在備份期間不能進行讀和寫操作。溫備份：讀操作可執行；但寫操作不可執行熱備份：讀和寫都可以正常進行，不影響數據備份邏輯備份：導出資料庫中的數據和對象定義為標準 SQL ...

備份類型

常見的備份有冷備份、溫備份、熱備份，還有什麼物理備份、邏輯備份、增量備份、差異備份等等。

冷備份： 需要服務停止，在備份期間不能進行讀和寫操作。

溫備份： 讀操作可執行；但寫操作不可執行

熱備份： 讀和寫都可以正常進行，不影響數據備份

邏輯備份： 導出資料庫中的數據和對象定義為標準 SQL 語句保存在文本文件中。這些 SQL 語句可以重新執行來恢複數據庫。

物理備份： 直接複製資料庫的文件，包括數據文件、索引文件和日誌文件等。它是對資料庫文件系統層面的直接拷貝。

MySQL 備份內容

用戶信息： 例如MySQL的賬號信息，賬號的許可權等。這些都是存放再mysql這個資料庫中的。

業務數據： 相關的業務資料庫

配置文件： MySQL服務的相關配置

MySQL 物理備份

一種方法是將mysql服務停止後，將整個數據目錄進行拷貝或者打包，例如利用tar、cp、rsync等工具

還有一種方法就是利用專門的物理備份工具，例如xtrabackup就是一個非常好用的物理備份工具。

xtrabackup 介紹

xtrabackup是 Percona 開發的一個資料庫物理備份工具，100%開源。主要用來執行MySQL、Percona Server 和 MariaDB 資料庫的備份和恢復操作。

官方文檔：https://docs.percona.com/percona-xtrabackup/innovation-release/

xtrabackup具有以下特點：

1、非常適合大型資料庫以及需要快速備份還原和需要支持災難級備份的場景。

2、支持增量備份、對備份數據進行壓縮、數據加密等功能

3、xtrabackup主要是用來操作innodb這種存儲引擎的數據，且整個過程是非阻塞的。在備份過程中，對資料庫的讀寫操作可以繼續進行，適合需要7*24小時運行的環境

4、正是因為xtrabackup是直接操作資料庫文件和日誌，所以對資料庫的內部結構（如數據字典、redo log 格式）高度敏感。當這些內部結構在資料庫的新版本中發生變化時，XtraBackup 需要更新以適應這些更改。並且高版本不能向下相容低版本。

xtrabackup 安裝

安裝的時候需要先確認需要備份的資料庫版本，然後再參考官方文檔下載合適的 xtrabackup 版本才行。上面也說了，MySQL不同版本的文件結構有差別，所以造成不能高版本相容低版本的問題。

例如：mysql的版本是5.7.x，那麼就需要下載 2.4版本的xtrabackup，8.0版本的xtrabackup不能備份mysql5.7。

xtrabackup 備份數據

備份整個資料庫

XtraBackup 是直接備份物理文件的工具，但是也需要連接到 MySQL。因為需要知道MySQL的一些配置（如 datadir、innodb_data_home_dir、innodb_log_group_home_dir 等），通過這些配置來確定需要備份的文件。

還有就是讀取二進位日誌位置來確保備份可以用於點恢復操作。連接MySQL的參數和mysql命令行工具是一樣的。

1、創建備份目錄

需要執行備份操作的過程中，具有對這個目錄的讀和寫許可權，所以可以直接以root用戶身份執行。

mkdir /home/ehigh/mysql_bak

2、執行備份操作

通過 --backup 選項可以執行全備份。

例如：

sudo xtrabackup --backup --host="192.168.15.12" --user="root" --password="eHIGH2014" --port=3306 --datadir="/var/lib/mysql" --target-dir="/home/ehigh/mysql_bak"

3、數據恢復前的準備

通過 --prepare 選項讓 xtrabackup完成一些數據恢復前的準備工作，因為執行物理備份時，XtraBackup 複製的是資料庫文件的實時狀態，包括那一刻的所有數據文件和日誌文件。

這個狀態下可能會有未完成的事務等等。通過這個操作可以將這些未完成的事務回滾。保證恢復時資料庫的一致性。

而且執行這個操作是不會影響到現有數據的，因為這個操作僅針對備份的數據進行處理，只會處理備份目錄中的備份文件，例如應用重做日誌和回滾未完成的事務。

sudo xtrabackup --prepare --target-dir="/home/ehigh/mysql_bak"

4、進行數據恢復

(1)停止服務。避免在恢複數據時發生數據損壞。

sudo systemctl stop mysql.service

(2)清空數據目錄。

sudo rm -rf /var/lib/mysql

mv /var/lib/mysql /var/lib/mysql-bak

mkdir /var/lib/mysql

chown -R mysql:mysql /var/lib/mysql

(3)執行數據恢復
執行數據恢復一種方法是直接使用cp或者rsync工具將備份目錄下的所有文件拷貝到mysql數據目錄中，還有種方法就是通過 --copy-back 或 --move-back 選項從備份目錄將數據恢復到原始 MySQL 數據目錄。

這兩個參數的區別如下：

--copy-back：將備份數據複製回 MySQL 的數據目錄。這個過程不會刪除或更改備份目錄中的原始備份文件
--move-back：將備份數據移動（而非複製）回 MySQL 的數據目錄，移動過後備份目錄中就不存在備份文件了

sudo xtrabackup --copy-back  --target-dir=/home/ehigh/mysql_bak  --datadir=/var/lib/mysql

（4）更改文件許可權
將MySQL數據目錄下的文件許可權改為mysql

sudo chown -R /var/lib/mysql

備份整個資料庫並壓縮數據

xtrabackup不同版本支持的壓縮演算法不同，具體信息可以看官方文檔確認。通過 --compress選項即可再備份數據的時候，將數據進行壓縮。

1、備份數據

例如：

sudo xtrabackup --backup --host="192.168.15.12" --user="root" --password="eHIGH2014" --port=3306 --compress --compress-threads=4   --datadir="/var/lib/mysql" --target-dir="/home/ehigh/mysql_bak_c"

2、恢複數據前先解壓縮

在備份數據的時候xtrabackup會通過自帶的一些壓縮演算法庫文件將文件進行壓縮，但是解壓的時候需要在系統上安裝對應的工具才行。

通過 --decompress 選項即可完成解壓

sudo xtrabackup --decompress --target-dir=/home/ehigh/mysql_bak_c

3、恢復前的準備

通過 --prepare 選項完成恢復前的準備，具體操作和上面一樣的

4、數據恢復

（1）停止MySQL服務

（2）清空MySQL數據目錄

（3）使用 xtrabackup 恢複數據

（4）修改MySQL數據目錄中文件的屬主和屬組

備份單個資料庫

XtraBackup 備份單個資料庫的操作比較特殊，因為 XtraBackup 主要設計用於備份整個 MySQL 伺服器的。備份單個庫用mysqldump就行了。

MySQL 邏輯備份

就是將資料庫中的數據備份為SQL語句，將這些SQL語句存放在一個文件中，恢複數據的時候直接執行這個文件裡面的SQL即可重現數據。

mysqldump

mysqldump是mysql服務自帶的一個工具，mysqldump可以跨平臺使用且使用簡單，非常適用於小到中等規模的資料庫。

預設情況下，mysqldump 工具將生成的 SQL 語句輸出到標準輸出（stdout）。如果直接運行 mysqldump 命令而不進行任何重定向，它會在命令行界面中顯示 SQL 語句。

備份整個資料庫

mysqldump -u 用戶名 -p 用戶密碼  database_name > backup.sql

通過這種方法備份整個資料庫時，會生成一個包含所有表結構和數據的 SQL 文件，但不會包含 CREATE DATABASE 語句。即恢複數據的時候需要手動將對應的資料庫創建好，然後再將數據導入到該資料庫中。

如果需要生成一個帶 CREATE DATABASE 語句的sql文件，加一個 --databases 參數就可以了。

備份多個資料庫

如果需要一次性備份多個指定的資料庫，可以通過 mysqldump 的 --databases 選項來實現。還可以用 -B 參數，-B參數效果和 --databases 一樣的。

mysqldump -u user_name -p password --databases db1 db2 db3 > backup.sql

備分所有資料庫

如果想將所有資料庫進行備份的話，通過 --all-databases 選項可以實現，也可以用 -A 參數，效果是一樣的

mysqldump -u user_name -p password --all-databases > backup.sql

備份單個數據表

如果只想要備份單張表，只需要在資料庫的後面指定表名就行了。

mysqldump -u user_name -p password database_name table_name > backup.sql

備份多個數據表

在資料庫的後面指定多個表名就行了。

mysqldump -u user_name -p password database_name tb1 tb2 tb3 > backup.sql

只備份表結構，不備份數據

僅備份資料庫結構（例如，表定義、視圖、存儲過程等），但不包含任何實際數據，可以使用 --no-data 選項。

mysqldump -u user_name -p password --no-data database_name table_name > backup.sql

只備份數據，不備份表結構

只想備份數據而不包括表結構，可以使用 --no-create-info 選項

mysqldump -u user_name -p password --no-create-info database_name table_name > backup.sql

備份表的一部分數據

如果需要備份一個數據表中的部分數據，通過 -where 選項來指定符合特定條件的數據。

例如：備份id < 1000 的數據

mysqldump -u user_name -p password --databases db1 tables1  --where="id < 1000" > users_backup.sql

備份存儲過程和函數

使用 --routines 選項可以確保存儲過程和函數被包含在備份中，也可以使用簡寫的-R參數。

mysqldump -u user_name -p --no-create-info --no-data --no-create-db --routines --skip-triggers database_name > routines_backup.sql

打開備份的sql文件，我們可以看到 /*!50003 ... */ 這是MySQL的一種特殊註釋模式，稱為“條件註釋”或“版本控制註釋”。這些註釋內容實際上會被 MySQL 執行。

/*!50003 ，這裡的50003表示該命令需要的最低 MySQL 版本為 5.00.03，MySQL版本大於或等於這個版本的時候，註釋中的內容會被執行。

mysqldump 高級選項

在備份數據的過程中，可以添加一些選項來對備份進行優化：

1、優化備份速度：

如果備份一個數據量很大的庫或者表，mysqldump 預設會讀取整個表到記憶體中，然後寫入到備份文件。如果表非常大，這可能會消耗大量記憶體，並可能導致過度的記憶體使用甚至崩潰。

這個時候可以通過 --quick 選項，它是直接逐行讀取數據並寫入備份文件，顯著減少了一次性記憶體需求。從而加快備份速度。

2、主從複製環境：

預設情況下，備份數據文件的時候，是不會記錄當前二進位日誌位置的。使用 --master-data 時，mysqldump 會在 SQL 備份文件中添加一個 CHANGE MASTER TO 語句。

這個語句包含了備份時刻的二進位日誌文件名和位置（log file position）。這對於設置複製非常重要，因為它指明瞭從伺服器（slave）開始讀取主伺服器（master）二進位日誌的起始點。

--master-data有兩個值，預設值是2

--master-data=1：以非註釋形式包含 CHANGE MASTER TO 語句
--master-data=2：使CHANGE MASTER TO 語句以註釋形式添加，從而在不自動更改從伺服器配置的情況下提供必要的信息。

--master-data 選項經常結合 --flush-logs 選項一起使用，--flush-logs 會在備份開始前刷新 MySQL 伺服器的日誌，包括二進位日誌（binary log）。此時 MySQL 會關閉當前的二進位日誌文件並開始一個新的日誌文件。

說明：

--master-data 選項在 mysqldump 輸出的 CHANGE MASTER TO 語句中主要包含 MASTER_LOG_FILE 和 MASTER_LOG_POS 參數，這些參數指明瞭二進位日誌（binary log）的文件名和位置。

但是，這個語句並不包含完整的主伺服器（master）配置，如 master_host、master_user、master_password 等。

所以這個參數主要用於記錄備份時刻的二進位日誌位置，這對於配置從伺服器從正確的位置開始複製數據很重要，所以完整的數據配置可以自己手動修改這個sql文件實現。

3、innodb引擎表備份：

使用 --single-transaction 選項，mysqldump 會在開始備份之前啟動一個新的事務。所以備份會捕捉到事務開始時刻的資料庫狀態，並且在整個備份過程中保持這一狀態，即使後續對資料庫進行了更改。

4、mysql資料庫備份

mysql這個資料庫裡面存放了mysql的用戶和許可權信息，如果想讓備份文件被恢復到另一個 MySQL 伺服器上後，用戶和許可權的更改立即生效。需要加 --flush-privileges 選項

添加這個選項後，會在 SQL 備份文件中添加一條 FLUSH PRIVILEGES; 語句。當這個備份文件被導入到 MySQL 伺服器時，FLUSH PRIVILEGES; 語句會執行，從而重新載入許可權表。

這確保了任何用戶和許可權的更改（如新用戶的添加或許可權的修改）會立即生效。這樣就不用手動刷新許可權了。

5、mysam引擎備份
現在一般用的都是innodb這個存儲引擎，使用mysqldump這個工具的時候，innodb是支持熱備份的，而mysam只支持溫備份，即備份過程中能讀不能寫。

如果備份所有資料庫，通過 --lock-all-tables 選項或者 -x選項來加全局讀鎖，會鎖定所有資料庫的所有表。也會導致一個問題，數據量大時，可能會導致長時間無法併發訪問資料庫。

mysqldump -u user_name -p --lock-all-tables --all-databases > backup.sql

如果支持備份單個資料庫，可以用 -lock-tables 參數，這樣只會鎖定正在備份的資料庫中的表。備份操作不會影響到伺服器上其他資料庫中的表。

mysqldump -u user_name -p --lock-tables database_name > backup.sql