萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 備份與還原全攻略:mysqldump 參數詳解與 Shell 自動化腳本

核心工具:mysqldump 參數速查

參數作用推薦場景
--single-transactionInnoDB 熱備份,一致性無鎖生產環境 InnoDB 表
--lock-all-tables全域鎖表 (MyISAM 必須)MyISAM/混合引擎
--flush-logs切換 binlog,增量備份基點配合增量備份
--master-data=2輸出 CHANGE MASTER 註釋 (binlog 位置)主從搭建/時間點恢復
--hex-blob二進位欄位十六進位導出,防亂碼含 BLOB/BINARY/JSON 欄位
--default-character-set指定導出編碼跨編碼環境遷移
-d / --no-data僅導出結構Schema 遷移/審計
--add-drop-tableCREATE 前加 DROP TABLE覆蓋式導入
--routines --triggers --events導出存儲過程/觸發器/事件完整遷移

常用導出命令範例

# 1. 完整備份 (InnoDB 生產標準)
mysqldump -u root -p --single-transaction --flush-logs --master-data=2 \
  --hex-blob --routines --triggers --events --default-character-set=utf8mb4 \
  mydb > mydb_full_$(date +%F).sql

# 2. 單表備份
mysqldump -u root -p mydb orders > orders_$(date +%F).sql

# 3. 僅結構
mysqldump -u root -p -d --add-drop-table mydb > schema_$(date +%F).sql

# 4. 遠程備份 (壓縮傳輸)
mysqldump -h 10.0.0.5 -u backup -p --single-transaction mydb | gzip > mydb_$(date +%F).sql.gz

導入/還原方式

# 方式一:mysql 客戶端 source (適合小檔)
mysql -u root -p mydb < backup.sql
# 或進入 mysql 後
SOURCE /path/backup.sql;

# 方式二:管道直導 (大檔/遠程)
gunzip < backup.sql.gz | mysql -u root -p mydb

# 方式三:多線程並行導入 (需 mydumper/myloader 或自行分表並行)

Shell 自動化備份腳本 (生產就緒版)

#!/bin/bash
# backup_mysql.sh - 每日全量備份 + 定期清理
# 部署:chmod +x backup_mysql.sh && crontab -e → 0 2 * * * /path/backup_mysql.sh

set -euo pipefail

# ===== 配置區 =====
DB_HOST="127.0.0.1"
DB_USER="backup_user"
DB_PASS="StrongPass#2024"
DB_NAMES=("app_db" "log_db" "report_db")  # 多庫陣列
BACKUP_ROOT="/data/backup/mysql"
RETENTION_DAYS=14
COMPRESS=true
# ===================

DATE=$(date +%F)
TIME=$(date +%H%M)
BAK_DIR="${BACKUP_ROOT}/${DATE}"
mkdir -p "${BAK_DIR}"

log() { echo "[$(date '+%F %T')] $*"; }

for DB in "${DB_NAMES[@]}"; do
    FILE="${BAK_DIR}/${DB}_${TIME}.sql"
    log "開始備份 ${DB} → ${FILE}"
    
    if mysqldump -h"${DB_HOST}" -u"${DB_USER}" -p"${DB_PASS}" \
        --single-transaction --flush-logs --master-data=2 \
        --hex-blob --routines --triggers --events \
        --default-character-set=utf8mb4 "${DB}" > "${FILE}"; then
        
        if ${COMPRESS}; then
            gzip "${FILE}"
            log "${DB} 壓縮完成:${FILE}.gz"
        fi
    else
        log "❌ ${DB} 備份失敗!"
        exit 1
    fi
done

# 清理舊備份
log "清理 ${RETENTION_DAYS} 天前備份..."
find "${BACKUP_ROOT}" -type f -mtime +${RETENTION_DAYS} -name "*.sql*" -delete
log "✅ 備份任務完成"

增量備份策略 (基於 Binlog)

  1. 啟用 Binlog:log-bin=mysql-bin、binlog_format=ROW、expire_logs_days=7。
  2. 全量備份時 --flush-logs --master-data=2 記錄基點 (如 mysql-bin.000042 pos 154)。
  3. 每日/每小時 mysqladmin flush-logs 切換新 binlog,備份舊 binlog 檔案。
  4. 恢復:先導入全量 SQL,再按序重放 binlog:mysqlbinlog mysql-bin.000042 mysql-bin.000043 | mysql -u root -p。

避坑提醒

  • 臨時表:Binlog 恢復需在同一 Session 重放跨檔案的臨時表事務。
  • CREATE TABLE ... SELECT:Statement 格式 Binlog 僅記語句不記資料,Row 格式無此問題。
  • 權限最小化:備份帳號僅需 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT。
  • 異地備份:配合 rsync/s3cp 推送至異地存儲,防單點災難。

建立「全量+增量+異地+驗證」四位一體備份體系,資料安全無憂。

copyright © 萬盛學電腦網 all rights reserved