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)

MySQL

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。