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

這種方法比執(zhí)行 mysqldump 或 SELECT ... INTO OUTFILE 要快得多，尤其適用于大表遷移，因?yàn)樗苯訌?fù)制物理文件。

主要sql腳本

以下sql腳本支持在MySQL5.7.44和MySQL8.4.6之間進(jìn)行ibd的遷移和恢復(fù)！

在MySQL 8目標(biāo)庫(kù)上操作——

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. 導(dǎo)入MySQL5.7.44中sales表的ibd文件至對(duì)應(yīng)的數(shù)據(jù)目錄下；

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

6. ALTER TABLE test1.sales IMPORT TABLESPACE;

SELECT * FROM sales;

SELECT VERSION();

重要前提與警告

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

遷移場(chǎng)景與步驟

假設(shè)我們要將表 mydatabase.mytable 從 源服務(wù)器 遷移到 目標(biāo)服務(wù)器。

場(chǎng)景一：從運(yùn)行中的MySQL服務(wù)器遷移（最常用）

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

在源服務(wù)器上操作：

• 在目標(biāo)服務(wù)器上創(chuàng)建空表

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

• 丟棄目標(biāo)表的表空間

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

執(zhí)行后，目標(biāo)服務(wù)器的 mytable.ibd 文件會(huì)被刪除。

在源服務(wù)器上操作：

• 鎖定并準(zhǔn)備源表

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

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

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

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

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

# 復(fù)制文件
cp mytable.cfg mytable.ibd /path/to/backup/directory/

解鎖源表

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

這個(gè)操作會(huì)同時(shí)刪除 mytable.cfg 文件。

在目標(biāo)服務(wù)器上操作：

傳輸文件
將剛才復(fù)制的 mytable.ibd 和 mytable.cfg 文件傳輸?shù)侥繕?biāo)服務(wù)器的對(duì)應(yīng)數(shù)據(jù)庫(kù)目錄下（如 /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.*

導(dǎo)入表空間

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

執(zhí)行這個(gè)命令后，MySQL 會(huì)讀取 mytable.cfg 文件來(lái)驗(yàn)證表空間的一致性，然后將數(shù)據(jù)導(dǎo)入。

驗(yàn)證

SELECT COUNT(*) FROM mydatabase.mytable;

場(chǎng)景二：從物理備份文件恢復(fù)（僅有 .frm 和 .ibd 文件）

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

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

在目標(biāo)服務(wù)器上操作：

• 創(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;

• 丟棄目標(biāo)表的表空間

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

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

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

導(dǎo)入表空間之前需要重啟一下數(shù)據(jù)庫(kù)?。?！
并且支持在MySQL5.7和8之間進(jìn)行遷移

嘗試導(dǎo)入表空間

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

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

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

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

標(biāo)準(zhǔn)流程（場(chǎng)景一）：

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

核心命令三部曲：

1. 目標(biāo)庫(kù)準(zhǔn)備：ALTER TABLE ... DISCARD TABLESPACE; (清空舞臺(tái))
2. 源庫(kù)鎖定并復(fù)制：FLUSH TABLE ... FOR EXPORT; -> cp -> UNLOCK TABLES; (準(zhǔn)備并搬運(yùn)貨物)
3. 目標(biāo)庫(kù)導(dǎo)入：ALTER TABLE ... IMPORT TABLESPACE; (接收貨物)

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

最新評(píng)論