Linux Scripts to Create Table Partition for Next Month Automatically

 In this article we are going to show you a method of create new monthly partition for MySQL table without stored procedure, with the REORGANIZE PARTITION from MySQL/MariaDB.

(Click here to skip the article and straight to the scripts.)

For demo purpose, below are the DML of the table to be partitioned, and the ALTER statement to start with 2 partitions: "p_old" and "p_future". Please change the p_old partition date to the first of next month.

	-- a table to be partition by column 'created_at'
	-- make sure all primary key and unique key of table contains column used for partition
	CREATE TABLE `table_to_be_partitioned` (
	`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
	`col_a` VARCHAR(255) DEFAULT NULL,
	`cal_b` VARCHAR(255) DEFAULT NULL,
	`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP(),
	`updated_at` DATETIME DEFAULT NULL,
	PRIMARY KEY (`created_at`,`id`),
	KEY `id` (`id`)
	) ENGINE=INNODB DEFAULT CHARSET=latin1;
	-- make current month and older date as 'p_old'
	-- and date after starting of next month as 'p_future'
	ALTER TABLE table_to_be_partitioned
	PARTITION BY RANGE (TO_DAYS(`created_at`))
	(
	PARTITION p_old VALUES LESS THAN (TO_DAYS('2021-09-01')),
	PARTITION p_future VALUES LESS THAN MAXVALUE
	);

view raw DML_partition.sql hosted with ❤ by GitHub

Below is the template of ALTER statement to REORGANIZE PARTITION "p_future" into a partition of next month ('p_202109' for example, as of writing), and 'p_future' for records after next month.

	ALTER TABLE table_to_be_partitioned
	REORGANIZE PARTITION p_future INTO (
	PARTITION p_partition_name VALUES LESS THAN (TO_DAYS('partition_next_start')),
	PARTITION p_future VALUES LESS THAN MAXVALUE
	);

view raw partition_template.txt hosted with ❤ by GitHub

And here is the shell script that did some simple string substitute based on the template, and execute it on your MySQL/MariaDB server. Please do not forget to change the username, password and database name to match you database server.

	#!/bin/bash
	partition_name=$(date -d "$(date +%Y-%m-01) +1 month" +%Y%m)
	next_start=$(date -d "$(date +%Y-%m-01) +2 month" +%Y-%m-%d)
	sed -e "s/partition_name/${partition_name}/g" -e "s/partition_next_start/${next_start}/g" partition_template.txt | mysql -u yourDbUsername -p yourDbPassword database_name

view raw create_partition.sh hosted with ❤ by GitHub

Finally, you may run this each month to create a new partition for next month with command:

./create_partition.sh

If you are to schedule it via crontab, ensure you modify the script to use full path for the template file.

Thank you.