MySQL 命令列進階操作：資料庫/表管理、資料匯入匯出、備份還原全攻略

MySQL 命令列進階操作實戰手冊

掌握命令列操作是 DBA 與開發者的核心技能。本文系統整理資料庫/表管理、資料操作、匯入匯出、備份還原、文字檔匯入五大場景的標準指令與最佳實務。

一、操作技巧基礎

• 分行輸入： 未輸入 ; 直接換行，提示符變 ->，補 ; 送出
• 取消指令： \c 取消當前多行輸入
• 歷史指令： 上/下鍵瀏覽，history 列表
• 垂直顯示： \G 替代 ;
• 執行外部檔： SOURCE /path/file.sql 或 mysql -u root -p db < file.sql

二、資料庫與表管理 (DDL)

資料庫操作

-- 列出所有資料庫
SHOW DATABASES;

-- 建立資料庫 (指定字元集)
CREATE DATABASE business_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 切換資料庫
USE business_db;

-- 刪除資料庫
DROP DATABASE IF EXISTS old_db;

表操作

-- 列出當前資料庫所有表
SHOW TABLES;

-- 查看表結構
DESCRIBE users;
-- 或
SHOW CREATE TABLE users\G

-- 建表示例 (InnoDB + utf8mb4)
CREATE TABLE users (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  password_hash CHAR(60) NOT NULL COMMENT 'bcrypt hash',
  status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=active,0=inactive',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 刪除表
DROP TABLE IF EXISTS temp_table;

-- 清空表資料 (保留結構、重置 AUTO_INCREMENT)
TRUNCATE TABLE logs;

三、資料操作 (DML)

-- 插入單筆
INSERT INTO users (username, email, password_hash) VALUES ('john', 'john@example.com', '$2b$12$...');

-- 批次插入 (高效)
INSERT INTO users (username, email, password_hash) VALUES
  ('alice', 'alice@example.com', '$2b$12$...'),
  ('bob', 'bob@example.com', '$2b$12$...'),
  ('carol', 'carol@example.com', '$2b$12$...');

-- 更新
UPDATE users SET status = 0 WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 刪除 (謹慎使用，建議軟刪除)
DELETE FROM users WHERE id = 123;

-- 查詢
SELECT id, username, email, status FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT 20;

四、文字檔案匯入 (LOAD DATA)

檔案格式要求

• 欄位分隔符：Tab (預設) 或自訂 FIELDS TERMINATED BY ','
• 欄位包圍符：無 (預設) 或 ENCLOSED BY '"'
• 行結束符：\n (預設) 或 LINES TERMINATED BY '\r\n'
• NULL 值：\N (預設) 或自訂
• 標題行：IGNORE 1 LINES 跳過

匯入指令範例

-- 基本匯入 (Tab 分隔、無標題)
LOAD DATA LOCAL INFILE '/data/users.txt' INTO TABLE users;

-- CSV 匯入 (有標題行、雙引號包圍)
LOAD DATA LOCAL INFILE '/data/users.csv' INTO TABLE users
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  IGNORE 1 LINES
  (username, email, @password_raw)
  SET password_hash = SHA2(@password_raw, 256);

-- 大檔案優化
SET SESSION bulk_insert_buffer_size = 256M;
SET SESSION unique_checks = 0;
SET SESSION foreign_key_checks = 0;
LOAD DATA ...
SET SESSION unique_checks = 1;
SET SESSION foreign_key_checks = 1;

安全性注意

• LOCAL 關鍵字：檔案在客戶端，經網路傳輸至伺服器 (需客戶端/伺服器均啟用 local_infile=1)
• 無 LOCAL：檔案必須在伺服器端，需 FILE 權限
• 生產環境建議：關閉 local_infile，使用伺服器端匯入或應用層批次寫入

五、備份與還原

mysqldump 邏輯備份 (標準工具)

# 備份單一資料庫 (含結構+資料、觸發器、常規)
mysqldump -u root -p --single-transaction --routines --triggers --events business_db > business_db_$(date +%F).sql

# 備份單一表
mysqldump -u root -p --single-transaction business_db users > users_$(date +%F).sql

# 僅備份結構 (無資料)
mysqldump -u root -p --no-data business_db > schema_only.sql

# 壓縮備份 (管道串流，不佔暫存空間)
mysqldump -u root -p --single-transaction business_db | gzip > business_db_$(date +%F).sql.gz

# 遠端備份
mysqldump -h 192.168.1.100 -u root -p --single-transaction business_db | gzip > remote_backup.sql.gz

關鍵參數解析

還原操作

# 1. 建立目標資料庫
mysql -u root -p -e "CREATE DATABASE business_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# 2. 建立應用帳號 (參考權限管理文章)

# 3. 還原 (解壓管道串流)
gunzip < business_db_2024-01-15.sql.gz | mysql -u root -p business_db

# 或直接還原 SQL 檔
mysql -u root -p business_db < business_db_2024-01-15.sql

# 4. 驗證
mysql -u root -p -e "USE business_db; SHOW TABLES; SELECT COUNT(*) FROM users;"

物理備份 (Percona XtraBackup / MySQL Enterprise Backup)

• 適用大型資料庫 (TB 級)，備份/還原速度快數十倍
• 支援增量備份、流式傳輸、壓縮、加密
• 需安裝額外工具，操作較複雜，建議大型環境採用

六、自動化備份腳本範例 (Cron 部署)

#!/bin/bash
# /usr/local/bin/mysql_backup.sh
BACKUP_DIR="/backup/mysql"
DB_USER="backup_user"
DB_PASS="strong_password"
DBS="business_db logging_db"
RETENTION_DAYS=7

mkdir -p "$BACKUP_DIR"
for DB in $DBS; do
  FILE="$BACKUP_DIR/${DB}_$(date +%F_%H-%M).sql.gz"
  mysqldump -u "$DB_USER" -p"$DB_PASS" --single-transaction --routines --triggers --events "$DB" | gzip > "$FILE"
  echo "[$(date)] Backup $DB -> $FILE"
done

# 清理舊備份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$(date)] Cleanup completed"

# Crontab: 每天凌晨 2:30 執行
30 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

命令列操作無捷徑，唯有大量練習建立肌肉記憶。建議在測試環境完整演練「建庫 → 建表 → 匯入 → 備份 → 還原 → 驗證」全流程，再上生產環境。