| 參數 | 作用 | 建議場景 |
|---|---|---|
--single-transaction | InnoDB 一致性備份不鎖表 (RR 隔離級別) | 線上業務庫必加 |
--master-data=2 | 輸出檔案含 Binlog 位點 (註釋形式)，利於從庫搭建 | 主從架構必加 |
--routines --triggers --events | 備份存儲過程/觸發器/事件 | 完整備份必加 |
--hex-blob | 二進位欄位用十六進位導出 | 含 BLOB/BINARY/GEOMETRY 必加 |
--quick | 逐行讀取不緩衝整個結果集 | 大表必加 (預設開啟) |
--lock-tables=false | 不鎖表 (配合 single-transaction) | 線上備份 |
--set-gtid-purged=OFF | 不輸出 GTID 資訊 (避免匯入衝突) | 跨實例遷移 |
--column-statistics=0 | 8.0+ 禁用統計資訊導出 (避免鎖表) | 8.0+ 線上備份 |
# 1. 單庫備份 (生產標準)
mysqldump -u root -p --single-transaction --routines --triggers --events \
--master-data=2 --hex-blob --quick \
mydb > mydb_$(date +%F_%H%M).sql
# 2. 全庫備份 (含系統帳號)
mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events \
--master-data=2 --hex-blob > full_$(date +%F_%H%M).sql
# 3. 壓縮備份 (管道流式，不佔磁碟臨時空間)
mysqldump -u root -p --single-transaction mydb | gzip > mydb_$(date +%F_%H%M).sql.gz
# 或並行壓縮 (多核加速)
mysqldump -u root -p --single-transaction mydb | pigz -p 4 > mydb_$(date +%F_%H%M).sql.gz
# 4. 單表備份
mysqldump -u root -p --single-transaction mydb t1 t2 > mydb_t1_t2_$(date +%F).sql
# 5. 僅結構 (Schema)
mysqldump -u root -p -d mydb > mydb_schema_$(date +%F).sql
# 6. 僅資料 (無建表語句)
mysqldump -u root -p -t mydb > mydb_data_$(date +%F).sql
# 7. 指定 Where 條件導出 (增量/分片)
mysqldump -u root -p --single-transaction --where="created_at > '2024-01-01'" mydb orders > orders_2024.sql# 1. 還原單庫 (需先建庫)
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4;"
mysql -u root -p mydb < mydb_2024-01-15.sql
# 2. 還原壓縮檔
gunzip -c mydb_2024-01-15.sql.gz | mysql -u root -p mydb
# 或
zcat mydb_2024-01-15.sql.gz | mysql -u root -p mydb
# 3. 還原全庫 (含 mysql 系統庫)
mysql -u root -p < full_2024-01-15.sql
# ⚠️ 還原 mysql 庫後必須執行
mysql -u root -p -e "FLUSH PRIVILEGES;"
# 4. 還原單表 (從全量檔提取)
# 方法 A: sed 提取 (小檔案)
sed -n '/^-- Table structure for table `t1`/,/^-- Table structure for table/p' full.sql > t1.sql
mysql -u root -p mydb < t1.sql
# 方法 B: 專用工具 (大檔案)
# mysql_utils / mydumper/myloader 支援平行匯入若備份檔不含目標表最新資料，需結合 Binlog：
# 1. 找到備份時間點對應的 Binlog 位置
head -30 mydb_backup.sql
# -- Position to start replication from: mysql-bin.000023 456789
# 2. 提取該位置後的 Binlog 事件 (僅目標表)
mysqlbinlog --start-position=456789 --database=mydb --table=t1 \
/var/lib/mysql/mysql-bin.000023 | mysql -u root -p mydb
# 3. 或使用 mysqlbinlog 導出 SQL 再審核執行
mysqlbinlog --start-position=456789 --database=mydb --table=t1 \
/var/lib/mysql/mysql-bin.000023 > t1_incremental.sql
# 審核後執行
mysql -u root -p mydb < t1_incremental.sqlmysql 系統庫 (含 user、db、tables_priv 等) 後，**必須執行 FLUSH PRIVILEGES;** 重新載入權限快取，否則新帳號不生效、舊帳號不失效mysql_upgrade 或手動同步帳號SET @@GLOBAL.gtid_purged='...' 避免 GTID 衝突mydumper (備份) + myloader (還原)，多執行緒、壓縮、Chunk 分片，速度提升 5-10 倍ALTER TABLE t DISABLE KEYS; (MyISAM) / 導入後 ALTER TABLE t ENABLE KEYS;；InnoDB 5.7+ 支援 innodb_disable_sort_file_cache 但效果有限innodb_buffer_pool_size、innodb_log_file_size、bulk_insert_buffer_size (MyISAM)SET GLOBAL innodb_doublewrite = OFF;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
-- 還原完成後恢復並重啟#!/bin/bash
set -euo pipefail
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
DATE=$(date +%F_%H%M)
DB_LIST=$(mysql -u root -p'xxx' -NBe "SHOW DATABASES WHERE \`Database\` NOT IN ('information_schema','performance_schema','sys')")
for DB in $DB_LIST; do
FILE="${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
mysqldump -u root -p'xxx' --single-transaction --routines --triggers --events \
--master-data=2 --hex-blob --quick "$DB" | pigz -p 4 > "$FILE"
# 驗證檔案非空
if [[ ! -s "$FILE" ]]; then
echo "ERROR: $FILE empty" | mail -s "Backup Failed" admin@example.com
exit 1
fi
done
# 清理舊備份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
# 上傳異地 (可選)
# rclone copy "$BACKUP_DIR" remote:mysql-backup/$(hostname)/
