| 工具/命令 | 類型 | 適用場景 | 特點 |
|---|---|---|---|
| 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 -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:統一編碼防亂碼# 進入客戶端
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 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 目錄權限,文件不能預先存在。
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);
#!/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
MySQL 數據流轉工具箱:**mysqldump** 通用邏輯備份、**source** 邏輯恢復、**SELECT INTO OUTFILE / LOAD DATA** 高性能批量遷移、**xtrabackup** 物理熱備份、**mysqlpump** 並行導出。選型原則:**跨版本/異構用邏輯,同版本大庫用物理,高頻遷移用複製**。統一 utf8mb4、--single-transaction、--hex-blob、壓縮、校驗、保留策略,構建企業級數據流轉體系。