萬盛學電腦網

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

MySQL 單表數據導出與備份實戰:mysqldump 精準導出、編碼處理與 source 恢復

核心工具: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(二進制安全)、壓縮存儲、保留策略,構建企業級備份體系。

copyright © 萬盛學電腦網 all rights reserved