MySQL :: MySQL 9.0 參考手冊 :: 17.6.1.3 匯入 InnoDB 表格

版本 9.0

8.4 目前版本
8.0
5.7

8.0 日文

MySQL 9.0 參考手冊 / ... / 匯入 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 檔案，如下一個步驟所述。

注意

如果您要從加密的表格空間匯入表格，除了 .cfg 中繼資料檔案之外，InnoDB 還會產生 .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 檔案，如下一個步驟所述。

注意

如果您要從加密的表格空間匯入表格，除了 .cfg 中繼資料檔案之外，InnoDB 還會產生 .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 檔案，如下一個步驟所述。

注意

如果您要從加密的表格空間匯入分割區，除了 .cfg 中繼資料檔案之外，InnoDB 還會產生 .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 時，允許使用分割區和子分割區資料表名稱。當指定分割區名稱時，該分割區的子分割區會包含在操作中。

限制

可傳輸的資料表空間功能僅支援位於每個資料表檔案的資料表空間中的資料表。不支援位於系統資料表空間或一般資料表空間中的資料表。共用資料表空間中的資料表無法靜止。
具有 FULLTEXT 索引的資料表不支援 FLUSH TABLES ... FOR EXPORT，因為全文搜尋輔助資料表無法刷新。在匯入具有 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 檔案進行匯入的能力可能會很有用。
嘗試匯入使用 ALGORITHM=INSTANT 新增或捨棄欄位的資料表，而不使用 .cfg 檔案可能會導致未定義的行為。
在 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。
會更新 Btree 頁面。
頁面狀態會設定為髒頁，使其寫入磁碟。

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

[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

PREV HOME UP NEXT