Using transportable tablespaces with partitioned tables

by Ivan Groenewold on Apr 6, 2017 12:00:00 AM

Overview

In this post I will explain how to transfer a partitioned table using transportable tablespaces on MySQL 5.6. Note that the operation is not officially supported until MySQL 5.7, as the discard tablespace operation will fail if it is run against a partitioned table on MySQL 5.6. The workaround is transferring each individual partition as if it was a standalone table. Continue reading to learn more about the detailed procedure.

Steps

1. Create the same table structure on the destination server:

 [root@mysqlsandbox ~]# mysql
 Welcome to the MySQL monitor. Commands end with ; or \g.
 Your MySQL connection id is 1
 Server version: 5.6.35-80.0 Percona Server (GPL), Release 80.0, Revision f113994f31
 
 Copyright (c) 2009-2016 Percona LLC and/or its affiliates
 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.
 
 mysql> use test;
 Database changed
 mysql> create table t ( a int, b varchar(50), c varchar(10), `created` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  -> PRIMARY KEY (a, created)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPACT
  -> /*!50100 PARTITION BY RANGE (TO_DAYS(created))
  -> (PARTITION p267 VALUES LESS THAN (736772) ENGINE = InnoDB,
  -> PARTITION p268 VALUES LESS THAN (736779) ENGINE = InnoDB,
  -> PARTITION p269 VALUES LESS THAN (736786) ENGINE = InnoDB) */
  -> ;
 Query OK, 0 rows affected (0.15 sec)

2. On the destination server, you will also create one non-partitioned table with the same structure as the table we want to move, for each partition to be transported e.g.:

 mysql> create table t_p267 like t;
 Query OK, 0 rows affected (0.04 sec)
 
 mysql> create table t_p268 like t;
 Query OK, 0 rows affected (0.04 sec)
 
 mysql> create table t_p269 like t;
 Query OK, 0 rows affected (0.03 sec)
 
 mysql> alter table t_p267 remove partitioning;
 Query OK, 0 rows affected (0.45 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 
 mysql> alter table t_p268 remove partitioning;
 Query OK, 0 rows affected (0.18 sec)
 Records: 0 Duplicates: 0 Warnings: 0
 
 mysql> alter table t_p269 remove partitioning;
 Query OK, 0 rows affected (0.14 sec)
 Records: 0 Duplicates: 0 Warnings: 0

3. Discard the tablespace for each of the tables created above on the destination server:

 mysql> alter table t_p267 discard tablespace;
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> alter table t_p268 discard tablespace;
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> alter table t_p269 discard tablespace;
 Query OK, 0 rows affected (0.01 sec)

4. Prepare table for export on source server. Word of warning: this will lock the table for writes. If the copy process is expected to take more than your configured value of wait_timeout parameter, it is be a good idea to increase it for the session beforehand:

 mysql> set session wait_timeout=28800;
 mysql> flush tables t for export;
 Query OK, 0 rows affected (0.02 sec)

5. On a different terminal (to ensure the flush still going) copy each of the partitions cfg and ibd files to be moved and name them accordingly on the destination:

 [root@mysqlsandbox test]# scp t#P#p267.cfg dest:/var/lib/mysql/test/t_p267.cfg
 [root@mysqlsandbox test]# scp t#P#p267.ibd dest:/var/lib/mysql/test_p267.ibd
 [root@mysqlsandbox test]# scp t#P#p268.cfg dest:/var/lib/mysql/test/t_p268.cfg
 [root@mysqlsandbox test]# scp t#P#p268.ibd dest:/var/lib/mysql/test_p268.ibd
 [root@mysqlsandbox test]# scp t#P#p269.cfg dest:/var/lib/mysql/test/t_p269.cfg
 [root@mysqlsandbox test]# scp t#P#p269.ibd dest:/var/lib/mysql/test_p269.ibd

6. Unlock tables on source server:

 UNLOCK TABLES;

7. Import each partition's tablespace using the table you created to pivot. This step can take several minutes if the partitions are big.

 mysql> alter table t_p267 import tablespace;
 Query OK, 0 rows affected (0.10 sec)
 
 mysql> alter table t_p268 import tablespace;
 Query OK, 0 rows affected (0.04 sec)
 
 mysql> alter table t_p269 import tablespace;
 Query OK, 0 rows affected (0.03 sec)

8. Exchange the tables with the corresponding partition of the target table:

 mysql> alter table t exchange partition p267 with table t_p267;
 Query OK, 0 rows affected (0.10 sec)
 
 mysql> alter table t exchange partition p268 with table t_p268;
 Query OK, 0 rows affected (0.03 sec)
 
 mysql> alter table t exchange partition p269 with table t_p269;
 Query OK, 0 rows affected (0.04 sec)

9. Check the results and the error log. You should see a similar output:

 mysql> select partition_name, table_rows from information_schema.partitions where table_name='t' and table_schema='test';
 +----------------+------------+
 | partition_name | table_rows |
 +----------------+------------+
 | p267 | 10789 |
 | p268 | 1 |
 | p269 | 1 |
 +----------------+------------+
 3 rows in set (0.00 sec)
 
 mysql> system less /var/log/mysqld.log
 ...
 2017-04-05 16:29:01 1245 [Note] InnoDB: Importing tablespace for table 'test/t#P#p267' that was exported from host 'mysqlsandbox.dev'
 2017-04-05 16:29:01 1245 [Note] InnoDB: Phase I - Update all pages
 2017-04-05 16:29:01 1245 [Note] InnoDB: Sync to disk
 2017-04-05 16:29:01 1245 [Note] InnoDB: Sync to disk - done!
 2017-04-05 16:29:01 1245 [Note] InnoDB: Phase III - Flush changes to disk
 2017-04-05 16:29:01 1245 [Note] InnoDB: Phase IV - Flush complete
 2017-04-05 16:29:05 1245 [Note] InnoDB: Importing tablespace for table 'test/t#P#p268' that was exported from host 'mysqlsandbox.dev'
 2017-04-05 16:29:05 1245 [Note] InnoDB: Phase I - Update all pages
 2017-04-05 16:29:05 1245 [Note] InnoDB: Sync to disk
 2017-04-05 16:29:05 1245 [Note] InnoDB: Sync to disk - done!
 2017-04-05 16:29:05 1245 [Note] InnoDB: Phase III - Flush changes to disk
 2017-04-05 16:29:05 1245 [Note] InnoDB: Phase IV - Flush complete
 2017-04-05 16:29:08 1245 [Note] InnoDB: Importing tablespace for table 'test/t#P#p269' that was exported from host 'mysqlsandbox.dev'
 2017-04-05 16:29:08 1245 [Note] InnoDB: Phase I - Update all pages
 2017-04-05 16:29:08 1245 [Note] InnoDB: Sync to disk
 2017-04-05 16:29:08 1245 [Note] InnoDB: Sync to disk - done!
 2017-04-05 16:29:08 1245 [Note] InnoDB: Phase III - Flush changes to disk
 2017-04-05 16:29:08 1245 [Note] InnoDB: Phase IV - Flush complete

10. Drop tables used to pivot on the target server:

 mysql> drop table t_p267;
 Query OK, 0 rows affected (0.00 sec)
 
 mysql> drop table t_p268;
 Query OK, 0 rows affected (0.01 sec)
 
 mysql> drop table t_p269;
 Query OK, 0 rows affected (0.01 sec)

No Comments Yet

Let us know what you think

Share this

Using transportable tablespaces with partitioned tables

Overview

Steps

Share this

No Comments Yet

Subscribe by email