MySQL 重裝後數據恢復實戰：InnoDB .ibd 文件丟失、亂碼遷移與 mysqldump 編碼轉換全記錄

事故現場還原

• 觸發點：MySQL 服務異常，重啟無效，決定重裝。
• 操作：停止服務 → 重命名舊目錄 → 全新安裝 → 複製舊 `data` 目錄覆蓋新安裝。
• 現象：PHPMyAdmin 顯示用戶消失、數據庫存在；部分網站報 InnoDB 錯誤 `Missing .ibd file`；PHPMyAdmin 備份失敗 `Table is in use`。

核心教訓：直接複製 data 目錄的風險

正確恢復路徑：邏輯備份 + 編碼轉換

步驟 1：停止服務，完整保留舊 data 目錄

systemctl stop mysqld
mv /var/lib/mysql /var/lib/mysql_old_$(date +%F)
# 或複製備份
cp -a /var/lib/mysql /backup/mysql_$(date +%F)

步驟 2：全新安裝 MySQL (同版本/高版本)

• 初始化全新 data 目錄 (生成乾淨系統表、ibdata1、ib_logfile)。
• 啟動服務，驗證 root 登入正常。

步驟 3：從舊 data 目錄導出邏輯備份 (mysqldump)

# 啟動一個臨時實例掛載舊 data 目錄 (端口 3307)
mysqld_safe --defaults-file=/tmp/old_my.cnf --datadir=/var/lib/mysql_old --port=3307 --socket=/tmp/mysql_old.sock &

# 導出所有用戶庫 (排除系統庫)
mysqldump -h127.0.0.1 -P3307 -uroot -p \
  --all-databases \
  --ignore-database=mysql --ignore-database=sys \
  --ignore-database=performance_schema --ignore-database=information_schema \
  --single-transaction --routines --triggers --events \
  --default-character-set=utf8mb4 \
  --set-gtid-purged=OFF \
  > /backup/full_dump_$(date +%F).sql

# 關鍵參數：
# --default-character-set=utf8mb4  強制以 utf8mb4 讀取舊數據 (即使舊庫是 latin1)
# --set-gtid-purged=OFF            避免 GTID 污染新實例
# --single-transaction             InnoDB 一致性視圖，無鎖導出

步驟 4：編碼轉換 (latin1 → utf8mb4 典型場景)

# 方法一：sed 替換 CREATE TABLE 字元集聲明 (適用純 latin1 存儲)
sed -i 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4/g' full_dump.sql
sed -i 's/CHARSET=latin1/CHARSET=utf8mb4/g' full_dump.sql

# 方法二：iconv 整體文件轉碼 (適用混合編碼/無聲明)
iconv -f latin1 -t utf8mb4 full_dump.sql > full_dump_utf8.sql

# 方法三：mysqldump 導出時指定 --default-character-set=latin1 讀取，
# 導入時指定 --default-character-set=utf8mb4 寫入 (MySQL 內部自動轉換)
# 推薦：導出 latin1，導入 utf8mb4，讓 MySQL 引擎處理轉換

步驟 5：導入新實例

# 創建目標庫 (指定 utf8mb4)
mysql -uroot -p -e "CREATE DATABASE biz_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# 導入 (指定 utf8mb4 客戶端字元集)
mysql -uroot -p --default-character-set=utf8mb4 biz_db < full_dump_utf8.sql

# 或流式導入 (無中間文件)
mysqldump -h127.0.0.1 -P3307 -uroot -p --default-character-set=latin1 biz_db \
| mysql -h127.0.0.1 -P3306 -uroot -p --default-character-set=utf8mb4 biz_db

WordPress 類應用遷移特殊處理

wp-config.php 與 wp-db.php 雙重保障

// wp-config.php
define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', 'utf8mb4_unicode_ci');

// wp-includes/wp-db.php (連接建立後立即執行)
$this->query("SET NAMES 'utf8mb4'");  // 強制會話字元集
// 位置：db_connect() 方法中，select($dbname) 之前

站點 URL 更新 (跨域/遷移必做)

UPDATE wp_options SET option_value = 'https://new-domain.com' WHERE option_name IN ('siteurl','home');
-- 或 PHPMyAdmin 編輯 wp_options 表

MySQL 版本升級遷移 (4.0 → 5.0/8.0) 亂碼根治

現象

• 舊版本 (4.0/5.1) 無字元集概念，數據實際按 latin1 存儲。
• 新版本 (5.5+/8.0) 預設 utf8mb4，直接導入顯示 `??` 或亂碼。

根治方案：拉丁 1 讀取 → UTF8MB4 寫入

# 1. 導出時強制按 latin1 讀取 (不做任何轉換)
mysqldump -h old_host -u root -p --default-character-set=latin1 \
  --single-transaction db_name > dump_latin1.sql

# 2. 新庫創建為 utf8mb4
mysql -h new_host -u root -p -e "CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# 3. 導入時強制按 utf8mb4 寫入 (MySQL 自動 latin1→utf8mb4 轉換)
mysql -h new_host -u root -p --default-character-set=utf8mb4 db_name < dump_latin1.sql

# 4. 驗證
mysql -h new_host -u root -p db_name -e "SELECT col FROM tbl WHERE col LIKE '%中文%';"

避坑總結：重裝/遷移黃金法則

1. 絕不直接複製 data 目錄跨版本/跨實例 — 只能同版本、同配置、同字元集的極緊急場景嘗試，且需專家評估。
2. 永遠使用邏輯備份 (mysqldump) 遷移數據 — 版本無關、字元集可控、結構自適應。
3. 字元集遷移三步走：舊庫實際編碼 → 導出指定讀取編碼 → 新庫創建目標編碼 → 導入指定寫入編碼。
4. 系統表永遠不遷移：用戶、權限、幫助表由新實例自帶，應用用戶重建授權。
5. 驗證鏈路：抽樣查詢中文欄位、檢查排序、確認應用讀寫正常、壓測驗證效能。

這次事故從「直接複製 data 目錄」到「mysqldump 編碼轉換導入」的轉折，生動詮釋了**物理備份不跨版本、邏輯備份通吃天下**的鐵律。將遷移流程腳本化、參數化、文檔化，納入變更管理體系，才能在下一次不可避免的升級/遷移中，從容應對 InnoDB 元數據不匹配、字元集亂碼、系統表版本衝突等經典難題。

1. 上一篇文章： MySQL Root 密碼緊急恢復實戰：--skip-grant-tables 模式下的標準化救援流程
2. 下一篇文章： MySQL 編碼轉換遷移實戰：從 latin1 到 UTF-8 的完整指南

MySQL 資料庫相關知識

XtraBackup 熱備份工具完整指南：安裝、備份、恢復與增量策略

XtraBackup 核心優勢 熱備份：InnoDB/Xt

MySQL 索引優化實戰：類型選擇、創建策略、EXPLAIN 分析與引擎配合

索引核心價值 索引是提升查詢性能的關鍵。無索引時 MySQL

MySQL 無限級分類三大實現方案深度解析與優化

無限級分類是電商、CMS 等系統的核心需求。本文系統梳理遞歸

MySQL 閃退問題完整排查指南：服務未啟動、權限、配置錯誤與連接參數錯誤

MySQL Command Line Client 輸入密碼