您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

mysqldump 備份與還原完整指令手冊：結構、資料、指定表、遠程操作全攻略

編輯：MySQL 資料庫相關知識

mysqldump 是 MySQL 官方提供的邏輯備份工具，支援完整庫、單表、僅結構、僅資料、遠程備份等多種場景。本文整理常用指令格式與實戰範例，涵蓋從基礎備份到自動化腳本的完整用法，適合 DBA 與開發者速查參考。

基本語法格式

mysqldump [options] db_name [tbl_name ...] > backup.sql
mysqldump [options] --databases db_name1 [db_name2 ...] > backup.sql
mysqldump [options] --all-databases > backup.sql

核心參數說明

參數	說明
`-u, --user`	使用者名稱
`-p, --password`	密碼（建議不帶值，互動式輸入）
`-h, --host`	主機位址（預設 localhost）
`-P, --port`	連接埠（預設 3306）
`-d, --no-data`	僅匯出結構（不含資料）
`-t, --no-create-info`	僅匯出資料（不含 CREATE TABLE）
`--single-transaction`	InnoDB 熱備份必備，啟動事務確保一致性
`--routines`	包含預存程序/函數
`--triggers`	包含觸發器
`--events`	包含排程事件
`--master-data=2`	附加 CHANGE MASTER TO 註解，適合主從同步基準點
`--hex-blob`	二進位欄位以十六進位匯出
`--compress`	壓縮傳輸（遠程備份建議開啟）

常用實戰範例

1. 完整資料庫備份（結構+資料）

mysqldump -u root -p --single-transaction --routines --triggers --events db_name > db_full_$(date +%F).sql

2. 僅匯出表結構

mysqldump -u root -p -d db_name > db_structure.sql

3. 指定單表/多表備份

# 單表
mysqldump -u root -p db_name table1 > table1.sql

# 多表（結構+資料）
mysqldump -u root -p db_name table1 table2 table3 > tables_subset.sql

# 多表（僅結構）
mysqldump -u root -p -d db_name table1 table2 table3 > tables_structure.sql

4. 使用 --tables 明確指定表（避免被解析為資料庫名）

mysqldump -u root -p --tables db_name table1 table2 > out.sql

5. 遠程伺服器備份

mysqldump -h 192.168.1.100 -u root -p --single-transaction db_name | gzip > db_remote_$(date +%F).sql.gz

6. Windows CMD 環境下備份


cd C:\Program Files\MySQL\MySQL Server 5.7\bin
mysqldump -u root -p db_name > D:\backups\db_%date:~,4%%date:~5,2%%date:~8,2%.sql

還原操作

1. 完整還原


mysql -u root -p db_name < backup.sql

2. 僅還原結構


mysql -u root -p db_name < structure.sql

3. 指定字元集還原（防止亂碼）


mysql -u root -p --default-character-set=utf8mb4 db_name < backup.sql

常用查看指令速查


SHOW DATABASES;              -- 查看所有資料庫
USE db_name;                 -- 切換資料庫
SHOW TABLES;                 -- 查看資料表
DESCRIBE table_name;         -- 查看表結構
SHOW CREATE TABLE table_name; -- 查看建表 SQL

自動化備份腳本範例


#!/bin/bash
# daily_backup.sh
BACKUP_DIR="/backup/mysql"
DB_USER="backup_user"
DB_PASS="secure_password"
DB_NAME="production_db"
DATE=$(date +%F_%H-%M-%S)

mkdir -p $BACKUP_DIR
mysqldump -u$DB_USER -p$DB_PASS --single-transaction --routines --triggers --events $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 保留 7 天
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

加入 crontab：0 2 * * * /scripts/daily_backup.sh 每天凌晨 2 點自動備份。

避坑提醒

不要在生產庫直接執行無 --single-transaction 的備份，會鎖表阻塞寫入
備份檔務必定期驗證還原測試，未測試的備份等於沒備份
生產環境建議建立專用備份帳號，僅授予 SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD, REPLICATION CLIENT 權限
大資料庫建議搭配 Percona XtraBackup 進行物理熱備份，速度更快、對業務影響更小