MySQL 拷貝一個InnoDB分割槽表到另一個例項

拷貝一個InnoDB分割槽表到另一個例項
這個過程演示瞭如何將一個InnoDB分割槽表從一個正在執行的MySQL伺服器例項複製到另一個正在執行的例項。同樣的過程，只要稍微做些調整，就可以在同一個例項上對InnoDB分割槽表執行完全恢復。

1.在源例項上，如果不存在分割槽表，則建立分割槽表。在下面的例子中，建立了一個包含三個分割槽(p0, p1, p2)的表

mysql> use test;
Database changed
mysql> create table t1(i int) engine=innodb partition by key(i) partitions 3;
Query OK, 0 rows affected (0.38 sec)
mysql> insert into t1 values(1),(2),(3),(4),(5),(6),(7),(8),(9);
Query OK, 9 rows affected (0.03 sec)
Records: 9  Duplicates: 0  Warnings: 0
mysql> select * from t1;
+------+
| i    |
+------+
|    4 |
|    5 |
|    1 |
|    6 |
|    7 |
|    2 |
|    3 |
|    8 |
|    9 |
+------+
9 rows in set (0.00 sec)

在/mysqldata/mysql/test目錄中，對於三個分割槽都有一個單獨的表空間(.ibd)檔案:

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
總用量 304
-rw-r-----. 1 mysql mysql    67 3月  15 16:53 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:43 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p0.ibd

2.在目標例項上，建立相同的分割槽表:

mysql> use test;
Database changed
mysql> create table t1(i int) engine=innodb partition by key(i) partitions 3;
Query OK, 0 rows affected (0.20 sec)

在/mysqldata/mysql/test目錄中，對於三個分割槽都有一個單獨的表空間(.ibd)檔案:

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
總用量 304
-rw-r-----. 1 mysql mysql    67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:45 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 15:45 t1#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:45 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:45 t1#P#p2.ibd

3.在目標例項上，丟棄分割槽表的表空間。(在將表空間匯入目標例項之前，必須丟棄附加到接收表的表空間。)

mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.09 sec)

組成分割槽表表空間的三個.ibd檔案從/mysqldata/mysql/tes目錄中被丟棄，留下以下檔案

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
總用量 16
-rw-r-----. 1 mysql mysql   67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql 8554 3月  16 15:45 t1.frm

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

mysql> flush tables t1 for export;
Query OK, 0 rows affected (0.01 sec)

在源例項的/mysqldata/mysql/test目錄中建立後設資料(.cfg)檔案，每個表空間(.ibd)檔案對應一個後設資料檔案

[root@localhost ~]# cd /mysqldata/mysql/test
[root@localhost test]# ls -lrt
總用量 316
-rw-r-----. 1 mysql mysql    67 3月  15 16:53 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:43 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p2.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 15:43 t1#P#p0.ibd
-rw-r-----. 1 mysql mysql   375 3月  16 16:00 t1#P#p1.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:00 t1#P#p0.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:00 t1#P#p2.cfg

FLUSH TABLES……FOR EXPORT語句確保對指定表的更改已重新整理到磁碟，以便在例項執行時可以進行二進位制表拷貝。當執行FLUSH TABLES ... FOR EXPORT時，InnoDB會在資料庫目錄中為表的表空間檔案生成一個.cfg後設資料檔案。.cfg檔案中包含匯入表空間檔案時驗證模式的後設資料。FLUSH TABLES ... FOR EXPORT只能在表上執行，而不能在單獨的表分割槽上執行。

5.將.ibd和.cfg檔案從源例項資料庫目錄複製到目標例項資料庫目錄。例如

[root@localhost test]# scp t1*.{ibd,cfg} mysql@192.168.1.243:/mysqldata/mysql/test/
mysql@192.168.1.243's password:
t1#P#p0.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t1#P#p1.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t1#P#p2.ibd                                                                                                                                                                                              100%   96KB  96.0KB/s   00:00
t1#P#p0.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
t1#P#p1.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
t1#P#p2.cfg                                                                                                                                                                                              100%  375     0.4KB/s   00:00
[root@localhost test]#
[root@localhost test]# ls -lrt
總用量 316
-rw-r-----. 1 mysql mysql    67 3月  15 16:55 db.opt
-rw-r-----. 1 mysql mysql  8554 3月  16 15:45 t1.frm
-rw-r-----. 1 mysql mysql 98304 3月  16 16:06 t1#P#p0.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:06 t1#P#p1.ibd
-rw-r-----. 1 mysql mysql 98304 3月  16 16:06 t1#P#p2.ibd
-rw-r-----. 1 mysql mysql   375 3月  16 16:06 t1#P#p0.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:06 t1#P#p1.cfg
-rw-r-----. 1 mysql mysql   375 3月  16 16:06 t1#P#p2.cfg

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

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

在源例項上釋放鎖時，會向mysql日誌檔案寫入刪除.cfg檔案的資訊:

2022-03-16T08:08:27.653352Z 10 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p0.cfg'
2022-03-16T08:08:27.653656Z 10 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p1.cfg'
2022-03-16T08:08:27.654214Z 10 [Note] InnoDB: Deleting the meta-data file './test/t1#P#p2.cfg'
2022-03-16T08:08:27.654256Z 10 [Note] InnoDB: Resuming purge

7.在目標例項上，匯入表空間:

mysql> select * from t1;
ERROR 1814 (HY000): Tablespace has been discarded for table 't1'
mysql> alter table t1 discard tablespace;
Query OK, 0 rows affected (0.09 sec)
mysql> alter table t1 import tablespace;
Query OK, 0 rows affected (0.46 sec)
mysql> select * from t1;
+------+
| i    |
+------+
|    4 |
|    5 |
|    1 |
|    6 |
|    7 |
|    2 |
|    3 |
|    8 |
|    9 |
+------+
9 rows in set (0.01 sec)