核心工具:mysqldump 精準導出單表數據
基本語法:僅導出數據(不含結構)
mysqldump -t -u root -p -B database_name --table table_name > /path/export.sql
-t / --no-create-info:不導出 CREATE TABLE
-B / --databases:指定庫(可省略,但建議顯式指定)
--table / --tables:指定表名
實戰示例
mysqldump -t -u root -p -B article --table article_detail > /backup/article_detail_$(date +%F).sql
編碼處理:防止中文亂碼的關鍵參數
導出時指定字符集
mysqldump -u root -p --default-character-set=utf8mb4 database_name > /backup/db_$(date +%F).sql
導入時指定字符集
mysql -u root -p --default-character-set=utf8mb4
mysql> USE database_name;
mysql> SOURCE /backup/db_2024-01-15.sql;
鐵律:導出、導入、表結構、文件編碼**四方一致**,統一 utf8mb4。
常用導出場景組合
| 場景 | 命令模板 |
| 全庫結構+數據 | mysqldump -u root -p db_name > full.sql |
| 全庫僅結構 | mysqldump -u root -p -d db_name > schema.sql |
| 單表結構+數據 | mysqldump -u root -p db_name tbl_name > tbl.sql |
| 單表僅數據 | mysqldump -u root -p -t db_name tbl_name > data.sql |
| 多表數據 | mysqldump -u root -p -t db_name tbl1 tbl2 > multi.sql |
| 帶 WHERE 條件導出 | mysqldump -u root -p -t db_name tbl --where="id > 1000" > part.sql |
| 壓縮導出 | mysqldump -u root -p db_name | gzip > db_$(date +%F).sql.gz |
source 恢復實戰
mysql -u root -p --default-character-set=utf8mb4
mysql> USE target_db;
mysql> SOURCE /backup/article_detail_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
自動化備份腳本模板
#!/bin/bash
# /usr/local/bin/backup_mysql.sh
DATE=$(date +%F_%H-%M)
BACKUP_DIR=/backup/mysql
DB_USER=backup_user
DB_PASS='StrongPass#2024'
DATABASES="db1 db2 db3"
mkdir -p $BACKUP_DIR/$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 | gzip > $BACKUP_DIR/$DATE/${DB}.sql.gz
# 驗證文件非空
if [ ! -s $BACKUP_DIR/$DATE/${DB}.sql.gz ]; then
echo "ERROR: $DB backup empty!" | mail -s "MySQL Backup Failed" admin@company.com
fi
done
# 保留 30 天
find $BACKUP_DIR -type f -mtime +30 -name "*.gz" -delete
常見問題排查
| 問題 | 原因 | 解決 |
| 導入報 "Packet too large" | max_allowed_packet 過小 | 客端/服務端同步調大至 1G |
| 導入報 "Unknown collation" | 版本差異導致校對規則不識別 | 導出加 --skip-set-charset 或目標庫創建時指定兼容校對 |
| 導出耗時極長/卡住 | 無 --single-transaction 鎖表 / 大事務 | InnoDB 必加 --single-transaction |
| 備份文件含 CREATE DATABASE 導入報錯 | 目標庫已存在/權限不足 | 去掉 -B 參數或手工建庫後導入 |
總結
單表備份核心:mysqldump -t --table 精準導出數據、**全鏈路 --default-character-set=utf8mb4 防亂碼**、**source 導入配合大包參數**。自動化腳本納入 --single-transaction(一致性)、--master-data=2(位點記錄)、--hex-blob(二進制安全)、壓縮存儲、保留策略,構建企業級備份體系。