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)

发表评论

电子邮件地址不会被公开。