MySQLPump：利用并行參數高效備份數據庫
前言
在MySQL數據庫管理中，備份是保障數據安全與業務連續性的關鍵環節。隨著數據量的不斷增長，傳統備份方式逐漸暴露出效率瓶頸。MySQL 5.7引入的mysqlpump工具，以其強大的多線程備份能力、豐富特性和靈活配置，為數據庫備份工作帶來了新的解決方案。
圖片
核心特性
并行處理加速備份
mysqlpump支持并行備份多個數據庫或表，顯著提升備份速度。通過調整并行度參數，如--default-parallelism和--parallel-schemas，可以根據服務器資源靈活分配任務，充分利用多核CPU的優勢，將備份時間大幅縮短。
實時進度一目了然
備份過程中，進度顯示功能讓管理員隨時掌握備份狀態，不再為漫長的備份過程感到焦慮。這一特性有助于及時發現潛在問題，比如備份停滯或速度過慢，從而及時調整策略。
壓縮功能節省空間
它內置對LZ4和ZLIB算法的支持，在備份時直接壓縮輸出，有效減少備份文件體積，節省存儲空間。這不僅降低了存儲成本，還加快了備份文件的傳輸速度，方便異地存儲和災備。
權限與用戶備份
能夠選擇性地備份用戶賬戶和權限，確保在恢復數據時，用戶的訪問權限也能一并恢復，避免因權限丟失導致的訪問問題，保障系統安全。
靈活過濾精準備份
提供了豐富的過濾選項，如--exclude-databases、--exclude-tables、--include-databases和--include-tables，可以精準選擇需要備份的對象，排除不必要的數據，提高備份效率。
使用指南
連接選項配置
連接MySQL數據庫時，常用選項包括--user（用戶名）、--password（密碼）、--host（主機名）、--port（端口號）和--socket（socket文件路徑），確保與數據庫建立正確連接。
備份選項設置
- 并行度設置：--default-parallelism=N用于設置默認并行度，--parallel-schemas=[N:]db_list可針對特定數據庫指定并行度。
- 事務與索引處理：--single-transaction確保事務一致性，--defer-table-indexes則延遲索引創建，加快數據加載速度。
- 數據篩選：利用--exclude-databases和--exclude-tables排除特定數據庫和表，--include-databases和--include-tables選擇備份特定對象。
- 其他選項：--users備份用戶賬戶，--skip-definer省略DEFINER子句，--skip-dump-rows只備份結構不備份數據。
輸出選項調整
--result-file=file_name指定備份輸出文件，--set-gtid-purged=value控制是否添加SET @@GLOBAL.GTID_PURGED語句，影響數據恢復時的GTID處理。
使用示例
部分庫表結構備份
假設業務中存在一些測試庫，僅需備份其表結構用于后續開發環境搭建，可利用--include-databases和--skip-dump-rows選項。例如有test_dev1和test_dev2兩個測試庫：
mysqlpump -u root -p --include-databases=test_dev1,test_dev2 --skip-dump-rows > test_dev_structure.sql其中old_data_table數據穩定無需備份
mysqlpump -u root -p test_dev1 --exclude-tables=old_data_table > test_dev_structure.sql提高并行度可以顯著加快備份速度
mysqlpump -u root -p --default-parallelism=8 --parallel-schemas=4:test_dev1,test_dev2 > large_backup.sql壓縮備份
mysqlpump -u root -p test_dev1 --compress-output=LZ4 > backup.lz4恢復備份
mysql -u root -p < test_dev_structure.sql對于壓縮備份，先解壓再恢復
lz4_decompress backup.lz4 backup.sql
mysql -u root -p < backup.sqlmysqlpump和mysqldump參數區別總匯：
功能 | mysqldump | mysqlpump | 說明 |
不導出表中的數據，只導出結構 | -d, --no-data | -d, --skip-dump-rows | |
輸出導入時的錯誤日志 | --log-error=name | -log-error-file=name | |
導出用戶和權限 | 無相關參數 | --users | |
排除對象 | 無相關參數 | --exclude-databases=name --exclude-events=name --exclude-routines=name --exclude-tables=name --exclude-triggers=name --exclude-users=name | |
指定包含的對象 | 無相關參數 | --include-databases=name --include-events=name --include-routines=name --include-tables=name --include-triggers=name --include-users=name | |
導出指定表 | --tables | --include-databases=name --include-tables=name | mysqldump：使用--tables參數跟庫名表名，表名之間空格隔開。mysqlpump：導出表需要同時使用--include-tables和--include-databases參數，如果只指定--include-tables=bm那么就會導出所有庫中bm表，表名之間逗號隔開。 |
insert插入包含多個值 | --extended-insert | --extended-insert=# | mysqlpump：定義一個insert語句包含多少個值，默認一個insert包含250個值。 |
導出時在CREATE TABLE前DROP TABLE IF EXISTS | --add-drop-table | --add-drop-table |
備份腳本示例
#!/bin/bash
# MySQLpump備份腳本
# 基礎配置
DB_HOST="192.168.0.1"
DB_PORT=3306
DB_USER="backup_user"
DB_PASS="xxxxxxxxxxx"
DATABASES="testdb"
BACKUP_DIR="/data/backup"
TS=$(date +%Y_%m_%d)
BACKUP_FILE="${BACKUP_DIR}/mysqlbak_${DATABASES}_${TS}.sql"
LOG_FILE="${BACKUP_DIR}/backup_${TS}.log"
PARALLEL_THREADS=4 # 設置并行線程數(根據CPU數，一般設置為CPU一半)
# 記錄開始時間
echo"[$(date +'%F %T')] 備份開始，使用 ${PARALLEL_THREADS} 個線程" | tee -a ${LOG_FILE}
# 執行備份命令
mysqlpump -h${DB_HOST} -u${DB_USER} -p"${DB_PASS}" -P${DB_PORT} \
--default-parallelism=${PARALLEL_THREADS} \
--parallel-schemas=${PARALLEL_THREADS}:${DATABASES} \
--single-transaction \
--set-gtid-purged=off \
--default-character-set=utf8mb4 \
--compress-output=LZ4 \
--defer-table-indexes \
--skip-definer \
--exclude-databases=mysql,sys,information_schema,performance_schema \
--log-error=${LOG_FILE} \
--result-file=${BACKUP_FILE} \
2>> ${LOG_FILE}
# 檢查結果
if [ $? -eq 0 ] && [ -f ${BACKUP_FILE} ]; then
# 獲取壓縮后的文件大小
BACKUP_SIZE=$(du -sh ${BACKUP_FILE} | awk '{print $1}')
echo"[$(date +'%F %T')] 備份成功，文件：${BACKUP_FILE} (大小: ${BACKUP_SIZE})" | tee -a ${LOG_FILE}
# 可選：解壓備份文件驗證完整性
# lz4_decompress ${BACKUP_FILE} ${BACKUP_FILE%.lz4}
# echo "[$(date +'%F %T')] 備份已解壓驗證" | tee -a ${LOG_FILE}
else
echo"[$(date +'%F %T')] 備份失敗！請檢查日志：${LOG_FILE}" >&2
exit 1
fi
