萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 命令列匯出匯入資料庫實戰:mysqldump 與 SOURCE 指令完整流程

場景:資料庫遷移/備份/版本升級測試

一、匯出 (mysqldump)

進入 bin 目錄 (Windows 範例)


cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
# Win7+ 若提示拒絕存取,請以「系統管理員身分執行」命令提示字元

1. 匯出整個資料庫 (結構+資料)


mysqldump -u root -p atm > atm.sql
# 提示輸入密碼,完成後在 bin 目錄產生 atm.sql

2. 匯出單一資料表


mysqldump -u root -p atm account > atm_account.sql

3. 僅匯出結構 (無資料)


mysqldump -u root -p -d atm > atm_structure.sql

4. 遠程伺服器匯出


mysqldump -h 192.168.1.100 -u root -p atm > atm_remote.sql

5. 壓縮輸出 (管道)


mysqldump -u root -p atm | gzip > atm_$(date +%F).sql.gz

二、匯入

方法 A:mysql 客戶端互動模式 (SOURCE)


# 1. 登入
mysql -u root -p

# 2. 建立目標資料庫 (指定編碼防亂碼)
mysql> CREATE DATABASE test CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql> USE test;

# 3. 設定連線編碼 (匹配 SQL 檔編碼)
mysql> SET NAMES utf8mb4;

# 4. 匯入
mysql> SOURCE atm.sql;
-- 或絕對路徑: SOURCE D:/Backup/atm.sql;

# 5. 驗證
mysql> SHOW TABLES;
mysql> SELECT COUNT(*) FROM account;

方法 B:管道導入 (非互動、腳本自動化首選)


# Windows
mysql -u root -p test < atm.sql

# Linux/macOS
mysql -u root -p test < atm.sql
# 免密 (配置 ~/.my.cnf [client] password=xxx)
mysql -u root test < atm.sql

# 壓縮檔直接導入
gunzip < atm.sql.gz | mysql -u root -p test

三、常見問題排查

錯誤/現象原因解決
`ERROR 1049 (42000): Unknown database 'test'`目標資料庫不存在先 `CREATE DATABASE test;` 再導入
`ERROR 2006: MySQL server has gone away`封包過大/超時增大 `max_allowed_packet` (客戶端 `--max-allowed-packet=1G`、伺服器 `my.cnf`)
中文亂碼 / `Incorrect string value`編碼不匹配匯入前 `SET NAMES utf8mb4`;建庫/表指定 `utf8mb4`
`Access denied for user 'root'@'localhost'`權限/密碼錯誤確認帳號、密碼、主機授權 `GRANT ... @'%'`
外鍵約束失敗建表順序/資料順序違反 FK匯出加 `--single-transaction` / 導入前 `SET foreign_key_checks=0;` 後恢復

自動化備份腳本範例 (Linux Cron)


#!/bin/bash
# /usr/local/bin/backup_mysql.sh
DB_USER="backup_user"
DB_PASS="StrongPass"
DB_NAME="prod_db"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%F_%H%M)
FILE="${DB_NAME}_${DATE}.sql.gz"

mkdir -p "$BACKUP_DIR"
mysqldump -u"$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers --hex-blob --quick --default-character-set=utf8mb4 "$DB_NAME" | gzip > "${BACKUP_DIR}/${FILE}"

# 保留 30 天
find "$BACKUP_DIR" -name "${DB_NAME}_*.sql.gz" -mtime +30 -delete

# 上傳至遠端儲存 (可選)
# rclone copy "$BACKUP_DIR" remote:mysql-backup/

加入 crontab:`0 2 * * * /usr/local/bin/backup_mysql.sh >> /var/log/mysql_backup.log 2>&1`

copyright © 萬盛學電腦網 all rights reserved