mysqldump 是邏輯備份標準工具，支援全量、單表、結構、壓縮、遠程傳輸等多種模式。熟練掌握參數組合，應對各類備份場景。
| 需求 | 命令範例 | 關鍵參數說明 |
|---|---|---|
| 單庫完整備份 (含資料+結構) | mysqldump -h host -u user -p db_name > backup.sql | 預設包含 CREATE TABLE、INSERT |
| 單庫備份 + DROP TABLE (便於直接覆蓋還原) | mysqldump --add-drop-table -u user -p db_name > backup.sql | --add-drop-table 在 CREATE 前加 DROP |
| 單庫備份並壓縮 (節省空間/傳輸) | mysqldump -h host -u user -p db_name | gzip > backup.sql.gz | 管道導向 gzip |
| 備份指定表 | mysqldump -h host -u user -p db_name tbl1 tbl2 > backup.sql | 表名空格分隔 |
| 同時備份多個庫 | mysqldump -h host -u user -p --databases db1 db2 db3 > multi.sql | --databases 多庫模式 |
| 僅備份結構 (無資料) | mysqldump --no-data --databases db1 db2 > struct.sql | --no-data / -d |
| 備份所有庫 (含系統庫) | mysqldump --all-databases > all.sql | --all-databases / -A |
| 帶條件備份 (如僅備份近 30 天) | mysqldump -u user -p db_name tbl --where="created_at > DATE_SUB(NOW(), INTERVAL 30 DAY)" > part.sql | --where 過濾條件 |
| 場景 | 命令 | 說明 |
|---|---|---|
| 標準還原 | mysql -h host -u user -p db_name < backup.sql | 需先建立目標資料庫 |
| 還原壓縮備份 | gunzip < backup.sql.gz | mysql -u user -p db_name | 解壓管道導入 |
| 跨伺服器直接遷移 (無落地檔案) | mysqldump -u user -p db_name | mysql --host=target_ip -C db_name | -C 啟用壓縮傳輸 |
| 進入 MySQL 後用 source 還原 | mysql> USE db_name; SOURCE /path/backup.sql; | 適合交互式、大檔案分批 |
mydumper (多線程) 或 mysqldump --single-transaction --quick --lock-tables=false 減少鎖爭用。SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 還原後再開啟；或用 LOAD DATA INFILE 導入 CSV。#!/bin/bash
DATE=$(date +%F_%H-%M)
mysqldump -u backup_user -p'password' --single-transaction --routines --triggers --events \
--databases db1 db2 | gzip > /backup/mysql_${DATE}.sql.gz
# 保留 7 天
find /backup -name "mysql_*.sql.gz" -mtime +7 -deletemysql < backup.sql 驗證還原成功、資料完整 (行數、檢查和)。| 參數 | 短參數 | 作用 |
|---|---|---|
| --single-transaction | - | InnoDB 一致性備份 (不鎖表)，需開啟事務隔離 REPEATABLE READ |
| --quick | -q | 不緩衝查詢結果直接輸出，適合大表 |
| --lock-tables | -l | 鎖定所有表 (MyISAM 必須)，預設開啟；InnoDB 配合 single-transaction 應關閉 |
| --routines | -R | 包含存儲過程/函數 |
| --triggers | - | 包含觸發器 |
| --events | -E | 包含事件調度器 |
| --master-data=2 | - | 輸出 CHANGE MASTER TO 註釋 (位置 2 為註釋)，用於主從搭建 |
| --hex-blob | - | 二進制欄位用十六進制輸出，避免編碼問題 |
| --default-character-set | - | 指定連線字符集 (如 utf8mb4) |
