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.

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.

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.

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.