快捷導(dǎo)航

MySQL8配置mysqldump邏輯備份的實(shí)現(xiàn)

更新時(shí)間：2026年03月03日 09:20:17 作者：leaf9z

mysqldump是MySQL自帶的邏輯備份工具,通過(guò)生成SQL語(yǔ)句實(shí)現(xiàn)跨平臺(tái)/版本的數(shù)據(jù)備份,下面就來(lái)介紹一下MySQL8配置mysqldump邏輯備份的實(shí)現(xiàn),文中通過(guò)示例代碼介紹的非常詳細(xì),對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

1. 核心特點(diǎn)

邏輯備份：它備份的是 SQL 指令，而不是物理數(shù)據(jù)文件（.ibd）。
跨平臺(tái)/版本：由于是 SQL 文本，你可以把 5.7 版本的備份恢復(fù)到 8.0 版本，或者從 Linux 遷移到 Windows。
簡(jiǎn)單靈活：可以只備份一個(gè)表、一個(gè)庫(kù)，或者整個(gè)服務(wù)器。
恢復(fù)成本：對(duì)于小數(shù)據(jù)庫(kù)非常方便，但對(duì)于 TB 級(jí)別的大數(shù)據(jù)庫(kù)，恢復(fù)速度比物理備份（XtraBackup）慢得多。

2. 主要優(yōu)缺點(diǎn)

維度	優(yōu)點(diǎn)	缺點(diǎn)
可讀性	備份文件是純文本，可以直接用編輯器打開(kāi)看。	文件體積相對(duì)物理備份較大（壓縮前）。
細(xì)粒度	能夠很方便地只恢復(fù)某一個(gè)表的數(shù)據(jù)。	恢復(fù)時(shí)需要重新構(gòu)建索引，CPU 和 IO 消耗大。
兼容性	兼容性極強(qiáng)，適合做數(shù)據(jù)庫(kù)升級(jí)或遷移。	備份大型數(shù)據(jù)庫(kù)時(shí)，對(duì)生產(chǎn)環(huán)境的壓力持續(xù)時(shí)間長(zhǎng)。
鎖表情況	配合 --single-transaction 可實(shí)現(xiàn) InnoDB 的無(wú)鎖熱備。	如果有非 InnoDB 表（如 MyISAM），備份時(shí)會(huì)鎖表。

3. mysqldump 的工作流程

連接查詢：連接到 MySQL 服務(wù)器。
獲取結(jié)構(gòu)：查詢表結(jié)構(gòu)（DDL），生成 CREATE TABLE 語(yǔ)句。
讀取數(shù)據(jù)：將每一行數(shù)據(jù)讀出來(lái)，轉(zhuǎn)換成 INSERT 語(yǔ)句。
生成文件：將這些 SQL 語(yǔ)句寫入到 .sql 文件中。

4. 常見(jiàn)的備份場(chǎng)景

日常小規(guī)模備份：數(shù)據(jù)量在 50G 以下時(shí)，mysqldump 是首選。
單表誤刪找回：物理備份只能整庫(kù)恢復(fù)，而 mysqldump 允許你只把那張誤刪的表導(dǎo)入回去。
環(huán)境遷移：把生產(chǎn)環(huán)境的數(shù)據(jù)抽出一部分導(dǎo)給開(kāi)發(fā)環(huán)境使用。

5. 執(zhí)行備份

5.1 推薦創(chuàng)建專用備份賬號(hào)

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongPass!';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, RELOAD, PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

5.2 準(zhǔn)備環(huán)境（不在備份命令中明文暴露）

[root@localhost ~]# vim /etc/mysql_backup.cnf
[client]
user=root
password=LJFLDskfdjsldkjfl
port=3306                             #根據(jù)自己實(shí)際端口填寫
socket=/var/lib/mysql/mysql.sock      #根據(jù)自己實(shí)際sock填寫

5.3 執(zhí)行備份

[root@localhost ~]# mysqldump --defaults-extra-file=/etc/mysql_backup.cnf \
    --single-transaction \
    --routines \
    --hex-blob \
    --set-gtid-purged=OFF \
    --triggers \
    --events \
    --all-databases \
    --flush-logs \
    --source-data=2 \
   | gzip > "/data/mysql/backup/logical_backup/mysqldump_$(date '+%F_%H-%M-%S').sql.gz"

6. 編寫備份腳本

[root@localhost ~]#vim  mysqldump_backup.sh 
#!/bin/bash
#
# ==============================================================================
# MySQL 邏輯備份腳本（基于 mysqldump）
# 適用于 MySQL 8.0，支持定時(shí)任務(wù)
# 作者：Noleaf
# 日期：2025-12-31
# ==============================================================================

set -euo pipefail

#=============================變量定義==========================================

# 時(shí)間戳
Timestamp=$(date '+%F_%H-%M-%S')

# 備份根目錄
BACKUP_BASE=/data/mysql8/backup/mysqldump_backup

# 備份文件名稱
BACKUP_NAME="mysqldump_${Timestamp}.sql.gz"

# 日志目錄及文件
LOG_DIR=${BACKUP_BASE}/logs
LOG_FILE=${LOG_DIR}/mysqldump_${Timestamp}.log

# 保留天數(shù)
KEEP_DAYS=30

# MySQL 配置文件（包含用戶名和密碼）
MYSQL_CNF=/etc/mysql_backup.cnf

#=============================創(chuàng)建目錄==========================================

mkdir -p "$BACKUP_BASE" "$LOG_DIR"

echo "========================== mysqldump備份開(kāi)始于 [$Timestamp] ===================" | tee -a "$LOG_FILE"

#=============================執(zhí)行備份==========================================
#注意mysqldump路徑
if /usr/local/mysql-8.0.44/bin/mysqldump --defaults-extra-file=${MYSQL_CNF} \
    --single-transaction \
    --routines \
    --hex-blob \
    --set-gtid-purged=OFF \
    --triggers \
    --events \
    --all-databases \
    --flush-logs \
    --source-data=2 \
    | gzip > "${BACKUP_BASE}/${BACKUP_NAME}"; then
    echo "Success: mysqldump備份成功！文件: ${BACKUP_BASE}/${BACKUP_NAME}" | tee -a "$LOG_FILE"
else
    echo "ERROR: mysqldump備份失?。≌?qǐng)檢查日志 ${LOG_FILE}" | tee -a "$LOG_FILE"
    exit 1
fi

#=============================清理舊備份==========================================

echo "正在清理 $KEEP_DAYS 天之前的舊備份..." | tee -a "$LOG_FILE"
find "${BACKUP_BASE}" -maxdepth 1 -type f -name "mysqldump_*.sql.gz" -mtime +${KEEP_DAYS} -exec rm -f {} \;
find "${LOG_DIR}" -maxdepth 1 -type f -name "mysqldump_*.log" -mtime +${KEEP_DAYS} -exec rm -f {} \;

#=============================統(tǒng)計(jì)數(shù)據(jù)簡(jiǎn)報(bào)========================================

BACKUP_SIZE=$(du -sh "${BACKUP_BASE}/${BACKUP_NAME}" | cut -f1)
echo "本次mysqldump備份大?。?{BACKUP_SIZE}" | tee -a "$LOG_FILE"

echo "=================== mysqldump備份完成于 [$(date '+%F %H:%M:%S')] ===================" | tee -a "$LOG_FILE"

7. MySQL8/GTID 恢復(fù)備份流程

7.1 準(zhǔn)備環(huán)境

確認(rèn)目標(biāo)庫(kù)為 MySQL 8.0，并啟用了 GTID 模式 (gtid_mode=ON，enforce_gtid_consistency=ON)。
確認(rèn)目標(biāo)庫(kù)已初始化，并有足夠磁盤空間。
建議在恢復(fù)前關(guān)閉業(yè)務(wù)連接，避免寫入沖突。

7.2 解壓備份文件

如果備份文件是 .sql.gz 壓縮格式，先解壓：

[root@localhost ~]# gunzip /data/mysql8/backup/mysqldump_backup/mysqldump_2026-01-05_14-30-00.sql.gz

得到解壓備份文件：

mysqldump_2026-01-05_14-30-00.sql

7.3 執(zhí)行恢復(fù)命令

直接導(dǎo)入 SQL 文件：

[root@localhost ~]# mysql --defaults-extra-file=/etc/mysqldump8_backup.cnf < mysqldump_2026-01-05_14-30-00.sql

如果是壓縮文件，用管道方式：

[root@localhost ~]# gunzip < mysqldump_2026-01-05_14-30-00.sql.gz | mysql --defaults-extra-file=/etc/mysqldump8_backup.cnf

7.4 恢復(fù)過(guò)程中的注意事項(xiàng)

GTID 設(shè)置
- 如果備份文件包含：
```
SET @@GLOBAL.gtid_purged='uuid:transaction_id,...';
```
  在恢復(fù)時(shí)可能報(bào)錯(cuò)。
- 建議在備份時(shí)使用 --set-gtid-purged=OFF，恢復(fù)后再根據(jù)需要手動(dòng)設(shè)置。
- 如果必須導(dǎo)入 GTID 集合，需先確認(rèn)目標(biāo)庫(kù)為空庫(kù)，并執(zhí)行：
```
SET GLOBAL gtid_purged='uuid:transaction_id,...';
```
外鍵檢查 mysqldump 會(huì)自動(dòng)關(guān)閉/開(kāi)啟 FOREIGN_KEY_CHECKS，避免因表順序?qū)е峦怄I錯(cuò)誤。
唯一性檢查 大庫(kù)恢復(fù)時(shí)可臨時(shí)關(guān)閉：
```
SET UNIQUE_CHECKS=0;
SET FOREIGN_KEY_CHECKS=0;
```
導(dǎo)入完成后再開(kāi)啟。
嚴(yán)格模式 MySQL 8 默認(rèn)開(kāi)啟嚴(yán)格模式，對(duì)非法日期（如 0000-00-00）、超長(zhǎng)字符串會(huì)報(bào)錯(cuò)。恢復(fù)前可調(diào)整：
```
SET sql_mode='NO_ENGINE_SUBSTITUTION';
```
對(duì)象屬性 MySQL 8 對(duì) DEFINER 用戶 要求更嚴(yán)格，如果備份文件中定義的用戶不存在，恢復(fù)后對(duì)象可能無(wú)法運(yùn)行。需提前創(chuàng)建：
```
CREATE USER 'definer_user'@'%' IDENTIFIED BY 'password';
```

7.5 驗(yàn)證恢復(fù)結(jié)果

檢查庫(kù)和表數(shù)量：

SHOW DATABASES;
SHOW TABLES FROM dbname;

檢查數(shù)據(jù)量：
```
SELECT COUNT(*) FROM dbname.tablename;
```

檢查對(duì)象完整性：

SHOW PROCEDURE STATUS;
SHOW TRIGGERS;
SHOW EVENTS;

檢查 GTID 集合：
```
SHOW VARIABLES LIKE 'gtid_executed';
```

7.6 恢復(fù)后的收尾工作

確認(rèn) DEFINER 用戶 存在，避免存儲(chǔ)過(guò)程/觸發(fā)器報(bào)錯(cuò)。
如果是主從環(huán)境，執(zhí)行：
```
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789, MASTER_AUTO_POSITION=1;
START SLAVE;
```
在 GTID 模式下，推薦使用 MASTER_AUTO_POSITION=1，自動(dòng)對(duì)齊 GTID。
檢查日志，確認(rèn)沒(méi)有報(bào)錯(cuò)或警告。