Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How to Move a MySQL Partition From One Table to Another

DZone's Guide to

How to Move a MySQL Partition From One Table to Another

If you're using MySQL before 5.7, shifting partitions can be a hassle. Fortunately, you can chain your moves together to shift your partition from one table to another.

· Database Zone
Free Resource

Whether you work in SQL Server Management Studio or Visual Studio, Redgate tools integrate with your existing infrastructure, enabling you to align DevOps for your applications with DevOps for your SQL Server databases. Discover true Database DevOps, brought to you in partnership with Redgate.

Move a MySQL Partition

In this blog post, we’ll look at how to move a MySQL partition from one table to another, for MySQL versions before 5.7.

Up to version 5.7, MySQL had a limitation that made it impossible to directly exchange partitions between partitioned tables. Now and then, we get questions about how to import an .ibd for use as a partition in a table, as well as how to exchange partitions with another partitioned table. Below is step-by-step instructions on how to move a partition from one table to another.

In this example, one of our customers had two tables with the following structures:

And their (likely obvious) goal is to move (not copy) the oldest partition from live_tbl to archive_tbl. To achieve this, we came up with the following procedure:

For the following, we assume:

  • The datadir is “/var/lib/mysql/”.
  • MySQL Server is run by “mysql” Linux user.
  • “p201203” is the partition name you want to move.
  • “live_tbl is the source table from where you want to move the partition.
  • “archive_tbl” is the destination table to where you want to move the partition.
  • “dest_tbl_tmp” is the temporary table we will create, using the same CREATE TABLE criteria as in the live_tbl.
  • “thedb” is the database name.

1. Copy the .ibd Data File From That Particular Partition

First, make sure you flush any pending changes to disk and that the table is locked, so that binary table copies can be made while the server is running. Keep in mind that the table will be locked while you copy the .ibd file. All reads/writes during that time will be blocked.

Important: Don’t close this session or the lock will be released.

Open another session and copy the .ibd file to a temporary folder.

After you copy the .ibd file to the temporary folder, go back to the MySQL session and unlock the table so that all reads and writes to that particular table are allowed again.

2. Prepare a Temporary Table to Import the Tablespace

Create a temporary table exactly like the one into which you want to import the partition. Remove the partitioning on it and discard the tablespace so that it is ready for the .ibd import.

3. Import the Tablespace to the Temporary Table

Place the .ibd file in the appropriate folder, set the correct permissions and ownership and then import the tablespace to the temporary table.

Partition according to your own schema. (This is just an example using date values. In our case, we have to REORGANIZE PARTITION to accommodate a new LESS THAN range before the MAXVALUE.)

SELECT * FROM archive_tbl;
SELECT * FROM dest_tbl_tmp;
SELECT * FROM live_tbl;
ALTER TABLE live_tbl DROP PARTITION <em>p201203</em>;


In MySQL version 5.7, it is possible to exchange partitions without the unpartitioned table step, as described here.

There are bugs related to the steps in this guide that might be useful to take into consideration:

It’s easier than you think to extend DevOps practices to SQL Server with Redgate tools. Discover how to introduce true Database DevOps, brought to you in partnership with Redgate

Topics:
partition ,mysql ,database ,tutorial ,tablespace

Published at DZone with permission of Pablo Padua, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}