一、 mysqldump 在 MySQL 5.6 的工作原理

1.邏輯備份工具

• mysqldump 是一個(gè) 客戶端工具，它不會(huì)直接復(fù)制數(shù)據(jù)文件，而是通過 SQL 協(xié)議連接到數(shù)據(jù)庫，把表結(jié)構(gòu)和數(shù)據(jù)導(dǎo)出為一系列 SQL 語句（CREATE TABLE、INSERT 等）。
• 恢復(fù)時(shí)只需執(zhí)行這些 SQL，就能在另一臺(tái)服務(wù)器上重建數(shù)據(jù)庫。

2.一致性快照

• 對 InnoDB 表： 使用 --single-transaction，在導(dǎo)出開始時(shí)開啟一個(gè)事務(wù)，保證整個(gè)導(dǎo)出過程看到的是同一個(gè)一致性快照，不會(huì)鎖表。
• 對 MyISAM 表： 因?yàn)椴恢С质聞?wù)，必須用 --lock-tables 來加讀鎖，否則可能出現(xiàn)數(shù)據(jù)不一致。

3.對象導(dǎo)出

• 默認(rèn)只導(dǎo)出表結(jié)構(gòu)和數(shù)據(jù)。
• 如果加上 --routines --triggers --events，還能導(dǎo)出存儲(chǔ)過程、觸發(fā)器和事件。
• --hex-blob 確保二進(jìn)制字段（BLOB/TEXT）在導(dǎo)出時(shí)不會(huì)亂碼。

4.復(fù)制位點(diǎn)

• --master-data=2 會(huì)在導(dǎo)出文件里寫入當(dāng)前 binlog 文件和位置（作為注釋），方便主從復(fù)制或點(diǎn)時(shí)間恢復(fù)。
• 在 MySQL 5.6 開啟 GTID 時(shí)，mysqldump 會(huì)提示是否要導(dǎo)出 GTID 集合。通常建議加 --set-gtid-purged=OFF，避免恢復(fù)時(shí) GTID 沖突。

5.性能優(yōu)化

• --quick：逐行讀取數(shù)據(jù)并寫入文件，避免客戶端內(nèi)存爆炸。
• --extended-insert：使用多行 INSERT，減少 SQL 語句數(shù)量，加快恢復(fù)速度。
• 壓縮：mysqldump 本身不壓縮，通常配合 gzip 使用，例如：

[root@localhost ~]# mysqldump ... | gzip > backup.sql.gz

6.恢復(fù)過程

• 恢復(fù)時(shí)直接執(zhí)行：

  [root@localhost ~]# gunzip < backup.sql.gz | mysql -u root -p

• mysqldump 在導(dǎo)出時(shí)會(huì)自動(dòng)關(guān)閉外鍵檢查，恢復(fù)時(shí)再開啟，避免因表順序?qū)е峦怄I錯(cuò)誤。

?? 局限性

• 大庫性能差：TB 級(jí)數(shù)據(jù)庫用 mysqldump 會(huì)非常慢，恢復(fù)更慢。

• 非事務(wù)表不一致：MyISAM 表無法保證一致性，必須鎖表。

• 跨版本風(fēng)險(xiǎn)：5.6 導(dǎo)出的 SQL 在 8.0 恢復(fù)時(shí)可能遇到字符集、排序規(guī)則不兼容問題。

?MySQL 5.6 和 MySQL5.7 mysqldump 邏輯備份深度對比表

? 總結(jié)：MySQL 5.6 的 mysqldump 是一個(gè) 邏輯備份工具，通過 SQL 導(dǎo)出數(shù)據(jù)庫結(jié)構(gòu)和數(shù)據(jù)，適合小型或中型數(shù)據(jù)庫、跨版本遷移，但不適合超大規(guī)模數(shù)據(jù)庫。

二、執(zhí)行備份

使用mysqldump命令來執(zhí)行備份

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

?? 參數(shù)含義逐條解釋

• --defaults-extra-file=/etc/mysqldump57_backup.cnf 指定一個(gè)額外的配置文件，里面寫好用戶名、密碼、host、port 等信息。這樣避免在命令里明文寫密碼，提高安全性。
• --single-transaction 在 InnoDB 表上使用一致性快照（事務(wù)隔離），保證導(dǎo)出數(shù)據(jù)的一致性，同時(shí)不鎖表，業(yè)務(wù)可以繼續(xù)寫入。
• --routines 導(dǎo)出存儲(chǔ)過程和函數(shù)。默認(rèn)不導(dǎo)出，必須顯式加上。
• --hex-blob 把 BLOB/TEXT/BINARY 字段用十六進(jìn)制格式導(dǎo)出，避免亂碼或特殊字符導(dǎo)致恢復(fù)失敗。
• --triggers 導(dǎo)出觸發(fā)器。默認(rèn)會(huì)導(dǎo)出，但顯式寫上更清晰。
• --events 導(dǎo)出事件調(diào)度器里的事件（EVENT）。默認(rèn)不導(dǎo)出，需要顯式指定。
• --all-databases 導(dǎo)出所有數(shù)據(jù)庫。如果只想導(dǎo)出某個(gè)庫，可以改成 --databases dbname。
• --flush-logs 在導(dǎo)出時(shí)刷新 binlog，生成新的日志文件，方便后續(xù)恢復(fù)或復(fù)制。
• --master-data=2 在導(dǎo)出文件里寫入當(dāng)前 binlog 文件和位置（作為注釋），用于主從復(fù)制或點(diǎn)時(shí)間恢復(fù)。
  • =1：寫成可執(zhí)行 SQL。
  • =2：寫成注釋，避免恢復(fù)時(shí)自動(dòng)執(zhí)行。
• --set-gtid-purged=OFF 在啟用 GTID 的環(huán)境下，避免導(dǎo)出時(shí)寫入 SET @@GLOBAL.gtid_purged，防止恢復(fù)時(shí) GTID 沖突。
• | gzip > ... 把導(dǎo)出的 SQL 文件壓縮成 .sql.gz，節(jié)省存儲(chǔ)空間。

三、備份腳本

編寫基于MySQL5.6的mysqldump備份腳本

腳本目錄結(jié)構(gòu)：

[root@localhost ~]# tree -L 5 /data/
/data/
└── mysql56
    ├── backup
    │   └── mysqldump_backup
    │       ├── logs
    │       │   ├── mysqldump_2026-01-05_09-40-21.log
    │       │   ├── mysqldump_2026-01-05_09-46-33.log
    │       │   └── mysqldump_2026-01-05_11-06-00.log
    │       ├── mysqldump_2026-01-05_09-40-21.sql.gz
    │       ├── mysqldump_2026-01-05_09-46-33.sql.gz
    │       └── mysqldump_2026-01-05_11-06-00.sql.gz
    └── scripts
        └── mysqldump8_backup.sh

5 directories, 7 files

備份腳本：

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

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

# 備份根目錄
BACKUP_BASE=/data/mysql56/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/mysqldump56_backup.cnf

#=============================創(chuàng)建目錄==========================================
mkdir -p "$BACKUP_BASE" "$LOG_DIR"

echo "========================== MySQL 5.6 mysqldump備份開始于 [$Timestamp] ===================" | tee -a "$LOG_FILE"

#=============================執(zhí)行備份==========================================
if /usr/local/mysql/bin/mysqldump --defaults-extra-file=${MYSQL_CNF} \
    --single-transaction \
    --routines \
    --hex-blob \
    --triggers \
    --events \
    --all-databases \
    --flush-logs \
    --master-data=2 \
    --set-gtid-purged=OFF \
     | gzip > "${BACKUP_BASE}/${BACKUP_NAME}"; then
    echo "Success: mysqldump備份成功！文件路徑: ${BACKUP_BASE}/${BACKUP_NAME}" | tee -a "$LOG_FILE"
else
    echo "ERROR: mysqldump備份失?。≌垯z查日志 ${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ù)簡報(bào)========================================
BACKUP_SIZE=$(du -sh "${BACKUP_BASE}/${BACKUP_NAME}" | cut -f1)
echo "本次mysqldump備份大小：${BACKUP_SIZE}" | tee -a "$LOG_FILE"

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

?賬號(hào)密碼不寫進(jìn)明文，采用配置文件方式

在 /etc/mysqldump56_backup.cnf 里寫好備份賬號(hào)信息：

[root@localhost ~]# vim /etc/mysqldump56_backup.sh
[client]
user=root                         #自行選擇備份賬號(hào)
password=asfasdfO&*#HR   
port=3306
socket=/usr/local/mysql/mysql.sock

執(zhí)行腳本

#給腳本執(zhí)行權(quán)限
[root@localhost ~]# chmod +x mysqldump56_backup.sh
#執(zhí)行腳本
[root@localhost ~]# bash mysqldump56_backup.sh
========================== mysqldump備份開始于 [2026-01-05_11-06-00] ===================
Success: mysqldump備份成功！文件PATH: /data/mysql56/backup/mysqldump_backup/mysqldump_2026-01-05_11-06-00.sql.gz
正在清理 30 天之前的舊備份...
本次mysqldump備份大?。?00M
========================== mysqldump備份完成于 [2026-01-05 11:06:12] ===================

加入計(jì)劃任務(wù) 

[root@localhost ~]# crontab -e
0 2 * * * bash /data/mysql56/scripts/mysqldump56_backup.sh >> /var/log/mysql/mysqldump56_cron.log 2>&1

四、恢復(fù)備份流程

MySQL 5.6 mysqldump 恢復(fù)流程

1. 準(zhǔn)備環(huán)境

• 確認(rèn)目標(biāo)庫版本為 MySQL 5.6。
• 確認(rèn)目標(biāo)庫已初始化，并有足夠磁盤空間。
• 建議在恢復(fù)前關(guān)閉業(yè)務(wù)連接，避免寫入沖突。
• 如果是主從環(huán)境，確認(rèn) binlog_format 和復(fù)制參數(shù)一致。

2. 解壓備份文件

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

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

得到：

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

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

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

[root@localhost ~]# mysql --defaults-extra-file=/etc/mysqldump56_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/mysqldump56_backup.cnf

4. 恢復(fù)過程中的注意事項(xiàng)

• 外鍵檢查： mysqldump 在導(dǎo)出時(shí)會(huì)自動(dòng)關(guān)閉 FOREIGN_KEY_CHECKS，恢復(fù)時(shí)再開啟，避免因表順序?qū)е峦怄I錯(cuò)誤。

• 唯一性檢查： 大庫恢復(fù)時(shí)可臨時(shí)關(guān)閉：

  SET UNIQUE_CHECKS=0;
  SET FOREIGN_KEY_CHECKS=0;

  導(dǎo)入完成后再開啟：

  SET UNIQUE_CHECKS=1;
  SET FOREIGN_KEY_CHECKS=1;

• GTID 支持： MySQL 5.6 是 GTID 的引入階段，恢復(fù)時(shí)如果備份文件包含 SET @@GLOBAL.gtid_purged，可能報(bào)錯(cuò)。建議在備份時(shí)使用 --set-gtid-purged=OFF，恢復(fù)后再根據(jù)需要手動(dòng)設(shè)置。

• 字符集/排序規(guī)則： 5.6 的默認(rèn)字符集可能與目標(biāo)庫不同，恢復(fù)時(shí)需確認(rèn) character_set_server 和 collation_server。

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

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

SHOW DATABASES;
SHOW TABLES FROM dbname;

檢查數(shù)據(jù)量：

SELECT COUNT(*) FROM dbname.tablename;

檢查對象完整性：

SHOW PROCEDURE STATUS;
SHOW TRIGGERS;
SHOW EVENTS;

檢查 binlog 位點(diǎn)（如果有 --master-data）： 在恢復(fù)文件開頭會(huì)看到：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;

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

• 檢查并創(chuàng)建缺失的DEFINER 用戶，確保存儲(chǔ)過程/觸發(fā)器能正常運(yùn)行。

• 如果是主從環(huán)境，執(zhí)行：

  CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000123', MASTER_LOG_POS=456789;
  START SLAVE;

• 檢查日志，確認(rèn)沒有報(bào)錯(cuò)或警告。

? 總結(jié)

MySQL 5.6 恢復(fù)流程的關(guān)鍵點(diǎn)：

• 解壓備份文件
• 導(dǎo)入 SQL 文件
• 處理外鍵/唯一性檢查
• 注意 GTID 和字符集差異
• 驗(yàn)證恢復(fù)結(jié)果
• 收尾工作（權(quán)限、復(fù)制、日志檢查）

到此這篇關(guān)于MySQL5.6使用mysqldump數(shù)據(jù)備份的實(shí)現(xiàn)的文章就介紹到這了,更多相關(guān)MySQL5.6 mysqldump數(shù)據(jù)備份內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論