Migrating Partitioned Tables between two hosts with MySQL 5.6

When we deal with partitioned tables and transportable tablespaces to move the tables and data between two hosts with substantially bigger tables on MySQL 5.6 we cannot do it straight away. MySQL 5.6 transportable tablespaces do not support Partitioned table migration. If you try to do it , you will have to deal with instance crash with signal 11.

“ERROR 1031 (HY000) at line ###: Table storage engine for …

12:10:13 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.




key_buffer_size=536870912
read_buffer_size=524288
max_used_connections=2
max_threads=2500
thread_count=2
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3116299 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.



Thread pointer: 0x9a972820
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong…

Lets take a look at an example :

Create a table with 4 partitions T1 on host A and then we will insert rows in the table.

CREATE TABLE t1 (
i int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (i)
PARTITIONS 4 */




mysql> select * from t1;
+------+
| i |
+------+
| 1 |
| 4 |
| 2 |
| 6 |
+------+
4 rows in set (0.00 sec)

check the files create under the database test for table t1.

$ ls
t1.frm t1.par t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibd

Lets prepare table for export and copy the files to test2 database

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

now create a table t1 using table definition as above.

mysql> CREATE TABLE t1 ( i int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (i) PARTITIONS 4 */;
Query OK, 0 rows affected (0.03 sec)

Now try to discard tablespace using following command.

mysql> alter table t1 discard tablespace;
ERROR 1031 (HY000): Table storage engine for 't1' doesn't have this option

As we see , we cannot discard tablespace of partitioned table as it is not supported in version MySQL 5.6.

Therefore we need to think Out of Box . When I encountered this situation I was working on two different production servers with table to move was of 66G in size. What I did is that I created another table using following query on source . In this example my source will be test database and table name is t1 , I will create another table t2 out of t1 with data. This will create a table without indexes and will move all the data into that table.

mysql> create table t2 select * from t1;
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0




mysql> show create table t2;
+-------+---------------------------------------------------
| Table | Create Table |
+-------+----------------------------------------------------
| t2 | CREATE TABLE t2 (
i int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------
1 row in set (0.00 sec)

Now we will create this empty table in test2 database with definition shown above . Then we will discard its tablespace .

mysql> CREATE TABLE t2 (
-> i int(11) DEFAULT NULL

-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)



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

Now we will move test1.t2 table’s tablespace to test2 after flushing.

mysql> flush table t2 for export;
Query OK, 0 rows affected (0.00 sec)




[mysql@vm028436 ~]$ cd data/test/
[mysql@vm028436 test]$ ls
t1.frm t1.par t1#P#p0.ibd t1#P#p1.ibd t1#P#p2.ibd t1#P#p3.ibd t2.cfg t2.frm t2.ibd
[mysql@vm028436 test]$ scp -r t2.cfg ../test2/
[mysql@vm028436 test]$ scp -r t2.ibd ../test2/

Once all the t2.ibd and t2.cfg table files being copied test2 database , we will import the tablespace using following command as shown below.

mysql> use test2
Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A
Database changed
mysql> alter table t2 import tablespace;
Query OK, 0 rows affected (0.04 sec)

As we can see it is successfully imported but here is a catch when we are dealing with big partitioned tables. we will struggle with select statements as tables are not indexed. I suggest that we can alter t2 and add primary key same as of t1 and then start migrating data using similar kind of statement shown below.

mysql> insert into t1 select * from t2;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0

Hence migration completed .

Thanks for visiting My Blog . I hope you will enjoyed it .

 

Leave a Reply

Your email address will not be published.