您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 命令行備份與恢復完整指南：mysqldump 實戰技巧與跨平台操作

編輯：MySQL 綜合教學

MySQL 命令行備份恢復實戰：擺脫 phpMyAdmin 超時限制，掌握生產級操作技能

Web 介面備份工具（如 phpMyAdmin）受限於 HTTP 超時、內存限制，大數據庫操作極易失敗。本文系統講解 Linux/Windows 命令行下 `mysqldump` 備份與恢復的標準流程、壓縮優化、自動化腳本及跨平台注意事項。

核心命令結構

mysqldump -u 用戶名 -p 數據库名 [表名...] > 備份文件.sql
mysql -u 用戶名 -p 數據库名 < 備份文件.sql

Linux 環境完整操作指南

1. 基礎備份（未壓縮）

# 備份單庫
mysqldump -u mysqluser -p test_db > test_db.sql

# 備份多庫
mysqldump -u mysqluser -p --databases db1 db2 > multi_db.sql

# 備份全庫（含系統庫）
mysqldump -u mysqluser -p --all-databases > full_backup.sql

2. 壓縮備份（生產環境必用）

# 管道壓縮（推薦：節省磁盤、網絡傳輸快）
mysqldump -u mysqluser -p test_db | gzip > test_db_$(date +%Y%m%d_%H%M).sql.gz

# 或者使用 pigz 多核並行壓縮（大數據庫顯著加速）
mysqldump -u mysqluser -p test_db | pigz -p 4 > test_db_$(date +%Y%m%d_%H%M).sql.gz

3. 恢復操作

# 未壓縮文件恢復
mysql -u mysqluser -p test_db < test_db.sql

# 壓縮文件直接恢復（無需先解壓）
gunzip < test_db.sql.gz | mysql -u mysqluser -p test_db

# 或 zcat（部分系統）
zcat test_db.sql.gz | mysql -u mysqluser -p test_db

4. 進階參數（生產環境標準配置）

mysqldump -u mysqluser -p \
  --single-transaction \        # InnoDB 一致性非阻塞
  --master-data=2 \             # 記錄 Binlog 位點
  --routines --triggers --events \  # 存儲過程/觸發器/事件
  --hex-blob \                  # 二進制字段十六進制
  --quick \                     # 流式讀取，防大表 OOM
  --databases test_db > test_db_$(date +%F).sql

Windows 環境操作指南

1. 進入 MySQL Bin 目錄

cmd
cd "C:\Program Files\MySQL\MySQL Server 5.7\bin"
# 或根據實際安裝路徑調整

2. 備份命令

# 導出到指定文件
mysqldump -u root -p voice > D:\backup\voice_%date:~,4%%date:~5,2%%date:~8,2%.sql
# 輸入密碼後開始導出

3. 恢復步驟

啟動 MySQL Command Line Client，輸入密碼進入 `mysql>` 提示符
查看現有庫：`SHOW DATABASES;`
創建目標庫：`CREATE DATABASE voice;`
切換庫：`USE voice;`
導入數據：`SOURCE D:\backup\voice_20240115.sql;`
等待完成，無報錯即成功

大數據庫優化策略（>10GB）

痛點	優化方案	工具/命令
導出耗時過長	並行導出	mydumper (多線程)、mysqldump 分表導出
導入極慢	禁用索引/外鍵、批量提交	導入前 SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; SET AUTOCOMMIT=0;
文件過大	分卷壓縮、增量備份	split -b 1G backup.sql.gz、xtrabackup 增量
跨版本遷移	兼容性檢查、字符集統一	mysql_upgrade、--default-character-set=utf8mb4

自動化備份腳本（Linux Crontab 部署）

#!/bin/bash
# /opt/scripts/mysql_daily_backup.sh
# 每日 02:00 執行：0 2 * * * /opt/scripts/mysql_daily_backup.sh >> /var/log/mysql_backup.log 2>&1

DB_USER="backup_user"
DB_PASS="StrongPassword123"
BACKUP_BASE="/backup/mysql"
DATE_STR=$(date +%Y%m%d_%H%M)
RETENTION_DAYS=30

mkdir -p ${BACKUP_BASE}/${DATE_STR}

# 核心業務庫單獨備份（便於單庫恢復）
for db in core_db user_db order_db; do
    mysqldump -u${DB_USER} -p${DB_PASS} \
        --single-transaction --master-data=2 \
        --routines --triggers --events \
        --hex-blob --quick \
        ${db} | pigz -p 4 > ${BACKUP_BASE}/${DATE_STR}/${db}.sql.gz
done

# 全庫備份（災難恢復用）
mysqldump -u${DB_USER} -p${DB_PASS} \
    --single-transaction --master-data=2 \
    --routines --triggers --events \
    --all-databases | pigz -p 4 > ${BACKUP_BASE}/${DATE_STR}/full_instance.sql.gz

# 清理舊備份
find ${BACKUP_BASE} -type f -name "*.gz" -mtime +${RETENTION_DAYS} -delete

# 異地同步（可選）
# rsync -avz ${BACKUP_BASE}/ offsite_server:/offsite/mysql_backup/