基于 *.ibd 和 *.frm 文件進行 InnoDB 表的數(shù)據(jù)遷移，核心是利用了 InnoDB 的 “可傳輸表空間” 特性。

這種方法比執(zhí)行 mysqldump 或 SELECT ... INTO OUTFILE 要快得多，尤其適用于大表遷移，因為它直接復制物理文件。

主要sql腳本

以下sql腳本支持在MySQL5.7.44和MySQL8.4.6之間進行ibd的遷移和恢復！

在MySQL 8目標庫上操作——

1. create database test1;

2. CREATE TABLE test1.`sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_name` varchar(50) DEFAULT NULL,
  `sale_date` date DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;

3. ALTER TABLE test1.sales DISCARD TABLESPACE;

4. 導入MySQL5.7.44中sales表的ibd文件至對應的數(shù)據(jù)目錄下；

5. 重啟數(shù)據(jù)庫后執(zhí)行下述導入表空間操作；

6. ALTER TABLE test1.sales IMPORT TABLESPACE;

SELECT * FROM sales;

SELECT VERSION();

重要前提與警告

1. MySQL版本：此方法適用于 MySQL 5.6 及更高版本。不同大版本之間（如從 5.7 遷移到 8.0）可能有問題，最好在同版本或小版本間進行。
2. 存儲引擎：必須是 InnoDB 表。
3. 配置：必須開啟 innodb_file_per_table（默認就是開啟的）。這個配置意味著每個表都有自己獨立的 *.ibd 文件。
4. 文件一致性：復制的 *.ibd 文件必須與數(shù)據(jù)庫的邏輯狀態(tài)保持一致。因此，操作過程中需要將表置于一種鎖定的狀態(tài)。
5. MySQL 8.0+ 注意：從 MySQL 8.0 開始，不再有 *.frm 文件。表結(jié)構(gòu)存儲在數(shù)據(jù)字典中。如果你只有 *.frm 和 *.ibd 文件，說明它們來自舊版本（如 5.7）。在 8.0 中恢復時，需要先創(chuàng)建一個表結(jié)構(gòu)完全相同的表。

遷移場景與步驟

假設我們要將表 mydatabase.mytable 從 源服務器 遷移到 目標服務器。

場景一：從運行中的MySQL服務器遷移（最常用）

這種方法適用于源表可被短暫鎖定的情況。

在源服務器上操作：

• 在目標服務器上創(chuàng)建空表

-- 在目標服務器的數(shù)據(jù)庫中，先創(chuàng)建一個表結(jié)構(gòu)完全相同的空表。
-- 你可以通過 `SHOW CREATE TABLE mydatabase.mytable\G` 在源服務器上獲取建表語句。
CREATE TABLE mydatabase.mytable (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

• 丟棄目標表的表空間

-- 這個操作會刪除目標表新創(chuàng)建的、空的 .ibd 文件。
ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

執(zhí)行后，目標服務器的 mytable.ibd 文件會被刪除。

在源服務器上操作：

• 鎖定并準備源表

-- 對源表加一個讀鎖，并生成一個 .cfg 文件（包含表空間元數(shù)據(jù)）。
FLUSH TABLES mydatabase.mytable FOR EXPORT;

執(zhí)行這個命令后：

• 表 mytable 會被加上讀鎖，僅允許查詢，不允許寫入。
• 在 mydatabase 目錄下，會生成一個 mytable.cfg 文件。

復制文件
在操作系統(tǒng)層面，從源服務器的數(shù)據(jù)目錄復制三個文件到安全的地方：

# 進入MySQL數(shù)據(jù)目錄下的數(shù)據(jù)庫目錄
cd /var/lib/mysql/mydatabase

# 復制文件
cp mytable.cfg mytable.ibd /path/to/backup/directory/

解鎖源表

-- 復制完成后，立即解鎖源表，恢復寫入。
UNLOCK TABLES;

這個操作會同時刪除 mytable.cfg 文件。

在目標服務器上操作：

傳輸文件
將剛才復制的 mytable.ibd 和 mytable.cfg 文件傳輸?shù)侥繕朔掌鞯膶獢?shù)據(jù)庫目錄下（如 /var/lib/mysql/mydatabase/），并確保文件所有者是 mysql 用戶。

scp /path/to/backup/mytable.{ibd,cfg} user@target-server:/var/lib/mysql/mydatabase/
chown mysql:mysql /var/lib/mysql/mydatabase/mytable.*

導入表空間

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

執(zhí)行這個命令后，MySQL 會讀取 mytable.cfg 文件來驗證表空間的一致性，然后將數(shù)據(jù)導入。

驗證

SELECT COUNT(*) FROM mydatabase.mytable;

場景二：從物理備份文件恢復（僅有 .frm 和 .ibd 文件）

這種情況通常是你只有物理文件，沒有運行中的源MySQL實例。這更像是一種數(shù)據(jù)恢復操作。

前提：你必須知道該表的精確表結(jié)構(gòu)。

在目標服務器上操作：

• 創(chuàng)建表結(jié)構(gòu)完全相同的空表

-- 這是最關(guān)鍵的一步！表結(jié)構(gòu)必須與源表100%一致（列名、類型、索引、行格式等）。
CREATE TABLE mydatabase.mytable (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

• 丟棄目標表的表空間

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

• 復制文件
  將你擁有的 mytable.ibd 文件（如果有 mytable.cfg 也一起）復制到目標服務器的數(shù)據(jù)庫目錄，并修改所有者。

cp mytable.ibd /var/lib/mysql/mydatabase/
chown mysql:mysql /var/lib/mysql/mydatabase/mytable.ibd

導入表空間之前需要重啟一下數(shù)據(jù)庫?。。?br />并且支持在MySQL5.7和8之間進行遷移

嘗試導入表空間

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

可能遇到的問題與解決方案：

• 錯誤：Schema mismatch：表結(jié)構(gòu)不匹配。請仔細檢查并重新創(chuàng)建表，確保每個細節(jié)都相同。
• 錯誤：表空間ID不匹配：這是正?，F(xiàn)象，IMPORT TABLESPACE 過程就是為了解決這個問題。
• MySQL 8.0 恢復 5.7 的表：
  • 你沒有 *.frm 文件，需要在 8.0 中根據(jù)記憶或文檔創(chuàng)建表結(jié)構(gòu)。
  • 最好先在 MySQL 5.7 實例中通過 SHOW CREATE TABLE 獲取精確的表結(jié)構(gòu)。

總結(jié)與工作流圖示

標準流程（場景一）：

目標庫：創(chuàng)建空表 -> DISCARD TABLESPACE
源庫：FLUSH TABLE ... FOR EXPORT -> 復制 .ibd & .cfg -> UNLOCK TABLES
目標庫：傳輸文件 -> IMPORT TABLESPACE -> 驗證

核心命令三部曲：

1. 目標庫準備：ALTER TABLE ... DISCARD TABLESPACE; (清空舞臺)
2. 源庫鎖定并復制：FLUSH TABLE ... FOR EXPORT; -> cp -> UNLOCK TABLES; (準備并搬運貨物)
3. 目標庫導入：ALTER TABLE ... IMPORT TABLESPACE; (接收貨物)

以上就是MySQL通過傳輸表空間實現(xiàn)大表ibd文件的物理遷移的具體方案的詳細內(nèi)容，更多關(guān)于MySQL大表ibd文件物理遷移的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論