Create MySQL table partition by month

create_partition.sh

#!/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  

DML_partition.sql

-- 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
);

partition_template.txt

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
);