腳本之家服務器常用軟件

快捷導航

MySQL進行數(shù)據離線遷移的實現(xiàn)方案

更新時間：2026年02月26日 08:47:27 作者：西門吹雪分身

在實際的業(yè)務需求中,mysql數(shù)據庫往往需要從一個服務器遷移或者導出到另外一臺服務器,這個數(shù)據導入導出的工作,是不可避免的,所以本文介紹了MySQL數(shù)據遷移的物理操作方式,需要的朋友可以參考下

mysql對于數(shù)據的導入導出提供了邏輯操作與物理操作兩種方式，其中mysqdump的邏輯操作往往需要生成SQL文件，如果數(shù)據量比較大，可能執(zhí)行的效果比較差，耗時比較長。本文主要介紹一下基于mysql文件層面的物理操作，也可以叫做冷備份。

mysql的相關官網說明

mysql官網

Transportable Tablespace（可傳輸表空間）是 MySQL InnoDB 存儲引擎提供的一種機制，允許用戶將某個表的表空間文件（.ibd）及其元數(shù)據校驗文件（.cfg）從一臺服務器“運輸”到另一臺服務器，并直接掛載到目標數(shù)據庫的表結構上。

核心原理
InnoDB 表空間文件內部包含了一個唯一的 Tablespace ID 和 Schema 指紋。直接拷貝 .ibd 文件時，目標庫的元數(shù)據（Data Dictionary）中記錄的 ID 與文件內部的 ID 不一致，導致無法識別。

.ibd：實際的數(shù)據文件。
.cfg：由 FLUSH TABLES … FOR EXPORT 命令生成。它包含了表空間的元數(shù)據副本（包括 Tablespace ID、Schema 校驗和、頁大小等）。
流程本質：
1. 目標庫先創(chuàng)建一個空表，生成一個新的空的 .ibd。
2. 執(zhí)行 DISCARD TABLESPACE：目標庫刪除這個空的 .ibd，并進入“等待導入”狀態(tài)。
3. 放入源庫的 .ibd 和 .cfg。
4. 執(zhí)行 IMPORT TABLESPACE：目標庫讀取 .cfg，驗證 .ibd 的完整性和一致性。如果通過，它將更新內部數(shù)據字典，將現(xiàn)有的 .ibd 文件“認領”為該表的數(shù)據文件。

詳細的操作流程

假設我們要遷移表 users，數(shù)據庫為 test_db。

前提條件：

源庫和目標庫 MySQL 大版本一致（如都是 5.7 或 8.0）。
源庫和目標庫的 innodb_file_per_table 均為 ON。
目標庫已創(chuàng)建好結構完全一致的表。

第一階段：在源庫（Source）生成文件

這一步的目的是獲取一份一致性快照的數(shù)據文件和配套的校驗文件。

確保表是可傳輸?shù)模ㄍǔＤJ就是，但顯式執(zhí)行一次更穩(wěn)妥）：

USE test_db;
-- 這一步會刷新臟頁到磁盤，并鎖定表進行元數(shù)據凍結
FLUSH TABLES users FOR EXPORT;

注意：執(zhí)行此命令后，表 users 會被鎖定為只讀（Read Only），直到你解鎖或復制完文件。其他會話對該表的寫操作會被阻塞。

復制文件
保持終端窗口不關閉（保持鎖狀態(tài)），打開另一個終端或通過腳本復制文件。
找到數(shù)據目錄（通常是 /var/lib/mysql/test_db/）：
- users.ibd：數(shù)據文件。
- users.cfg：關鍵文件，包含元數(shù)據校驗信息。

# 在另一個終端執(zhí)行
cp /var/lib/mysql/test_db/users.{ibd,cfg} /tmp/backup_for_migration/

# 確認文件已復制完成
ls -lh /tmp/backup_for_migration/

解鎖源庫
文件復制完成后，必須立即解鎖，恢復業(yè)務寫入。
回到第一個執(zhí)行 SQL 的終端：

UNLOCK TABLES;

此時源庫業(yè)務恢復正常。

第二階段：在目標庫（Target）準備環(huán)境

創(chuàng)建結構一致的表
在目標庫執(zhí)行與源庫完全相同的 CREATE TABLE 語句。

CREATE DATABASE IF NOT EXISTS test_db;
USE test_db;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -- 必須與源庫完全一致，包括字符集、排序規(guī)則、索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

提示：可以用 SHOW CREATE TABLE users\G 在源庫查看精確語句。

丟棄自動生成的表空間：

剛才建表時，InnoDB 已經生成了一個空的 users.ibd。需要刪掉它，以便導入舊的。

ALTER TABLE users DISCARD TABLESPACE;

執(zhí)行后，去操作系統(tǒng)查看 /var/lib/mysql/test_db/users.ibd，它應該已經消失了。此時表存在，但無法讀寫數(shù)據。

第三階段：導入文件到目標庫

拷貝文件到目標數(shù)據目錄：
將第一階段生成的 users.ibd 和 users.cfg 上傳到目標庫對應的數(shù)據目錄。

# 假設文件在 /tmp/backup_for_migration/
cp /tmp/backup_for_migration/users.{ibd,cfg} /var/lib/mysql/test_db/

修正文件權限（至關重要）：
MySQL 進程通常以 mysql 用戶運行。如果文件歸屬是 root，導入會失敗。

chown mysql:mysql /var/lib/mysql/test_db/users.{ibd,cfg}
chmod 660 /var/lib/mysql/test_db/users.{ibd,cfg}

執(zhí)行導入命令
登錄目標庫 MySQL：

USE test_db;
ALTER TABLE users IMPORT TABLESPACE;

如果成功：無任何輸出，表立即變?yōu)榭捎脿顟B(tài)。
如果失?。簳箦e。常見原因包括：
- Schema mismatch：建表語句不一致（字段類型、順序、字符集等）。
- Tablespace ID mismatch：通常是因為沒執(zhí)行 DISCARD TABLESPACE 或者 .cfg 文件不匹配。

驗證數(shù)據：

SELECT COUNT(*) FROM users;
SELECT * FROM users LIMIT 5;

特點與限制

優(yōu)點

速度極快：不涉及邏輯導出（SQL 生成）和導入（SQL 解析執(zhí)行），本質是文件拷貝，適合 TB 級大表遷移。
節(jié)省 IO：不會像 mysqldump 那樣產生巨大的重做日志（Redo Log）和二進制日志（Binlog）壓力（導入時可以暫時關閉 Binlog）。
支持分區(qū)表：可以單獨導入某個分區(qū)，也可以導入整個分區(qū)表。

限制與注意事項

4. 版本嚴格限制：

源和目標必須是相同的大版本（如 5.7 -> 5.7, 8.0 -> 8.0）。
跨大版本（如 5.6 -> 8.0）通常不支持，因為 .ibd 內部格式可能發(fā)生變化。

架構一致：

表結構（DDL）必須比特級一致。哪怕是字符集 utf8 和 utf8mb4 的區(qū)別，或者自增列初始值不同，都可能導致 Schema mismatch 錯誤。

只針對 InnoDB：MyISAM 或其他引擎不適用。
外鍵約束：如果表之間有外鍵約束，導入過程可能會比較復雜，建議先禁用外鍵檢查 (SET FOREIGN_KEY_CHECKS=0)，導入后再開啟。
只讀窗口：在源庫執(zhí)行 FLUSH TABLES … FOR EXPORT 期間，該表是只讀的。雖然時間很短（僅拷貝文件的時間），但在高并發(fā)場景下仍需注意。

總結

Transportable Tablespace 是 MySQL 官方提供的“物理備份/遷移”方案。

核心命令組合：FLUSH TABLES … FOR EXPORT (源) + DISCARD TABLESPACE (目標) + IMPORT TABLESPACE (目標)。
關鍵文件：.ibd (數(shù)據) + .cfg (元數(shù)據校驗)。
適用場景：海量數(shù)據快速遷移、表空間損壞后的單表恢復、跨實例克隆大表

以上就是MySQL進行數(shù)據離線遷移的實現(xiàn)方案的詳細內容，更多關于MySQL數(shù)據離線遷移的資料請關注腳本之家其它相關文章！

您可能感興趣的文章: