mysqldump 備份排除指定庫/表的 3 種優雅姿勢與自動化腳本

痛點：--all-databases 無法排除庫，--ignore-table 僅能排除表

數十個庫逐個寫名字？太 Low。Linux 管道 + 文本處理輕鬆搞定。

核心思路

1. mysql -e 'SHOW DATABASES;' 列出所有庫
2. grep -v 過濾掉系統庫（information_schema、mysql、performance_schema、test）
3. xargs mysqldump --databases 批量備份剩餘庫

方案一：標準過濾（MySQL 5.5+ 需額外處理 performance_schema）

# 方法 A：備份時跳過 performance_schema
mysql -uroot -p123456 -e 'SHOW DATABASES;' | \
grep -Ev "Database|information_schema|mysql|test" | \
xargs mysqldump -uroot -p123456 --skip-lock-tables --databases > backup.sql

# 方法 B：列表階段直接過濾 performance_schema（推薦）
mysql -uroot -p123456 -e 'SHOW DATABASES;' | \
grep -Ev "Database|information_schema|mysql|performance_schema" | \
xargs mysqldump -uroot -p123456 --databases > backup.sql

方案二：生產級自動化備份腳本（每日定時）

#!/bin/bash
# Description: MySQL 每日全量備份（排除系統庫）
# Author: DBA Team

FILE="mysql_$(date +%F)"
BACKDIR="/mysqlbackup"
CONF_FILE="/etc/my.cnf"
MAIL_TO="dba@company.com"

# 核心備份邏輯
mysql -uroot -e 'SHOW DATABASES' | \
egrep -v "Database|information_schema|performance_schema" | \
xargs mysqldump -uroot --databases > "$BACKDIR/$FILE.sql" && \
cp "$CONF_FILE" "$BACKDIR/$FILE.cnf" && \
echo -e "Host: $(hostname)\nIP: $(hostname -I)\nStatus: MySQL backup completed" | \
mail -s "MySQL Backup Success - $FILE" "$MAIL_TO"

進階：排除指定表（同庫內）

場景：業務庫 spservice 含百張表，需排除日志類大表

#!/bin/bash
# 排除固定表名
EXCLUDE_TABLES=("mo_log" "mt_log" "report_info")

for tbl in $(mysql -uroot -e "USE spservice; SHOW TABLES" | grep -v Tables); do
    if [[ ! " ${EXCLUDE_TABLES[@]} " =~ " ${tbl} " ]]; then
        mysqldump -uroot --default-character-set=gbk --opt spservice "$tbl" > "${tbl}.sql"
    fi
done

更通用：命令列參數傳遞排除列表

#!/bin/bash
# 用法: ./exclude.sh mo_log mt_log report_log
declare -A exclude_map
for arg in "$@"; do exclude_map[$arg]=1; done

for tbl in $(mysql -uroot -e "USE spservice; SHOW TABLES" | grep -v Tables); do
    if [[ -z ${exclude_map[$tbl]} ]]; then
        mysqldump -uroot --default-character-set=gbk --opt spservice "$tbl" > "${tbl}.sql"
    fi
done

陣列寫法（最簡潔）

tables=(mo_log mt_log report_log)
./exclude.sh "${tables[@]}"

最佳實踐建議

• 備份輸出目錄掛載獨立磁碟，避免寫滿系統盤
• 配合 gzip 壓縮：... | gzip > backup.sql.gz
• 異地複製：rsync / scp 至備份伺服器
• 定期演練恢複：mysql -uroot -p < backup.sql 驗證可用性
• MySQL 8.0+ 建議加 --set-gtid-purged=OFF 避免 GTID 污染