Linux 下 MySQL 邏輯備份與恢復實戰：mysqldump 核心參數、Lock Tables 報錯解決、單表恢復技巧

核心備份命令模板

# 完全備份 (含所有庫、例程、觸發器、事件、Binlog 位置、事務一致性)
mysqldump -u root -p \
  --single-transaction \
  --master-data=2 \
  --routines --triggers --events \
  --hex-blob \
  --all-databases > /backup/full_$(date +%F_%H-%M).sql

# 單庫備份
mysqldump -u root -p --single-transaction --master-data=2 --databases db_name > /backup/db_name_$(date +%F).sql

# 單表備份
mysqldump -u root -p --single-transaction --master-data=2 db_name table_name > /backup/table_name_$(date +%F).sql

# 只導出結構 (不含數據)
mysqldump -u root -p --no-data --databases db_name > /backup/structure_db_name_$(date +%F).sql

關鍵參數深度解析

常見報錯：mysqldump: Got error: 1016 when using LOCK TABLES

原因：表數量極多 (如 WordPress MU 數萬表)，FLUSH TABLES WITH READ LOCK 耗時極長或因表損壞/權限失敗。

解決：添加 --lock-tables=false (或 --skip-lock-tables) 配合 --single-transaction，跳過表鎖，依賴 MVCC 快照一致性。

mysqldump -u root -p --single-transaction --lock-tables=false --databases huge_db > /backup/huge_db.sql

注意：若庫含 MyISAM 表，關閉鎖表將無法保證 MyISAM 表一致性。建議將 MyISAM 表轉為 InnoDB。

恢復實戰

方式一：客戶端管道導入 (最常用)

# 進入 MySQL 交互式
mysql -u root -p
# 執行
SOURCE /backup/full_2023-10-01.sql;

方式二：Shell 管道導入 (腳本自動化首選)

mysql -u root -p < /backup/full_2023-10-01.sql
# 指定目標庫 (備份文件含 CREATE DATABASE)
mysql -u root -p target_db < /backup/db_name.sql

方式三：大文件優化導入 (關鍵加速參數)

mysql -u root -p --force target_db < /backup/huge.sql
# 會話級優化 (源文件頭部建議包含)
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
SET AUTOCOMMIT=0;
SET sql_log_bin=0;  -- 不寫 Binlog，極大加速
-- 導入結束後
COMMIT;
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
SET AUTOCOMMIT=1;
SET sql_log_bin=1;

單表恢復技巧 (從全量備份中提取單表)

1. sed/awk 提取 (適用中小文件)：

   
   sed -n '/^-- Current Database: `db_name`/,/^-- Current Database: /p' full.sql | \
   sed -n '/^CREATE TABLE `target_table`/,/^CREATE TABLE /p' > target_table.sql
   # 手動檢查文件完整性 (以 DROP TABLE 開頭，以 UNLOCK TABLES/下一個 CREATE TABLE 結尾)
   

2. 專用工具：mysqldump --tables db_name target_table 重新導出 (需主庫可連)；或用 mysqlbinlog 解析 Binlog 提取單表 DML (複雜)。
3. 異機恢復驗證**：恢復到測試庫 → 校驗行數/校驗和 → 導出單表 SQL → 生產庫導入。

備份驗證鐵律 (未驗證 = 無備份)

1. 文件完整性：tail -5 backup.sql 應以 -- Dump completed on ... 結尾。
2. 校驗和對比：SELECT CHECKSUM TABLE t; 主備對比。
3. 定期演練：月度/季度在測試環境全量恢復，計時驗證 RTO。
4. 異地存儲：自動同步至對象存儲 (S3/OSS/COS) + 異地機房。