Creating the Test Database

CREATE DATABASE partition_demo CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Creating the Table

Note: The column used for partitioning must be part of the primary key.

USE partition_demo;

CREATE TABLE event_logs (
    id BIGINT(20) NOT NULL AUTO_INCREMENT,
    event_time DATETIME NOT NULL,
    message VARCHAR(200),
    PRIMARY KEY (id, event_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Partitioned event logs';

Inserting Test Data

INSERT INTO event_logs (event_time, message) VALUES
    ('2021-02-01 13:34:23', '20210201133423'),
    ('2021-02-02 14:23:13', '20210202142313');

Manual Partitioning

Manual partitions must exist before automatic partition management can begin.

The partitioning column must be part of the primary key; it cannot be a standalone index.
You can adjust the primary key with the following command if needed:
ALTER TABLE event_logs DROP PRIMARY KEY, ADD PRIMARY KEY (id, event_time);

Batch Partition Creation

ALTER TABLE event_logs PARTITION BY RANGE COLUMNS(event_time) (
    PARTITION p20210201 VALUES LESS THAN ('2021-02-02'),
    PARTITION p20210202 VALUES LESS THAN ('2021-02-03'),
    PARTITION p20210203 VALUES LESS THAN ('2021-02-04')
);

Single Partition Creation

ALTER TABLE event_logs ADD PARTITION (PARTITION p20210201 VALUES LESS THAN ('2021-02-02'));

Dropping a Partition

Dropping a partition also removes all data stored in that partition.

ALTER TABLE event_logs DROP PARTITION p20210201;

Viewing Table Partitions

SELECT PARTITION_NAME, PARTITION_DESCRIPTION AS val FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'event_logs' AND TABLE_SCHEMA = 'partition_demo';

Creating the Stored Procedure for Partition Maintenance

DELIMITER $$
DROP PROCEDURE IF EXISTS maintain_partitions
$$
CREATE PROCEDURE maintain_partitions()
BEGIN
  DECLARE cur_date DATE;
  DECLARE min_partition_date DATE;
  DECLARE max_partition_date DATE;
  DECLARE partition_suffix VARCHAR(20);
  DECLARE partition_boundary VARCHAR(20);

  -- Add new partitions up to three days in the future
  SELECT MAX(CAST(REPLACE(PARTITION_DESCRIPTION, '''', '') AS DATE)) AS val
  INTO max_partition_date
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'event_logs' AND TABLE_SCHEMA = 'partition_demo';

  SET cur_date = CURDATE();

  WHILE max_partition_date <= (cur_date + INTERVAL 3 DAY) DO
    SET partition_suffix = DATE_FORMAT(max_partition_date, '%Y%m%d');
    SET partition_boundary = DATE_FORMAT(max_partition_date + INTERVAL 1 DAY, '%Y-%m-%d');
    SET @sql = CONCAT('ALTER TABLE event_logs ADD PARTITION (PARTITION p', partition_suffix, ' VALUES LESS THAN(''', partition_boundary, '''))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET max_partition_date = max_partition_date + INTERVAL 1 DAY;
  END WHILE;

  -- Drop partitions older than one year
  SELECT MIN(CAST(REPLACE(PARTITION_DESCRIPTION, '''', '') AS DATE)) AS val
  INTO min_partition_date
  FROM INFORMATION_SCHEMA.PARTITIONS
  WHERE TABLE_NAME = 'event_logs' AND TABLE_SCHEMA = 'partition_demo';

  WHILE min_partition_date <= (cur_date - INTERVAL 1 YEAR) DO
    SET partition_suffix = DATE_FORMAT(min_partition_date - INTERVAL 1 DAY, '%Y%m%d');
    SET @sql = CONCAT('ALTER TABLE event_logs DROP PARTITION p', partition_suffix);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    SET min_partition_date = min_partition_date + INTERVAL 1 DAY;
  END WHILE;
END$$
DELIMITER ;

Manual Execution of the Procedure

CALL maintain_partitions();

Enabling the Event Scheduler

MySQL has the event scheduler disabled by default.

Check the current status:

SHOW VARIABLES LIKE '%event_scheduler%';
-- or
SELECT @@event_scheduler;

Temporarily enable it (resets after restart):

SET GLOBAL event_scheduler = 1;

To enable permanently, add the following line to the my.cnf configuration file:

event_scheduler = ON;

Creating the Scheduled Event

DELIMITER $$
DROP EVENT IF EXISTS auto_partition_event $$
CREATE EVENT auto_partition_event
ON SCHEDULE
EVERY 1 DAY
STARTS '2021-02-01 13:19:02'
DO
BEGIN
    CALL maintain_partitions();
END$$
DELIMITER ;