MySQL :: MySQL 8.4 參考手冊 :: 17.6.1.3 匯入 InnoDB 資料表

版本 8.4

9.0
8.0
5.7

8.0 日文

MySQL 8.4 參考手冊 / ... / 匯入 InnoDB 資料表

17.6.1.3 匯入 InnoDB 資料表

本節說明如何使用可傳輸表格空間功能匯入資料表，此功能允許匯入位於每個資料表檔案表格空間中的資料表、分割資料表或個別資料表分割區。您可能想要匯入資料表的原因有很多：

在非生產 MySQL 伺服器執行個體上執行報表，以避免對生產伺服器造成額外負載。
將資料複製到新的複本伺服器。
從備份的表格空間檔案還原資料表。
與匯入傾印檔案相比，這是一種更快速的移動資料方式，這需要重新插入資料並重建索引。
將資料移動到具有更適合您儲存需求的儲存媒體的伺服器。例如，您可能會將繁忙的資料表移動到 SSD 裝置，或將大型資料表移動到高容量 HDD 裝置。

本節的以下主題說明可傳輸表格空間功能：

先決條件

必須啟用 innodb_file_per_table 變數，此變數預設為啟用。
表格空間的頁面大小必須與目標 MySQL 伺服器執行個體的頁面大小相符。InnoDB 頁面大小由 innodb_page_size 變數定義，此變數會在初始化 MySQL 伺服器執行個體時設定。
如果資料表具有外部索引鍵關聯，則在執行 DISCARD TABLESPACE 之前，必須停用 foreign_key_checks。此外，您應該在相同的邏輯時間點匯出所有外部索引鍵相關資料表，因為 ALTER TABLE ... IMPORT TABLESPACE 不會強制執行匯入資料的外部索引鍵限制。若要執行此操作，請停止更新相關資料表、提交所有交易、取得資料表的共用鎖定，然後執行匯出操作。
從另一個 MySQL 伺服器執行個體匯入資料表時，兩個 MySQL 伺服器執行個體都必須具有正式發行 (GA) 狀態，且必須為相同版本。否則，必須在要匯入的相同 MySQL 伺服器執行個體上建立資料表。
如果資料表是藉由在 CREATE TABLE 陳述式中指定 DATA DIRECTORY 子句而在外部目錄中建立的，則您在目標執行個體上取代的資料表必須使用相同的 DATA DIRECTORY 子句定義。如果子句不符，則會報告結構描述不符錯誤。若要判斷來源資料表是否使用 DATA DIRECTORY 子句定義，請使用 SHOW CREATE TABLE 來檢視資料表定義。如需有關使用 DATA DIRECTORY 子句的資訊，請參閱第 17.6.1.2 節，「從外部建立資料表」。
如果表格定義中未明確指定 ROW_FORMAT 選項，或使用 ROW_FORMAT=DEFAULT，則來源和目的執行個體上的 innodb_default_row_format 設定必須相同。否則，當您嘗試匯入操作時，會報告結構描述不符的錯誤。使用 SHOW CREATE TABLE 來檢查表格定義。使用 SHOW VARIABLES 來檢查 innodb_default_row_format 設定。相關資訊，請參閱定義表格的列格式。

匯入表格

此範例示範如何匯入位於每個表格一個檔案的表格空間中的常規非分割表格。

在目的執行個體上，建立一個與您打算匯入的表格具有相同定義的表格。（您可以使用 SHOW CREATE TABLE 語法取得表格定義。）如果表格定義不符，當您嘗試匯入操作時，會報告結構描述不符的錯誤。
```
mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;
```
在目的執行個體上，捨棄您剛建立的表格的表格空間。（在匯入之前，您必須捨棄接收表格的表格空間。）
```
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
```
在來源執行個體上，執行 FLUSH TABLES ... FOR EXPORT 以靜止您打算匯入的表格。當表格被靜止時，只允許對該表格進行唯讀交易。
```
mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;
```
FLUSH TABLES ... FOR EXPORT 確保對指定表格的變更已刷新到磁碟，以便在伺服器執行時可以製作二進制表格副本。當執行 FLUSH TABLES ... FOR EXPORT 時，InnoDB 會在表格的結構描述目錄中產生一個 .cfg 中繼資料檔案。.cfg 檔案包含在匯入操作期間用於結構描述驗證的中繼資料。

注意

執行 FLUSH TABLES ... FOR EXPORT 的連線必須在操作執行時保持開啟；否則，當連線關閉時，鎖定釋放後會移除 .cfg 檔案。
將 .ibd 檔案和 .cfg 中繼資料檔案從來源執行個體複製到目的執行個體。例如
```
$> scp /path/to/datadir/test/t1.{ibd,cfg} destination-server:/path/to/datadir/test
```
必須先複製 .ibd 檔案和 .cfg 檔案，然後再釋放共用鎖定，如下一步所述。

注意

如果您從加密的表格空間匯入表格，則 InnoDB 除了 .cfg 中繼資料檔案之外，還會產生一個 .cfp 檔案。 .cfp 檔案必須與 .cfg 檔案一起複製到目的執行個體。 .cfp 檔案包含傳輸金鑰和加密的表格空間金鑰。在匯入時，InnoDB 使用傳輸金鑰來解密表格空間金鑰。相關資訊，請參閱第 17.13 節「InnoDB 靜態資料加密」。
在來源執行個體上，使用 UNLOCK TABLES 來釋放 FLUSH TABLES ... FOR EXPORT 陳述式所取得的鎖定。
```
mysql> USE test;
mysql> UNLOCK TABLES;
```
UNLOCK TABLES 操作也會移除 .cfg 檔案。

在目的執行個體上，匯入表格空間

mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;

匯入分割表格

此範例示範如何匯入分割表格，其中每個表格分割區都位於每個表格一個檔案的表格空間中。

在目的執行個體上，建立一個與您想要匯入的分割表格具有相同定義的分割表格。（您可以使用 SHOW CREATE TABLE 語法取得表格定義。）如果表格定義不符，當您嘗試匯入操作時，會報告結構描述不符的錯誤。
```
mysql> USE test;
mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;
```
在 /datadir/test 目錄中，每個三個分割區都有一個表格空間 .ibd 檔案。
```
mysql> \! ls /path/to/datadir/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd
```
在目的執行個體上，捨棄分割表格的表格空間。（在匯入操作之前，您必須捨棄接收表格的表格空間。）
```
mysql> ALTER TABLE t1 DISCARD TABLESPACE;
```
分割表格的三個表格空間 .ibd 檔案會從 /datadir/test 目錄中捨棄。
在來源執行個體上，執行 FLUSH TABLES ... FOR EXPORT 以靜止您打算匯入的分割表格。當表格被靜止時，只允許對該表格進行唯讀交易。
```
mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;
```
FLUSH TABLES ... FOR EXPORT 確保對指定表格的變更已刷新到磁碟，以便在伺服器執行時可以製作二進制表格副本。當執行 FLUSH TABLES ... FOR EXPORT 時，InnoDB 會在表格的每個表格空間檔案的結構描述目錄中產生 .cfg 中繼資料檔案。
```
mysql> \! ls /path/to/datadir/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd
t1#p#p0.cfg  t1#p#p1.cfg  t1#p#p2.cfg
```
.cfg 檔案包含在匯入表格空間時用於結構描述驗證的中繼資料。FLUSH TABLES ... FOR EXPORT 只能在表格上執行，不能在個別表格分割區上執行。
將 .ibd 和 .cfg 檔案從來源執行個體結構描述目錄複製到目的執行個體結構描述目錄。例如
```
$>scp /path/to/datadir/test/t1*.{ibd,cfg} destination-server:/path/to/datadir/test
```
必須先複製 .ibd 和 .cfg 檔案，然後再釋放共用鎖定，如下一步所述。

注意

如果您從加密的表格空間匯入表格，則 InnoDB 除了 .cfg 中繼資料檔案之外，還會產生 .cfp 檔案。 .cfp 檔案必須與 .cfg 檔案一起複製到目的執行個體。 .cfp 檔案包含傳輸金鑰和加密的表格空間金鑰。在匯入時，InnoDB 使用傳輸金鑰來解密表格空間金鑰。相關資訊，請參閱第 17.13 節「InnoDB 靜態資料加密」。
在來源執行個體上，使用 UNLOCK TABLES 來釋放 FLUSH TABLES ... FOR EXPORT 所取得的鎖定。
```
mysql> USE test;
mysql> UNLOCK TABLES;
```
在目的執行個體上，匯入分割表格的表格空間
```
mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
```

匯入表格分割區

此範例示範如何匯入個別表格分割區，其中每個分割區都位於每個表格一個檔案的表格空間檔案中。

在以下範例中，匯入四個分割區表格的兩個分割區（p2 和 p3）。

在目的執行個體上，建立一個與您想要從中匯入分割區的分割表格具有相同定義的分割表格。（您可以使用 SHOW CREATE TABLE 語法取得表格定義。）如果表格定義不符，當您嘗試匯入操作時，會報告結構描述不符的錯誤。
```
mysql> USE test;
mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;
```
在 /datadir/test 目錄中，每個四個分割區都有一個表格空間 .ibd 檔案。
```
mysql> \! ls /path/to/datadir/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd t1#p#p3.ibd
```
在目的執行個體上，捨棄您打算從來源執行個體匯入的分割區。（在匯入分割區之前，您必須從接收的分割表格中捨棄對應的分割區。）
```
mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;
```
兩個已捨棄分割區的表格空間 .ibd 檔案會從目的執行個體上的 /datadir/test 目錄中移除，留下以下檔案
```
mysql> \! ls /path/to/datadir/test/
t1#p#p0.ibd  t1#p#p1.ibd
```
注意

當在子分割表格上執行 ALTER TABLE ... DISCARD PARTITION ... TABLESPACE 時，允許使用分割區和子分割區表格名稱。當指定分割區名稱時，該分割區的子分割區會包含在操作中。
在來源執行個體上，執行 FLUSH TABLES ... FOR EXPORT 以靜止分割表格。當表格被靜止時，只允許對該表格進行唯讀交易。
```
mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;
```
FLUSH TABLES ... FOR EXPORT 確保對指定表格的變更已刷新到磁碟，以便在執行個體執行時可以製作二進制表格副本。當執行 FLUSH TABLES ... FOR EXPORT 時，InnoDB 會在表格的結構描述目錄中為表格的每個表格空間檔案產生一個 .cfg 中繼資料檔案。
```
mysql> \! ls /path/to/datadir/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd t1#p#p3.ibd
t1#p#p0.cfg  t1#p#p1.cfg  t1#p#p2.cfg t1#p#p3.cfg
```
.cfg 檔案包含在匯入操作期間用於結構描述驗證的中繼資料。FLUSH TABLES ... FOR EXPORT 只能在表格上執行，不能在個別表格分割區上執行。
將分割區 p2 和分割區 p3 的 .ibd 和 .cfg 檔案從來源執行個體結構描述目錄複製到目的執行個體結構描述目錄。
```
$> scp t1#p#p2.ibd t1#p#p2.cfg t1#p#p3.ibd t1#p#p3.cfg destination-server:/path/to/datadir/test
```
必須先複製 .ibd 和 .cfg 檔案，然後再釋放共用鎖定，如下一步所述。

注意

如果您要從加密的表空間匯入分割區，InnoDB 除了會產生 .cfg 元數據檔案之外，還會產生 .cfp 檔案。.cfp 檔案必須與 .cfg 檔案一起複製到目標執行個體。.cfp 檔案包含傳輸金鑰和加密的表空間金鑰。在匯入時，InnoDB 會使用傳輸金鑰來解密表空間金鑰。如需相關資訊，請參閱第 17.13 節「InnoDB 靜態資料加密」。
在來源執行個體上，使用 UNLOCK TABLES 來釋放 FLUSH TABLES ... FOR EXPORT 所取得的鎖定。
```
mysql> USE test;
mysql> UNLOCK TABLES;
```
在目標執行個體上，匯入表格分割區 p2 和 p3
```
mysql> USE test;
mysql> ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;
```
注意

當在子分割表格上執行ALTER TABLE ... IMPORT PARTITION ... TABLESPACE 時，允許使用分割區和子分割區表名稱。當指定分割區名稱時，該分割區的子分割區會包含在此操作中。

限制

可傳輸表空間功能僅支援位於每表一個檔案表空間中的表格。它不支援位於系統表空間或一般表空間中的表格。共用表空間中的表格無法被靜止。
FLUSH TABLES ... FOR EXPORT 不支援具有 FULLTEXT 索引的表格，因為無法刷新全文搜尋輔助表格。在匯入具有 FULLTEXT 索引的表格後，請執行 OPTIMIZE TABLE 以重建 FULLTEXT 索引。或者，在匯出操作之前刪除 FULLTEXT 索引，並在目標執行個體上匯入表格後重新建立索引。
由於 .cfg 元數據檔案的限制，在匯入分割表格時，不會報告分割區類型或分割區定義差異的綱要不符。會報告欄位差異。

使用注意事項

除了包含立即新增或刪除欄位的表格外，ALTER TABLE ... IMPORT TABLESPACE 不需要 .cfg 元數據檔案即可匯入表格。但是，在沒有 .cfg 檔案的情況下匯入時，不會執行元數據檢查，並且會發出類似以下的警告
```
Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)
```
如果預期沒有綱要不符，並且表格不包含任何立即新增或刪除的欄位，才應考慮在沒有 .cfg 元數據檔案的情況下匯入表格。在無法存取元數據的崩潰復原情境中，無需 .cfg 檔案即可匯入的能力可能很有用。
嘗試在沒有使用 .cfg 檔案的情況下匯入使用 ALGORITHM=INSTANT 新增或刪除欄位的表格，可能會導致未定義的行為。
在 Windows 上，InnoDB 會在內部以小寫儲存資料庫、表空間和表格名稱。為了避免在區分大小寫的作業系統（例如 Linux 和 Unix）上發生匯入問題，請使用小寫名稱建立所有資料庫、表空間和表格。確保以小寫建立名稱的一種便利方法是在初始化伺服器之前將lower_case_table_names 設定為 1。（禁止使用與伺服器初始化時使用的設定不同的 lower_case_table_names 設定來啟動伺服器。）
```
[mysqld]
lower_case_table_names=1
```
當在子分割表格上執行 ALTER TABLE ... DISCARD PARTITION ... TABLESPACE 和 ALTER TABLE ... IMPORT PARTITION ... TABLESPACE 時，允許使用分割區和子分割區表名稱。當指定分割區名稱時，該分割區的子分割區會包含在此操作中。

內部運作

以下資訊說明表格匯入程序期間的內部運作和寫入錯誤日誌的訊息。

當在目標執行個體上執行 ALTER TABLE ... DISCARD TABLESPACE 時

表格以 X 模式鎖定。
表空間會與表格分離。

當在來源執行個體上執行 FLUSH TABLES ... FOR EXPORT 時

正在刷新以進行匯出的表格會以共用模式鎖定。
清除協調器執行緒會停止。
髒頁會同步到磁碟。
表格元數據會寫入二進位的 .cfg 檔案。

此操作的預期錯誤日誌訊息

[Note] InnoDB: Sync to disk of '"test"."t1"' started.
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to './test/t1.cfg'
[Note] InnoDB: Table '"test"."t1"' flushed to disk

當在來源執行個體上執行 UNLOCK TABLES 時

二進位的 .cfg 檔案會被刪除。
正在匯入的表格或多個表格的共用鎖定會解除，並且清除協調器執行緒會重新啟動。

此操作的預期錯誤日誌訊息

[Note] InnoDB: Deleting the meta-data file './test/t1.cfg'
[Note] InnoDB: Resuming purge

當在目標執行個體上執行 ALTER TABLE ... IMPORT TABLESPACE 時，匯入演算法會針對正在匯入的每個表空間執行以下操作

會檢查每個表空間頁面是否有損毀。
會更新每個頁面上的空間 ID 和日誌序號 (LSN)。
會驗證標頭頁面的旗標，並更新 LSN。
會更新 B 樹頁面。
頁面狀態會設定為 dirty，以便將其寫入磁碟。

此操作的預期錯誤日誌訊息

[Note] InnoDB: Importing tablespace for table 'test/t1' that was exported
from host 'host_name'
[Note] InnoDB: Phase I - Update all pages
[Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk - done!
[Note] InnoDB: Phase III - Flush changes to disk
[Note] InnoDB: Phase IV - Flush complete

注意

您也可能會收到表空間已捨棄的警告（如果您捨棄了目標表格的表空間）以及由於缺少 .ibd 檔案而無法計算統計資料的訊息

[Warning] InnoDB: Table "test"."t1" tablespace is set as discarded.
7f34d9a37700 InnoDB: cannot calculate statistics for table
"test"."t1" because the .ibd file is missing. For help, please refer to
https://mysqldev.dev.org.tw/doc/refman/en/innodb-troubleshooting.html