將資料表導出為 SQL 腳本,跨版本升級相容性佳,最常用。
| 參數 | 說明 |
|---|---|
--compatible=name | 指定相容模式:ansi、mysql323、mysql40、postgresql、oracle 等,多值用逗號分隔。 |
--complete-insert, -c | 完整 INSERT(含欄位名),提高插入效率,但可能受 max_allowed_packet 限制。 |
--default-character-set=charset | 關鍵:非 latin1 編碼資料庫必須指定,否則還原產生亂碼。 |
--disable-keys | 在 INSERT 前後加 ALTER TABLE ... DISABLE/ENABLE KEYS,批量插入後再建索引,大幅提速(僅 MyISAM)。 |
--extended-insert=false | 關閉預設的多行 INSERT,改用單行 INSERT,便於除錯與相容。 |
--hex-blob | 二進制欄位(BINARY、VARBINARY、BLOB)以十六進制導出,避免編碼問題。 |
--lock-all-tables, -x | 全域讀鎖,保證一致性,自動關閉 --single-transaction。 |
--lock-tables | 鎖定當前導出的表(MyISAM 適用),InnoDB 請用 --single-transaction。 |
--no-create-info, -t | 僅導出資料,不含 CREATE TABLE。 |
--no-data, -d | 僅導出結構,不含資料。 |
--opt | 快捷選項組合(預設開啟),含 --add-drop-tables --disable-keys --extended-insert --lock-tables --quick --set-charset 等。 |
--quick, -q | 大表必備:直接輸出查詢結果,不緩存到記憶體。 |
--routines, -R | 導出預存程序與自定義函數。 |
--single-transaction | InnoDB 專用:啟動事務導出,不阻塞寫入,保證一致性視圖。需搭配 --quick。 |
--triggers | 導出觸發器(預設開啟)。 |
# MyISAM 表
mysqldump -uuser -ppass --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob -x db_name > db_name.sql
# InnoDB 表
mysqldump -uuser -ppass --default-character-set=utf8 --opt --extended-insert=false --triggers -R --hex-blob --single-transaction db_name > db_name.sql
mysql -uuser -ppass db_name < db_name.sqlSOURCE /absolute/path/db_name.sql;(需 mysqld 執行用戶有讀取權限)LOCK TABLES、FLUSH TABLES + cp/scp。SELECT、RELOAD 權限及資料目錄讀取權限。mysqlhotcopy -h=localhost -u=user -p=pass db_name /backup/dir
mysqlhotcopy -h=localhost -u=user -p=pass db_name_1 db_name_2 /backup/dir
mysqlhotcopy -h=localhost -u=user -p=pass db_name./regex/ /backup/dir
cp -rf db_name /var/lib/mysql/
chown -R mysql:mysql /var/lib/mysql/db_name
.frm、.MYD,不含索引檔,還原需重建索引,較慢。BACKUP TABLE tbl_name TO '/path/'; / RESTORE TABLE tbl_name FROM '/path/';FILE 權限,目標目錄需 mysqld 可寫,檔案不可覆蓋。SELECT * INTO OUTFILE '/path/tbl.txt' FROM tbl_name;LOAD DATA INFILE '/path/tbl.txt' INTO TABLE tbl_name [REPLACE|IGNORE];FILE 權限,檔案不可覆蓋,表需預先存在。my.cnf:
[mysqld]
server-id = 1
log-bin = binlog
log-bin-index = binlog.index
binlog.000001、binlog.index。FLUSH LOGS; -- 切換新 binlog,舊檔案即為增量備份
從庫還需備份 master.info、relay-log.info。
# 查看
mysqlbinlog /path/binlog.000001
# 還原
mysqlbinlog /path/binlog.000001 | mysql -uuser -ppass db_name
採用「主從複制」,在從庫執行備份:低成本、不影響主庫效能、支援增量備份、分擔查詢壓力。
FLUSH TABLES WITH READ LOCK; -- 刷新記憶體到磁碟,鎖定所有表
# 拷貝整個 datadir 目錄
# InnoDB 還需備份 ib_logfile*(崩潰恢復依賴)
UNLOCK TABLES;
| 系統等級 | 策略 |
|---|---|
| 中等業務量 | 首次全量 → 每日增量 → 每周全量 |
| 核心高併發系統 | 每日全量 → 每小時增量 → 甚至更頻繁 |
最佳實踐:主從複制 + 從庫備份,實現線上無鎖增量備份。
CHECK TABLE / REPAIR TABLE:檢查/修復OPTIMIZE TABLE:碎片整理ANALYZE TABLE:更新統計資訊myisamchk 工具完成。ALTER TABLE tbl_name ENGINE = InnoDB;
看似無操作,實則重建表空間、整理碎片、提升索引速度。
ib_logfile* 重做日誌。my.cnf 設定 innodb_force_recovery = 4 可強制啟動並嘗試修復(參考手冊調整等級)。