MySQL 傳輸表空間

將file-per-table表空間複製到另一個例項
如何將一個file-per-table表空間從一個MySQL例項複製到另一個例項中，也就是眾所周知的可傳輸表空間特性。

有很多原因可以解釋為什麼你可以將一個InnoDB檔案表空間複製到不同的例項中:
.在不增加生產伺服器額外負載的情況下執行報表。

.在新的從伺服器上為表設定相同的資料

.在出現問題或錯誤後恢復表或分割槽的備份版本。

.作為一種比mysqldump命令匯入更快的移動資料的方法。資料立即可用，而不需要重新插入和重建索引

.將每個file-per-table表空間移動到具有更適合系統需求的儲存介質的伺服器。例如，您可能希望在SSD裝置上有繁忙的表，或者在高容量HDD裝置上有大型表。

限制和使用說明
.只有當innodb_file_per_table設定為ON(預設設定)時，才可以複製表空間。駐留在共享系統表空間中的表不能被靜默。

.當一個表被靜默時，只允許在受影響的表上執行只讀事務

.在匯入表空間時，頁面大小必須與匯入例項的頁面大小相匹配。

.當foreign_key_checks設定為1時，對於父-子(主-外來鍵)關係的表空間不支援DISCARD TABLESPACE。在丟棄父-子表的表空間之前，設定foreign_key_checks=0。分割槽InnoDB表不支援外來鍵。

.ALTER TABLE……IMPORT TABLESPACE不會對匯入的資料強制外來鍵約束。如果表之間存在外來鍵約束，那麼所有表都應該在同一(邏輯)時間點匯出。分割槽InnoDB表不支援外來鍵。

.ALTER TABLE……IMPORT TABLESPACE 和 ALTER TABLE…IMPORT PARTITION…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檔案進行匯入可能會更方便。此外，在無法從.ibd檔案收集後設資料的崩潰恢復場景中，不需要.cfg檔案就可以匯入。

.由於.cfg後設資料檔案的限制，當為分割槽表匯入表空間檔案時，不會對分割槽型別或分割槽定義差異報告模式不匹配。列差異被報告。

.當在子分割槽表上執行ALTER TABLE ... DISCARD PARTITION ... TABLESPACE和ALTER TABLE ... IMPORT PARTITION ... TABLESPACE，分割槽和子分割槽表名都是允許的。當指定分割槽名時，該分割槽的子分割槽將包含在操作中。

.如果兩個例項都有GA(通用可用性)狀態，並且它們的版本在同一系列可以從另一個MySQL伺服器例項匯入表空間檔案。否則，該檔案必須是在匯入它的同一個伺服器例項上所建立

.在複製場景中，innodb_file_per_table必須在主節點和從節點上都設定為ON。

.在Windows環境下，InnoDB內部儲存資料庫、表空間和表名時使用小寫字母。為了避免在區分大小寫的作業系統(如Linux、UNIX)上的匯入問題，請在建立資料庫、表空間和表時使用小寫名稱。一種方便的方法是在建立資料庫、表空間或表之前，在my.cnf或my.ini檔案的[mysqld]部分中新增下面這一行:

[mysqld]
lower_case_table_names=1

.alter table ... discard tablespace和alter table ... import tabelspace不支援屬於InnoDB通用表空間中的表。

.InnoDB表的預設行格式可以透過innodb_default_row_format配置選項進行配置。如果匯入的表沒有明確定義行格式(ROW_FORMAT)，或者使用了ROW_FORMAT=DEFAULT，那麼如果源例項上的innodb_default_row_format設定與目標例項上的innodb_default_row_format設定不一致，可能會導致模式不匹配錯誤

.在使用InnoDB表空間加密特性匯出加密的表空間時，InnoDB除了生成一個.cfg後設資料檔案外，還會生成一個.cfp檔案。在目標例項上執行ALTER TABLE…IMPORT TABLESPACE之前，必須將.cfp檔案與.cfg檔案和表空間檔案一起復制到目標例項中。cfp檔案包含一個傳輸金鑰和一個加密的表空間金鑰。在匯入時，InnoDB使用傳輸金鑰來解密表空間金鑰。

傳輸表空間示例
例如1:複製一個InnoDB表到另一個例項
這個過程演示瞭如何將一個普通的InnoDB表從一個正在執行的MySQL伺服器例項複製到另一個正在執行的例項。可以使用相同的過程在相同的例項上執行全表恢復，只是做了一些小小的調整。
1. 在源例項上，如果不存在表，則建立一個表:

mysql> use test;
Database changed
mysql> create table t(c1 int) engine=innodb;
Query OK, 0 rows affected (0.12 sec)
mysql> insert into t values(1);
Query OK, 1 row affected (0.16 sec)

2.在目標例項上，如果不存在表，則建立表:

mysql> use test;
Database changed
mysql> create table t(c1 int) engine=innodb;
Query OK, 0 rows affected (0.09 sec)

3.在目標例項上，丟棄現有的表空間。(在匯入表空間之前，InnoDB必須丟棄連線到接收表空間的表空間。)

[mysql@localhost test]$ ls -lrt
總用量 112
-rw-r-----. 1 mysql mysql    67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql  8556 3月  15 16:57 t.frm
-rw-r-----. 1 mysql mysql 98304 3月  15 16:57 t.ibd
mysql> alter table t discard tablespace;
Query OK, 0 rows affected (0.17 sec)
[mysql@localhost test]$ ls -lrt
總用量 16
-rw-r-----. 1 mysql mysql   67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql 8556 3月  15 16:57 t.frm

4.在源例項上，執行FLUSH TABLES…FOR EXPORT將暫停表並建立.cfg後設資料檔案

mysql> flush tables t for export;
Query OK, 0 rows affected (0.00 sec)
[mysql@localhost test]$ ls -lrt
總用量 116
-rw-r-----. 1 mysql mysql    67 3月  15 16:53 db.opt
-rw-r-----. 1 mysql mysql  8556 3月  15 16:54 t.frm
-rw-r-----. 1 mysql mysql 98304 3月  15 16:54 t.ibd
-rw-r-----. 1 mysql mysql   371 3月  15 17:00 t.cfg

在InnoDB資料目錄下建立後設資料(.cfg)
注意:FLUSH TABLES …… FOR EXPORT在MySQL 5.6.6版本中可用。該語句確保對指定表的更改已重新整理到磁碟，以便在例項執行時可以生成二進位制表副本。當FLUSH TABLES ... FOR EXPORT時，InnoDB會在表所在的資料庫目錄中生成一個.cfg檔案。cfg檔案中包含匯入表空間檔案時用於模式驗證的後設資料。

5.將.ibd檔案和.cfg後設資料檔案從源例項複製到目標例項

[mysql@localhost test]$ scp t.{ibd,cfg} mysql@192.168.1.243:/mysqldata/mysql/test/
mysql@192.168.1.243's password:
t.ibd                                                                                                                                                                                                    100%   96KB  96.0KB/s   00:00
t.cfg                                                                                                                                                                                                    100%  371     0.4KB/s   00:00
[mysql@localhost test]$

在釋放共享鎖之前必須複製.ibd與.cfg檔案。

6.在源例項上，使用unlock tables語句來釋放由flush tables ... for export所獲取的鎖:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
7.在目標例項上，匯入表空間:
mysql> alter table t import tablespace;
Query OK, 0 rows affected (0.15 sec)
mysql> desc t;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> select * from t;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

可以看到表t從一個例項遷移到另一個例項上。