Personal blog of Yzmir Ramirez

Space constraint has been an endless and painstaking process for many of us, especially in systems that have a high number of transactions and data growth exceeding hundreds of GBs in a matter of days. In this blog, I will share a solution to remove this space and remove rows from tables in a few seconds regardless of the size of a table without causing any additional load on the database using table partitions.

The first approach that comes to anyone’s mind for deleting the row is using a DELETE query in SQL. Suppose, one wants to delete rows from a table that are older than one year—the query for such operations would be like this:

DELETE FROM salaries WHERE from_date <DATE_SUB(NOW(),INTERVAL 1 YEAR);

The above query is pretty straightforward but there are a few caveats:

• Server business will grow exponentially and could impact the usual traffic on the server.
• To speed up the above query we need to create an appropriate index so that the query can be executed in minimal time and have less impact on server performance.
• If we are using binlog_format as ROW, a huge number of bin logs would be created which could choke I/O of servers and require extra cleanup.
• Even after deleting all the rows, space won’t be freed. MySQL won’t shrink tablespace and storage won’t be released to the file system. To release it to the file system, we need to recreate the table by running ANALYZE or an ALTER.

One way to get around this is using the Percona pt-archiver. There is an excellent blog you may want to follow for mysql data archival with minimal disruption. But the archiver process will take time as it also considers system load, replica lag, and specified parameters to throttle the archiving process without affecting ongoing traffic.

What I propose here is using MySQL partitioning, which is a much faster approach.

What is partitioning?

In MySQL, the InnoDB storage engine has long supported the notion that a tablespace and the MySQL Server, even prior to the introduction of partitioning, could be configured to employ different physical directories for storing different databases. Partitioning takes this notion a step further, allowing users to save portions of the table according to a user-defined rule. The user-selected rule by which data can be divided is known as a partitioning function, which could be a simple rule against a set of ranges or value lists, an internal hashing function, or a linear hashing function.

When we partition the table data-file is split across multiple partitions of smaller data-files. The operation we do against that specific range of data, will not affect the whole table as only one data file is touched.

Table without partition:

centos: employees # ls -lh salaries*#*
-rw-r-----. 1 mysql mysql 104G Oct 11 05:47 salaries.ibd

Table with partition:

centos: employees # ls -lh salaries*#*
-rw-r-----. 1 mysql mysql 9.0G Oct 11 05:47 salaries#P#p01.ibd
-rw-r-----. 1 mysql mysql 9.0G Oct 11 05:47 salaries#P#p02.ibd
-rw-r-----. 1 mysql mysql  10G Oct 11 05:47 salaries#P#p03.ibd
-rw-r-----. 1 mysql mysql  11G Oct 11 05:47 salaries#P#p04.ibd
-rw-r-----. 1 mysql mysql  11G Oct 11 05:47 salaries#P#p05.ibd
-rw-r-----. 1 mysql mysql  12G Oct 11 05:47 salaries#P#p06.ibd
-rw-r-----. 1 mysql mysql  12G Oct 11 05:47 salaries#P#p07.ibd
-rw-r-----. 1 mysql mysql  13G Oct 11 05:47 salaries#P#p08.ibd
-rw-r-----. 1 mysql mysql  14G Oct 11 05:47 salaries#P#p09.ibd
-rw-r-----. 1 mysql mysql  14G Oct 11 05:47 salaries#P#p10.ibd
-rw-r-----. 1 mysql mysql  15G Oct 11 05:47 salaries#P#p11.ibd
-rw-r-----. 1 mysql mysql  15G Oct 11 05:47 salaries#P#p12.ibd
-rw-r-----. 1 mysql mysql  16G Oct 11 05:47 salaries#P#p13.ibd
-rw-r-----. 1 mysql mysql  16G Oct 11 05:47 salaries#P#p14.ibd
-rw-r-----. 1 mysql mysql  17G Oct 11 05:47 salaries#P#p15.ibd
-rw-r-----. 1 mysql mysql  17G Oct 11 05:47 salaries#P#p16.ibd
-rw-r-----. 1 mysql mysql  16G Oct 11 05:47 salaries#P#p17.ibd
-rw-r-----. 1 mysql mysql  13G Oct 11 05:47 salaries#P#p18.ibd
-rw-r-----. 1 mysql mysql  96M Oct 11 05:45 salaries#P#p19.ibd

How will partitioning help in quickly deleting rows and releasing space?

To archive old data in a partitioned table, we will create an empty table that is identical to the original table in structure but does not have multiple partitions like the original table. Once this table is created we will swap the newly created empty table with one of the partitions of the original table in a matter of seconds.

In this example, we are using a table partitioned on the basis of a date range.

mysql>show create table salaries\G
*************************** 1. row ***************************
       Table: salaries
Create Table: CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `response_code` blob NOT NULL,
  PRIMARY KEY (`emp_no`,`from_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE  COLUMNS(from_date)
(PARTITION p01 VALUES LESS THAN ('1985-12-31') ENGINE = InnoDB,
 PARTITION p02 VALUES LESS THAN ('1986-12-31') ENGINE = InnoDB,
 PARTITION p03 VALUES LESS THAN ('1987-12-31') ENGINE = InnoDB,
 PARTITION p04 VALUES LESS THAN ('1988-12-31') ENGINE = InnoDB,
 PARTITION p05 VALUES LESS THAN ('1989-12-31') ENGINE = InnoDB,
 PARTITION p06 VALUES LESS THAN ('1990-12-31') ENGINE = InnoDB,
 PARTITION p07 VALUES LESS THAN ('1991-12-31') ENGINE = InnoDB,
 PARTITION p08 VALUES LESS THAN ('1992-12-31') ENGINE = InnoDB,
 PARTITION p09 VALUES LESS THAN ('1993-12-31') ENGINE = InnoDB,
 PARTITION p10 VALUES LESS THAN ('1994-12-31') ENGINE = InnoDB,
 PARTITION p11 VALUES LESS THAN ('1995-12-31') ENGINE = InnoDB,
 PARTITION p12 VALUES LESS THAN ('1996-12-31') ENGINE = InnoDB,
 PARTITION p13 VALUES LESS THAN ('1997-12-31') ENGINE = InnoDB,
 PARTITION p14 VALUES LESS THAN ('1998-12-31') ENGINE = InnoDB,
 PARTITION p15 VALUES LESS THAN ('1999-12-31') ENGINE = InnoDB,
 PARTITION p16 VALUES LESS THAN ('2000-12-31') ENGINE = InnoDB,
 PARTITION p17 VALUES LESS THAN ('2001-12-31') ENGINE = InnoDB,
 PARTITION p18 VALUES LESS THAN ('2002-12-31') ENGINE = InnoDB,
 PARTITION p19 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */

1 row in set (0.00 sec)

The size of this table is around 105 GB.

mysql>select table_schema, table_name, table_rows, round(data_length / 1024 / 1024 / 1024 ) DATA_MB, round(index_length / 1024 / 1024 / 1024 ) INDEX_GB, round(data_free / 1024 / 1024 / 1024) FREE_MB, round(data_length / 1024 / 1024 / 1024 )+round(index_length / 1024 / 1024 )+round(data_free / 1024 / 1024 /1024 ) TOTAL_MB from information_schema.tables where table_schema='employees' and table_name='salaries';
+--------------+------------+------------+---------+----------+---------+---------+
| table_schema | table_name | table_rows | DATA_GB | INDEX_GB | FREE_GB | TOTAL_GB|
+--------------+------------+------------+---------+----------+---------+---------+
| employees    | salaries   |    2845404 |     105 |        0 |       0 |     105 |
+--------------+------------+------------+---------+----------+---------+---------+

1 row in set (0.00 sec)

We will create a table that is identical to the partitioned table but we will remove partitioning from this newly created table.

mysql> CREATE TABLE salaries_swap_p3 LIKE salaries;
Query OK, 0 rows affected (0.23 sec)

mysql> ALTER TABLE salaries_swap_p3 REMOVE PARTITIONING;
Query OK, 0 rows affected (0.32 sec)
Records: 0  Duplicates: 0  Warnings: 0

At this moment, this partition of the table has all the data and is occupying the space in the file system.

mysql> SELECT count(*) FROM salaries PARTITION (p03);
+----------+
| count(*) |
+----------+
|    57395 |
+----------+
1 row in set (0.00 sec)


mysql> SELECT count(*) FROM salaries_swap_p3;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.02 sec)

Space reserved by the file system:

[root@ip-172-31-83-227 employees]# ls -lrth *salaries*3*
-rw-r-----. 1 mysql mysql  96K Nov 21 03:54 salaries_swap_p3.ibd
-rw-r-----. 1 mysql mysql 8.5K Nov 21 03:56 salaries_swap_p3.frm
-rw-r-----. 1 mysql mysql  10G Nov 21 03:56 salaries#P#p03.ibd

To exchange partitions we need to execute a query that requires metadata lock on the table and is done almost instantaneously.

mysql> ALTER TABLE salaries EXCHANGE PARTITION p03 WITH TABLE salaries_swap_p3;

Since this operation requires metadata lock and is very fast, it is advised to run this operation in low-traffic periods.

mysql> show processlist;
+----+------+-----------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+
| Id | User | Host      | db        | Command | Time | State                           | Info                                                                   | Rows_sent | Rows_examined |
+----+------+-----------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+
|  4 | root | localhost | employees | Query   |   0  | Waiting for table metadata lock | ALTER TABLE salaries EXCHANGE PARTITION p03 WITH TABLE salaries_swap_p3 |         0 |             0 |
+----+------+-----------+-----------+---------+------+---------------------------------+------------------------------------------------------------------------+-----------+---------------+
1 row in set (0.00 sec)

After the swapping, the new table has all the data of the partition and the partition of the table is empty.

mysql>  SELECT count(*) FROM salaries PARTITION (p03);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql>  SELECT count(*) FROM salaries_swap_p3;
+----------+
| count(*) |
+----------+
|    57395 |
+----------+
1 row in set (0.02 sec)

Space freed by filesystem:

[root@ip-172-31-83-227 employees]# ls -lrth *salaries*3*
-rw-r-----. 1 mysql mysql  10G Nov 21 03:54 salaries_swap_p3.ibd
-rw-r-----. 1 mysql mysql 8.5K Nov 21 03:56 salaries_swap_p3.frm
-rw-r-----. 1 mysql mysql  96K Nov 21 03:56 salaries#P#p03.ibd

Now, you can proceed to drop the swapped table without locking the main table or can use this technique as well to speed up the drop: Speed Up Your Large Table Drops in MySQL.

mysql> drop table if exists salaries_swap_p3; 
Query OK, 0 rows affected (5.23 sec)

Conclusion

What we did here is use the MySQL partitioning-related functions to answer a customer challenge of purging data older than x days, sooner. But, partitioning is not a one size fits all solution, partitioning comes with its own caveats. If there are a lot of secondary indexes on a table and the search queries are not only limited to the partition key, the query performance could deteriorate exponentially. If partitioning the table is not an option, pt-archiver is a great tool that would automate the DELETE statement on your databases with minimal monitoring.

 

Further Reference:

https://dev.mysql.com/doc/refman/5.7/en/partitioning.html

https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html

Want to archive tables? Use Percona Toolkit’s pt-archiver

This blog was originally published in July 2017 and was updated in August 2023.

It’s a pretty common question around here, so let’s see what we can do about that.

So, What is MySQL Partitioning?

Partitioning is a way in which a database (MySQL in this case) splits its actual data down into separate tables but still gets treated as a single table by the SQL layer.

When partitioning in MySQL, it’s a good idea to find a natural partition key. You want to ensure that table lookups go to the correct partition or group of partitions. This means that all SELECT, UPDATE, and DELETE should include that column in the WHERE clause. Otherwise, the storage engine does a scatter-gather and queries ALL partitions in a UNION that is not concurrent.

Generally, you must add the partition key into the primary key along with the auto-increment, i.e., PRIMARY KEY (part_id, id). If you don’t have well-designed and small columns for this composite primary key, it could enlarge all of your secondary indexes.

Want to ensure a successful MySQL 5.7 to 8.0 Upgrade? Watch this on-demand webinar from Percona to learn how!

 

What are the Different Types of MySQL Partitions?

Horizontal and vertical MySQL partitions are techniques used to divide tables into smaller sections for improved performance and management. Horizontal partitioning splits a table into smaller tables with identical columns but distinct rows, while Vertical partitioning divides a table into separate tables with related columns. Various partition types, like RANGE, LIST, HASH, and KEY, are used for specific needs, from range-based data to custom criteria, to ensure efficient data handling and the optimization of queries. Note: Vertical partitioning is not supported in MySQL 8.0 — more on this later.

RANGE partitioning

RANGE partitioning in MySQL is a data partitioning technique where a large table is divided into smaller partitions based on a specified range of column values like dates or numeric intervals. Each partition holds data that falls within a specific range, optimizing data handling and query speed.

HASH partitioning

HASH partitioning in MySQL divides a table into partitions based on the hash value of a designated column’s contents. Unlike range or list partitioning, where you manually designate the partition for specific column values, hash partitioning automatically assigns values to partitions based on hashing. This method distributes data evenly across partitions to achieve balanced storage and optimal query performance.

LIST partitioning

LIST partitioning in MySQL shares similarities with range partitioning. As with range partitioning, each partition is explicitly defined, but in list partitioning, partitions are created and assigned based on including a column value in predefined value lists rather than the contiguous ranges of values used in range partitioning.

COLUMNS partitioning

COLUMNS partitioning in MySQL is a technique that involves dividing a table into partitions based on specific columns’ values. Unlike other partitioning methods focusing on the entire row, column partitioning separates columns into different partitions. This approach is helpful when working with tables with many columns or when specific columns are frequently updated.

KEY partitioning

KEY partitioning is similar to HASH partitioning, except that only one or more columns to be evaluated are specified, and the MySQL server provides its own hashing function. These columns can contain other than integer values since the hashing function supplied by MySQL guarantees an integer result regardless of the column data type.

MySQL Partitioning in Version 5.7

MySQL version 5.7 introduced various features and capabilities for partitioning, enhancing its utility in managing large datasets. It enabled dividing large tables into smaller, manageable segments based on defined criteria. This facilitates improved data organization, query optimization, and maintenance.

In version 5.7, MySQL partitioning supports multiple partitioning types, including RANGE, LIST, HASH, KEY, and COLUMNS. Each type caters to different data distribution needs. 

Using partitioning in a MySQL 5.7 environment offers several practical benefits. It significantly improves query performance by reducing the amount of data scanned during queries, which is especially helpful when dealing with large tables. Partition pruning, a feature in MySQL 5.7, ensures that only relevant partitions are accessed, further enhancing query efficiency. Additionally, partitioning aids in maintenance tasks like archiving and purging old data, as operations can be performed on individual partitions instead of the entire table.

Are you ready for MySQL 5.7 EOL? Percona can help. 

Learn More

 

MySQL Partitioning in MySQL 8.0

MySQL 8.0 brought substantial advancements and enhancements to partitioning, significantly elevating its capabilities. This version introduces key features and optimizations that address limitations from previous iterations.

One major enhancement is the support for subpartitioning. MySQL 8.0 allows you to create subpartitions within existing partitions, providing an additional level of data segmentation. This feature facilitates even more precise data organization and management, allowing for complex use cases involving multi-level data hierarchies.

Additionally, MySQL 8.0 introduces automated list partitioning, simplifying partition creation through by enabling the database to determine the partition based on the values inserted automatically.

This version also notably integrates native backing for range and list partitioning of spatial indexes, amplifying geospatial query speed for substantial datasets. Enhancements to the query optimizer improve partition pruning for both single-level and subpartitioned tables, leading to improved query performance.

To sum it up, MySQL 8.0 significantly advances partitioning with features like subpartitioning, automatic list partitioning, and improved query optimization. These enhancements address limitations from previous versions, allowing for more complex data organization, streamlined management, and optimized query performance.

Upgrading MySQL to 8.0? Check out this blog to learn how to avoid disaster!

What are the Benefits of MySQL Partitions?

MySQL partitioning offers several advantages in terms of query performance and maintenance:

Enhanced Query Performance: Partitioning improves query performance by minimizing the amount of data scanned during queries. As the data is distributed into smaller partitions, the database engine only needs to scan relevant partitions, leading to faster query responses.

Optimized Resource Utilization: Partitioning enables parallelism in query execution across partitions. This means that multiple partitions can be processed simultaneously, making better use of available hardware resources and further enhancing query performance.

Data Retention and Deletion: Partitioning simplifies the archiving or deleting of old data by targeting specific partitions, and enhancing data retention policies.

Reduced Overhead: Partitioning can significantly reduce the overhead of managing large tables. For example, when inserting or deleting data, the database engine only needs to modify the relevant partitions, which can be much faster than performing these operations on the entire table.

Streamlined Maintenance: Partitioning simplifies maintenance operations. For example, you can perform maintenance tasks like index rebuilds, statistics updates, or data archiving on specific partitions rather than the entire table, minimizing downtime and optimizing resource utilization.

Data Lifecycle Management: Partitioning supports efficient data lifecycle management. Old or infrequently accessed data can be stored in separate partitions or even archived, allowing for better control over data retention and optimization of storage resources.

Enhanced Scalability: Partitioning enhances the database’s ability to scale, as data can be distributed across different storage devices.

In summary, MySQL partitioning brings substantial advantages to both query performance and maintenance. It improves data retrieval speed, enhances resource utilization, streamlines maintenance operations, optimizes storage management, and reduces overheads associated with large tables. These benefits collectively contribute to a more efficient database environment.

What are the Challenges and Limitations of MySQL Partitions?

While there are lots of positives about using MySQL partitioning, there can also be challenges and limitations that users should be aware of:

Query Optimization Complexity: Although partitioning can enhance query performance, it requires queries to be designed with partitioning key considerations in mind. Inappropriately designed queries may not fully utilize partitioning benefits, leading to poor performance.

Limited Key Choices: Not all columns are suitable for partitioning keys. Choosing a proper partitioning key is crucial, and inappropriate selections can result in uneven data distribution across partitions, impacting performance.

Suboptimal Partitioning Strategies: Choosing the wrong partitioning strategy or key can lead to performance degradation. For instance, using partitioning on a table with a small number of rows may not provide significant benefits and can even worsen performance due to increased complexity.

Limited Parallelism: While partitioning allows for parallel processing, there might be limitations on how many partitions can be processed concurrently based on hardware resources, potentially impacting query performance.

Data Skewing: In some scenarios, data might not be uniformly distributed across partitions, causing “data skew.” This can lead to uneven resource utilization and slower performance for certain partitions.

Replication and Backup Issues: MySQL partitioning might impact the way data replication and backups are performed. Special considerations are needed to ensure these processes still work seamlessly after partitioning.

So, while MySQL partitioning does offer advantages, it also brings challenges and limitations related to complexity, maintenance, query optimization, and performance. Careful planning and continuous monitoring are crucial to facing these challenges and achieving optimal performance.

Performance Optimization with MySQL Partitioning

MySQL partitioning enhances query performance by enabling the database to focus on relevant data partitions during queries. This reduces the amount of data that needs to be scanned, resulting in faster data retrieval. For example, when querying a large table for specific date ranges, partitioning allows the engine to scan only relevant partitions containing data within those ranges.

Query execution plans are positively impacted by partitioning. The query optimizer recognizes partitioning schemes and generates execution plans that use partition pruning. This means the optimizer can skip unnecessary partitions, resulting in optimized query plans that use fewer resources and execute more quickly.

Partitioning influences indexing strategies by narrowing the scope of indexing. Instead of indexing the entire table, partitioning allows for more focused indexing. This minimizes index size and boosts efficiency, leading to faster query performance.

In scenarios where partitioning aligns with natural data distribution, such as time-series data or geographical regions, query execution time is significantly reduced. Queries that involve specific partitions can bypass irrelevant data; for instance, when searching for transactions within a certain date range, partitioning enables the database to search only the relevant partition.

Best Practices for Implementing MySQL Partitioning

With these best practices, you can ensure that your MySQL partitioning setup is efficient, well-maintained, and improves database performance.

Choose the Correct Partition Key: Select a partition key that aligns with your data distribution and query patterns. Common choices include time-based or range-based values.

Monitor Query Performance: Continuously monitor query performance after partitioning. Use tools like EXPLAIN to assess query execution plans.

Watch for Bloat: Over time, partitions can accumulate large amounts of data, leading to slow queries.

Proper Indexing: Partitioned tables benefit from proper indexing. Ensure that the chosen partition key is part of the primary or unique key. Additionally, consider indexing frequently queried columns to improve performance further.

Regular Maintenance: Perform routine maintenance tasks, such as purging old data from partitions, optimizing indexes, and rebuilding partitions.

Backup and Restore: As we mentioned earlier, partitioning can impact backup and restore strategies. Ensure your backup and restore procedures account for partitioned data to prevent data loss and ensure reliable recovery.

Test, Test, and Test Again: Before implementing partitioning in production, thoroughly test it in a controlled environment. This helps identify potential issues and fine-tune the partitioning strategy.

Documentation: Always be documenting! Be sure to include your partitioning strategy, why certain partition keys are used, and your maintenance procedures.

Talk to experts: If you’re new to partitioning or dealing with complex scenarios, consider consulting with experts.

Choosing the Right Partitioning Strategy

Selecting the appropriate partitioning strategy in MySQL involves carefully considering various factors, including:

Understanding your data’s nature and distribution. For range-based data, consider range partitioning, while list partitioning is suitable for discrete values. Hash partitioning evenly distributes data.

Analyzing query patterns to align the strategy with how data is accessed. Time-based queries benefit from range partitioning, while hash partitioning suits equally accessed values.

Matching the strategy to your database requirements. For archiving historical data, consider range-based on time. High-write workloads might favor hash or key partitioning for load balancing.

Watching for changes in data patterns. As data grows, a previously effective strategy might become less optimal. Periodically review and adjust.

Any partitioning strategy should improve query performance, not lead to suboptimal queries — test and benchmark strategies before implementation.

Ensuring the strategy aligns with maintenance operations. For example, rebuilding large partitions might often impact uptime. Select a strategy that minimizes disruptions.

Continuously monitoring query performance after implementation. Be ready to adjust your strategy as needed.

Evaluating how your chosen strategy accommodates future growth, as some strategies scale better with larger datasets.

Choosing the right partitioning strategy is pivotal to database performance. By aligning the strategy with your data’s characteristics and specific requirements, you ensure that your MySQL partitioning delivers the desired results.

Elevate your MySQL database management: Get started with Percona Distribution for MySQL.

Migrating to MySQL 8.0 and 5.7 EOL Support with Percona

Proper MySQL partitioning optimizes databases by splitting large tables into smaller parts, enhancing query speed and data management while reducing overhead and making maintenance easier. But, users need to understand that careful planning, monitoring, and testing are vital to avoid any potential performance declines due to improper setup.

Looking to upgrade to MySQL 8.0 or stay on 5.7? Percona will support you either way.

 

Move to MySQL 8.0  Get Post-EOL Support For MySQL 5.7

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#p201203.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.

mysql> UNLOCK TABLES;

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> CREATE TABLE dest_tbl_tmp LIKE archive_tbl;
mysql> ALTER TABLE dest_tbl_tmp REMOVE PARTITIONING;
mysql> ALTER TABLE dest_tbl_tmp DISCARD TABLESPACE;

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.

shell> cp /tmp/dest_tbl_tmp.ibd /var/lib/mysql/thedb/
shell> chmod 660 /var/lib/mysql/thedb/dest_tbl_tmp.ibd
shell> chown mysql.mysql /var/lib/mysql/thedb/dest_tbl_tmp.ibd
mysql> ALTER TABLE dest_tbl_tmp IMPORT 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 p201203 VALUES LESS THAN ('2012-04-01'),
PARTITION future VALUES LESS THAN (MAXVALUE)
);
mysql> ALTER TABLE archive_tbl EXCHANGE PARTITION p201203 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 p201203;

For more information on why these steps are needed, please check the following documentation link for ALTER TABLE … EXCHANGE PARTITION:
https://dev.mysql.com/doc/refman/5.6/en/partitioning-management-exchange.html

In MySQL version 5.7, it is possible to exchange partitions without the unpartitioned table step, as described in the following link:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-management-exchange.html

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 table
  https://bugs.mysql.com/bug.php?id=75706