CODEWITHSUNDEEP
mysqldatabase-partitioningdata-partitioning
usert4jju7
usert4jju7

Reputation: 1813

MySQL - Move data between partitions aka re-partition

I have a mysql table whose partitions look as below

p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data)
p2015h2 - Contains data where date < 2016-01-01
p2016h1 - Contains data where date < 2016-07-01
p2016h2 - Contains data where date < 2017-01-01

I'd like the new partitions to be quarterly based as below - 

p0 - Contains data where date < 2015-10-01
p1 - Contains data where date < 2016-01-01
p2 - Contains data where date < 2016-04-01
p3 - Contains data where date < 2016-07-01

I started by reorganizing the first partition & executed the below command. All went well.

alter table `table1` reorganize partition `p2015half1` into (partition `p0` values less than ('2015-10-01'));

Now as the existing partition p2015h2 has data that includes data upto 2015-10-01, how could I move this part into the partition p0 ? I would need to do the same thing with the other partitions too as I continue building the new ones.

I did try to remove partitioning on the table fully, but, the table is billions of rows in size & hence the operation will take days. Post this I will have to rebuild the partitions which will take days again. Hence, I decided to take the approach of splitting partitions.

I'm stuck at this point in time. I'd fully appreciate any guidance here please.

Upvotes: 3

Views: 1491

Answers (1)

usert4jju7
usert4jju7

Reputation: 1813

mysql> alter table `table1` reorganize partition p0,p2015half2 into (partition p00 values less than ('2015-07-01'), partition p1 values less than ('2016-01-01'));

mysql> alter table `table1` reorganize partition p00 into (partition p0 values less than ('2015-07-01'));

mysql> alter table `table1` reorganize partition p2016half1,p2016half2 into (partition p2 values less than ('2016-04-01'), partition p3 values less than ('2016-07-01'),partition p4 values less than maxvalue);

Upvotes: 1

Related Questions