MySQL 5.7.4 now supports Transportable Tablespaces for InnoDB Partitions.

When InnoDB transportable tablespaces was introduced in 5.6 it did not support partitions since it could be accomplished in combination with EXCHANGE PARTITION. To make it easier to use transportable tablespaces for partitioned tables we added support for DISCARD/IMPORT TABLESPACE for partitioned tables as well as single partitions in 5.7.4.To get a consistent full copy of the tablespace files one use FLUSH TABLES <list of tables> FOR EXPORT

To restore the all the partitions (optionally on another server):

To restore only a subset of the partitions do:

So in 5.7 it will be as easy to handle partitions and partitioned tables as for non-partitioned InnoDB tables.

But how to do this in 5.6?

FLUSH TABLES <list of tables> FOR EXPORT also works for partitioned tables in 5.6, so the above commands for FLUSH TABLES FOR EXPORT in 5.7 can be used in 5.6 as well for copy/backup purpose.

But to restore a partition in 5.6 one need to use EXCHANGE PARTITION for each partition:

Notes:

  • If done concurrently as other DML/DDL’s happen I recommend LOCK TABLE or other tools guarding against other sessions either alter the table or alters the data in between the statements. Also verify that the supposed empty tmp_table did not get any new data in between the statements.
  • EXCHANGE PARTITION currently scans the table to replace the partition to validate that every row will fit into the partition, I hope to address this by adding a ‘WITHOUT VALIDATION’ clause to the EXCHANGE command, to allow the DBA to directly exchange the data without scanning every record. IMPORT PARTITION TABLESPACE does not validate the data to match the partition.

One thought on “MySQL 5.7.4 now supports Transportable Tablespaces for InnoDB Partitions.

Leave a Reply

Your email address will not be published. Required fields are marked *


3 + eight =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">