| 參數 | 作用 | 推薦場景 |
|---|---|---|
--single-transaction | InnoDB 熱備份,一致性無鎖 | 生產環境 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-table | CREATE 前加 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 或自行分表並行)
#!/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 "✅ 備份任務完成"
log-bin=mysql-bin、binlog_format=ROW、expire_logs_days=7。--flush-logs --master-data=2 記錄基點 (如 mysql-bin.000042 pos 154)。mysqladmin flush-logs 切換新 binlog,備份舊 binlog 檔案。mysqlbinlog mysql-bin.000042 mysql-bin.000043 | mysql -u root -p。CREATE TABLE ... SELECT:Statement 格式 Binlog 僅記語句不記資料,Row 格式無此問題。SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT。建立「全量+增量+異地+驗證」四位一體備份體系,資料安全無憂。