MySQL mysqldump 實戰演練：備份與恢復全流程詳解

實驗環境與目標

本次實驗在 MySQL 5.0.85 環境下進行，目標資料庫為 test，包含 sms 和 email 兩張表。實驗內容涵蓋完整庫備份、單表備份、結構與數據分離導出，以及異機恢復的完整流程。

一、完整資料庫備份

# mysqldump -uroot -p test > /mnt/zbtest.sql

備份生成的 SQL 文件主要包含兩部分：建表語句（DDL）與插入語句（DML）。文件頭部包含字符集、時區、外鍵檢查等會話變量設置，確保恢復時環境一致。

二、備份文件結構解析

通過 vi /mnt/zbtest.sql 查看，典型結構如下：

• 版本註釋與會話變量保存/設置（SET @OLD_CHARACTER_SET_CLIENT 等）
• 表結構定義：DROP TABLE IF EXISTS + CREATE TABLE
• 數據導入：LOCK TABLES、ALTER TABLE ... DISABLE KEYS、批量 INSERT、ENABLE KEYS、UNLOCK TABLES

此結構設計可最大化導入效率：禁用索引更新、批量寫入、最後重建索引。

三、異機恢復實戰與常見坑點

1. 直接導入報錯

# mysql -u root -pchangyou.com zbdump < /mnt/zbtest.sql
ERROR 1049 (42000): Unknown database 'zbdump'

原因：目標資料庫 zbdump 不存在。mysqldump 預設不包含 CREATE DATABASE 語句（除非使用 -B/--databases 參數）。

2. 正確恢復步驟

mysql> CREATE DATABASE zbdump;
mysql> exit
# mysql -u root -pchangyou.com zbdump < /mnt/zbtest.sql

3. 驗證恢復結果

mysql> USE zbdump;
mysql> SHOW TABLES;
+------------------+
| Tables_in_zbdump |
+------------------+
| email            |
| sms              |
+------------------+

mysql> SELECT COUNT(*) FROM email;  -- 1,600,000 行
mysql> SELECT COUNT(*) FROM sms;    --   250,000 行

表結構、行數與原庫完全一致，恢復成功。

四、進階備份場景

1. 單表結構與數據備份

# mysqldump -uroot -p --tables zbdump email > /mnt/zbdump_email.sql

2. 表結構與數據分離導出（-T 參數）

# mysqldump -uroot -p -T /tmp zbdump sms

生成兩個文件：

• sms.sql：建表語句
• sms.txt：純文本數據（Tab 分隔），適合 LOAD DATA INFILE 高速導入

注意：-T 要求 mysqldump 與 mysqld 運行在同一台機器，且導出目錄需 mysql 用戶寫入權限。

3. 僅備份表結構（-d/--no-data）

# mysqldump -uroot -p -d zbdump email > email_struct.sql
# mysqldump -uroot -p -d zbdump sms email > /tmp/zbdump_struct.sql

4. 結構恢復

# mysql -uroot -p test < /tmp/zbdump_struct.sql

五、核心參數速查表

六、最佳實踐建議

1. 生產環境必加 --single-transaction --master-data=2，實現無鎖一致性備份並記錄 Binlog 位點。
2. 大表優先使用 -T 分離導出，配合 LOAD DATA INFILE 恢復，速度提升 5-10 倍。
3. 定期驗證備份可用性：建立自動化腳本定期恢復到測試環境，檢查數據完整性。
4. 壓縮傳輸：mysqldump ... | gzip > backup.sql.gz 節省帶寬與存儲。