transportable tablespace

最新推荐文章于 2024-04-19 17:29:33 发布

twc19890523

最新推荐文章于 2024-04-19 17:29:33 发布

阅读量681

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/twc19890523/article/details/73468018

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

实验环境：
源端：192.168.99.111
目标端：192.168.99.112
源端test1.actor–>目标端test2.actor

transportable tablespace的应用场景、限制及注意事项，具体请参考官方手册
https://dev.mysql.com/doc/refman/5.7/en/tablespace-copying.html#innodb-transportable-tablespace-limitations

Example 1: Copying an InnoDB Table to Another Instance

1、准备环境

源端：

root@localhost:mysql3306.sock [sakila]>use test1;
Database changed
root@localhost:mysql3306.sock [test1]>show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| test1           |
+-----------------+
1 row in set (0.00 sec)

root@localhost:mysql3306.sock [test1]>CREATE TABLE `actor` (
    ->   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->   `first_name` varchar(45) NOT NULL,
    ->   `last_name` varchar(45) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`actor_id`),
    ->   KEY `idx_actor_last_name` (`last_name`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3306.sock [test1]>show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| actor           |
| test1           |
+-----------------+
2 rows in set (0.00 sec)

root@localhost:mysql3306.sock [test1]>select * from actor;
Empty set (0.00 sec)

root@localhost:mysql3306.sock [test1]>insert into actor select * from sakila.actor;
Query OK, 200 rows affected (0.00 sec)
Records: 200  Duplicates: 0  Warnings: 0

目标端：

root@localhost:mysql3306.sock [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test1              |
| world              |
| world_x            |
+--------------------+
9 rows in set (0.08 sec)

root@localhost:mysql3306.sock [(none)]>create database test2;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: *** NONE ***

Query OK, 1 row affected (0.01 sec)

root@localhost:mysql3306.sock [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| menagerie          |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| test1              |
| test2              |
| world              |
| world_x            |
+--------------------+
10 rows in set (0.00 sec)

root@localhost:mysql3306.sock [(none)]>use test2;
Database changed
root@localhost:mysql3306.sock [test2]>CREATE TABLE `actor` (
    ->   `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    ->   `first_name` varchar(45) NOT NULL,
    ->   `last_name` varchar(45) NOT NULL,
    ->   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    ->   PRIMARY KEY (`actor_id`),
    ->   KEY `idx_actor_last_name` (`last_name`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

root@localhost:mysql3306.sock [test2]>show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| actor           |
+-----------------+
1 row in set (0.00 sec)

2、在目标端DISCARD TABLESPACE

[root@centos7 data]# cd /data/mysql/mysql3306/data/test2
[root@centos7 test2]# ll
total 128
-rw-r----- 1 mysql mysql   8694 Jun 19 14:51 actor.frm
-rw-r----- 1 mysql mysql 114688 Jun 19 14:51 actor.ibd
-rw-r----- 1 mysql mysql     61 Jun 19 14:49 db.opt
[root@centos7 test2]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:mysql3306.sock [(none)]>use test2;
No connection. Trying to reconnect...
Connection id:    5
Current database: *** NONE ***

Database changed
root@localhost:mysql3306.sock [test2]>show tables;
+-----------------+
| Tables_in_test2 |
+-----------------+
| actor           |
+-----------------+
1 row in set (0.00 sec)

root@localhost:mysql3306.sock [test2]>alter table actor discard tablespace;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3306.sock [test2]>quit
Bye
[root@centos7 test2]# ll
total 16
-rw-r----- 1 mysql mysql 8694 Jun 19 14:51 actor.frm
-rw-r----- 1 mysql mysql   61 Jun 19 14:49 db.opt

3、在源端FLUSH TABLES … FOR EXPORT

root@localhost:mysql3306.sock [(none)]>use test1;
Database changed
root@localhost:mysql3306.sock [test1]>flush tables actor for export;
Query OK, 0 rows affected (0.01 sec)

注意：执行完此命令后不要退出连接，另打开一个连接执行scp操作

4、从源端把.ibd和.cfg copy到目标端

[root@centos7 ~]# cd /data/mysql/mysql3306/data/test1
[root@centos7 test1]# ll
total 240
-rw-r----- 1 mysql mysql    649 Jun 19 15:05 actor.cfg
-rw-r----- 1 mysql mysql   8694 Jun 19 14:41 actor.frm
-rw-r----- 1 mysql mysql 114688 Jun 19 14:55 actor.ibd
-rw-r----- 1 mysql mysql     61 Jun 18 22:02 db.opt
-rw-r----- 1 mysql mysql   8556 Jun 18 22:02 test1.frm
-rw-r----- 1 mysql mysql  98304 Jun 18 22:02 test1.ibd
[root@centos7 test1]# scp /data/mysql/mysql3306/data/test1/actor.{cfg,ibd} 192.168.99.112:/data/mysql/mysql3306/data/test2/
The authenticity of host '192.168.99.112 (192.168.99.112)' can't be established.
ECDSA key fingerprint is a5:2a:57:b4:91:c7:f3:b5:d0:0d:b3:fb:ef:c7:31:b8.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.99.112' (ECDSA) to the list of known hosts.
root@192.168.99.112's password: 
actor.cfg                                                                                                                                                    100%  649     0.6KB/s   00:00    
actor.ibd                                                                                                                                                    100%  112KB 112.0KB/s   00:00

5、在源端执行unlock tables释放锁

root@localhost:mysql3306.sock [(none)]>use test1;
Database changed
root@localhost:mysql3306.sock [test1]>unlock tables;
Query OK, 0 rows affected (0.00 sec)

6、在目标端import tablespace

[root@centos7 test2]# ll
total 132
-rw-r----- 1 root  root     649 Jun 19 15:08 actor.cfg
-rw-r----- 1 mysql mysql   8694 Jun 19 14:51 actor.frm
-rw-r----- 1 root  root  114688 Jun 19 15:08 actor.ibd
-rw-r----- 1 mysql mysql     61 Jun 19 14:49 db.opt
[root@centos7 test2]# chown -R mysql:mysql *
[root@centos7 test2]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost:mysql3306.sock [(none)]>use test2;
Database changed
root@localhost:mysql3306.sock [test2]>alter table actor import tablespace;
Query OK, 0 rows affected, 1 warning (0.04 sec)

root@localhost:mysql3306.sock [test2]>select count(*) from actor;
+----------+
| count(*) |
+----------+
|      200 |
+----------+
1 row in set (0.00 sec)

对于一个普通的InnoDB表进行传输表空间操作到此就算结束了，很简单实用，在MySQL5.7版本开始支持分区表，更多示例请参考MySQL官方手册
https://dev.mysql.com/doc/refman/5.7/en/innodb-transportable-tablespace-examples.html