Linux 下改良版 mysqldump 分表備份腳本：避開全局鎖、降低 CPU 與 I/O 衝擊

原版 mysqldump 痛點

• --lock-all-tables / --single-transaction 仍會在備份初期持有全局讀鎖或長事務。
• 大庫（5GB+）備份導致 CPU 飆升、磁盤 I/O 滿載、所有查詢變慢查詢。
• 混合引擎（MyISAM/InnoDB/MEMORY）無法單純用 mysqlhotcopy。

核心思路：表級別串行備份 + 存儲過程單獨導出

遍歷所有表，逐個調用 mysqldump 導出單表，無全局鎖，沖擊分散到時間軸上。

備份數據腳本 fast_backup

#!/bin/sh
DBNAME=$1
BACKUPDIR=/home/david_yeung/backup_new
USERNAME=backup_user
PASSWD=123456
TARNAME="${BACKUPDIR}/backup${1}$(date '+%Y%m%d')"

# 白名單校驗
case "$1" in
  my_site) ;;
  *) exit;;
esac

# 獲取表名列表（去除表頭尾幹擾行）
NUM=$(/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME | wc -l)
HEADNUM=$((NUM - 3))
TAILNUM=$((NUM - 7))
ARR1=$(/usr/local/mysql/bin/mysql -u$USERNAME -p$PASSWD -s -vv -e "show tables" -D $DBNAME | head -n"$HEADNUM" | tail -n"$TAILNUM")
ARR2=($ARR1)

i=0
while [ "$i" -lt "${#ARR2[@]}" ]; do
  tmpFileName=${ARR2[$i]}
  # 單表導出，追加寫入同一文件
  /usr/local/mysql/bin/mysqldump -u$USERNAME -p"$PASSWD" "$DBNAME" "$tmpFileName" >> "$TARNAME"
  let "i++"
done

存儲過程單獨備份腳本 fast_sp

#!/bin/sh
TARNAME=/home/david_yeung/backup_new/spBackup"$1"$(date '+%Y%m%d')
/usr/local/mysql/bin/mysqldump -ubackup_user -p123456 -n -t -d -R my_site > "$TARNAME"
# -n: 不創建庫 -t: 不導出數據 -d: 只導結構 -R: 導出存儲過程/函數

定時任務配置

# 每天 01:00 備份數據
0 1 * * * /home/david_yeung/fast_backup my_site

# 每周五 00:00 備份存儲過程
0 0 * * 5 /home/david_yeung/fast_sp

優缺點總結

進階優化建議

• 大表拆分：按主鍵範圍分多段 WHERE id BETWEEN ... 導出並行。
• 配合 --single-transaction 對 InnoDB 表單表一致性視圖。
• 備份完成後校驗：mysqldump --no-data 對比結構、CHECKSUM TABLE 對比數據。
• 生產環境強烈建議：主從架構，在從庫備份，徹底消除主庫影響。