萬盛學電腦網

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

MySQL 數據導入導出全景指南:mysqldump、source、SELECT INTO OUTFILE、LOAD DATA 實戰

核心工具對比

工具/命令類型適用場景特點
mysqldump邏輯備份全庫/單表/結構/數據導出SQL 文本、跨版本兼容、可壓縮、支持 WHERE 過濾
source邏輯恢復SQL 文件導入客戶端命令、流式執行、大文件友好
SELECT INTO OUTFILE數據導出服務端生成 CSV/文本高性能、需 FILE 權限、受 secure_file_priv 限制
LOAD DATA INFILE數據導入文本/CSV 批量導入極快(比 INSERT 快 20 倍)、支持並行、格式靈活
mysqlpump並行邏輯備份大庫並行導出MySQL 5.7+、多線程、壓縮、對象級並行
xtrabackup / mariabackup物理熱備份生產級全量/增量備份不阻塞寫入、恢復快、需額外安裝

mysqldump 核心參數速查

# 全庫備份(含結構+數據)
mysqldump -u root -p --single-transaction --routines --triggers --events \
  --master-data=2 --hex-blob --default-character-set=utf8mb4 db_name > backup.sql

# 單表僅數據
mysqldump -u root -p -t db_name tbl_name > data.sql

# 僅結構
mysqldump -u root -p -d db_name > schema.sql

# 按條件導出部分數據
mysqldump -u root -p -t db_name tbl_name --where="id > 1000 AND status=1" > part.sql

# 多庫並行壓縮備份
mysqlpump --parallel-schemas=4 --default-parallelism=4 -u root -p \
  --default-character-set=utf8mb4 db1 db2 | gzip > multi_db.gz

關鍵參數解析

  • --single-transaction:InnoDB 一致性快照,**不鎖表**,必加
  • --master-data=2:輸出註釋含 binlog 位點,便於從庫搭建
  • --hex-blob:二進制列用十六進制導出,防亂碼/導入失敗
  • --routines --triggers --events:導出存儲過程/觸發器/事件
  • --default-character-set=utf8mb4:統一編碼防亂碼

source 導入實戰

# 進入客戶端
mysql -u root -p --default-character-set=utf8mb4

# 選庫並導入
mysql> USE target_db;
mysql> SOURCE /backup/full_2024-01-15.sql;

# 大文件優化參數直連導入(更快)
mysql -u root -p --default-character-set=utf8mb4 \
  --max_allowed_packet=1G --net_buffer_length=1M \
  target_db < /backup/large.sql

SELECT INTO OUTFILE / LOAD DATA INFILE 高性能數據遷移

導出 CSV(服務端文件)

SELECT id, name, email, created_at
INTO OUTFILE '/var/lib/mysql-files/export_users.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
FROM users WHERE status = 1;
需 secure_file_priv 目錄權限,文件不能預先存在。

導入 CSV(極速批量)

LOAD DATA INFILE '/var/lib/mysql-files/export_users.csv'
INTO TABLE users_new
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES  -- 跳過表頭
(id, name, email, created_at);

LOAD DATA 核心優勢

  • **批量提交**:默認每 1000 行提交一次(可調 `bulk_insert_buffer_size`)
  • **無索引維護開銷**:延遲索引構建(MyISAM)或批量插入緩衝(InnoDB)
  • **並行導入**:MySQL 8.0 支持 `LOAD DATA ... PARALLEL`(實驗特性)

自動化備份腳本(生產級)

#!/bin/bash
# /etc/cron.daily/backup_mysql.sh
DATE=$(date +%F)
BACKUP_ROOT=/backup/mysql
RETENTION_DAYS=30
DB_USER=backup_user
DB_PASS='StrongPass#2024'
DATABASES=(db1 db2 db3)

mkdir -p $BACKUP_ROOT/$DATE

for DB in "${DATABASES[@]}"; do
    mysqldump -u$DB_USER -p$DB_PASS \
        --single-transaction --routines --triggers --events \
        --master-data=2 --hex-blob --default-character-set=utf8mb4 \
        $DB | pigz -p 4 > $BACKUP_ROOT/$DATE/${DB}.sql.gz
    
    # 校驗
    if [ ${PIPESTATUS[0]} -ne 0 ] || [ ! -s $BACKUP_ROOT/$DATE/${DB}.sql.gz ]; then
        echo "[$(date)] $DB backup FAILED" | mail -s "MySQL Backup Alert" dba@company.com
    fi
done

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

異構遷移/版本升級遷移策略

  1. 邏輯遷移:`mysqldump` → 目標庫 `source` / `mysql < file`(跨版本最安全)
  2. 物理遷移:`xtrabackup` 全量+增量 → 目標機器恢復(同版本、大庫最快)
  3. 複製遷移:建立主從 → 切換讀寫 → 斷開復制(零停機、同版本)

總結

MySQL 數據流轉工具箱:**mysqldump** 通用邏輯備份、**source** 邏輯恢復、**SELECT INTO OUTFILE / LOAD DATA** 高性能批量遷移、**xtrabackup** 物理熱備份、**mysqlpump** 並行導出。選型原則:**跨版本/異構用邏輯,同版本大庫用物理,高頻遷移用複製**。統一 utf8mb4、--single-transaction、--hex-blob、壓縮、校驗、保留策略,構建企業級數據流轉體系。

copyright © 萬盛學電腦網 all rights reserved