A simple way for adding future partitions or purging old partitions is through mysql procedures. Later we can use any scheduler or a cronjob to call this procedure daily.
Here I will provide a sample procedure for maintainance of a daily partitioned table based on a datetime column. Please do look at my previous post on mysql range partitioning in the following post. I have taken the example from this post and modified it based on our requirement.
First we will create a sample table along with a partition for our testing purposes.
mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
mysql> alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16092015 values less than ( to_seconds('2015-09-17 00:00:00') ));
Now lets create a table for holding list of tables and number of partitions to preserve/drop.
mysql> create table test.part_tables(dbname varchar(50),tabname varchar(50),numpart int,nextpart int);
Lets go ahead and insert a sample row there :
mysql> insert into test.part_tables values('test','test_partitions',30,7);
So, we will be preserving only last 30 days partitions and make sure we have next 7 days partitions for ‘test_partitions’ table.
Mysql Procedure 1 (part_maint) : This procedure takes in table list from our meta-table i.e part_tables and runs partition maintainance for each table :
Read More