DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • How to Repair Corrupt MySQL Database Tables Step-by-Step
  • Harnessing the Power of AWS Aurora for Scalable and Reliable Databases
  • Optimizing MySQL Performance: Best Practices for Database Efficiency
  • Fine-Tuning Performance, Resolving Common Issues in FinTech Application With MySQL

Trending

  • A Guide to Container Runtimes
  • Segmentation Violation and How Rust Helps Overcome It
  • Building Scalable and Resilient Data Pipelines With Apache Airflow
  • Java's Quiet Revolution: Thriving in the Serverless Kubernetes Era
  1. DZone
  2. Data Engineering
  3. Databases
  4. How to Move a MySQL Partition From One Table to Another

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.

By 
Pablo Padua user avatar
Pablo Padua
·
Jan. 12, 17 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
10.2K Views

Join the DZone community and get the full member experience.

Join For Free

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:

CREATE TABLE live_tbl (
    some_id bigint(20) NOT NULL DEFAULT '0',
    summary_date date NOT NULL,
    PRIMARY KEY (some_id,summary_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(summary_date)
(PARTITION p201203 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
PARTITION p201204 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB,
PARTITION p201205 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB,
PARTITION p201206 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;


CREATE TABLE archive_tbl (
    some_id bigint(20) NOT NULL DEFAULT '0',
    summary_date date NOT NULL,
    PRIMARY KEY (some_id,summary_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
/*!50500 PARTITION BY RANGE COLUMNS(summary_date)
(PARTITION p201109 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p201110 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p201111 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION p201112 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
PARTITION p201201 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB,
PARTITION p201202 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB,
PARTITION future VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;


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.

mysql> USE thedb
mysql> FLUSH TABLE live_tbl FOR EXPORT;


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

shell> cp /var/lib/mysql/thedb/live_tbl#P#<em>p201203</em>.ibd /tmp/dest_tbl_tmp.ibd


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.

mysql> UNLOCK TABLES;


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.

mysql> CREATE TABLE dest_tbl_tmp LIKE archive_tbl;
mysql> ALTER TABLE dest_tbl_tmp REMOVE PARTITIONING;
mysql> ALTER TABLE dest_tbl_tmp DISCARD TABLESPACE;



4. Swap the Tablespace With the Destination Table’s Partition Tablespace

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.)

mysql> ALTER TABLE archive_tbl REORGANIZE PARTITION future INTO (
    PARTITION <em>p201203</em> VALUES LESS THAN ('2012-04-01'),
    PARTITION future VALUES LESS THAN (MAXVALUE)
);
mysql> ALTER TABLE archive_tbl EXCHANGE PARTITION <em>p201203</em> WITH TABLE dest_tbl_tmp;


5.Check That the Partitions Are Correctly Exchanged Before Dropping the One From the Source Table 

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


For more information on why these steps are needed, please check this documentation link for ALTER TABLE … EXCHANGE PARTITION.

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:

  • Hanging “System Lock” when executing “flush table … for export”https://bugs.mysql.com/bug.php?id=77011.
  • Alter table import tablespace creates a temporary tablehttps://bugs.mysql.com/bug.php?id=75706.
Database Partition (database) MySQL

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

Opinions expressed by DZone contributors are their own.

Related

  • How to Repair Corrupt MySQL Database Tables Step-by-Step
  • Harnessing the Power of AWS Aurora for Scalable and Reliable Databases
  • Optimizing MySQL Performance: Best Practices for Database Efficiency
  • Fine-Tuning Performance, Resolving Common Issues in FinTech Application With MySQL

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!