Mysql syslog partitioning events

First enable event handling within the mysql server itself by creating an event.cnf in /etc/mysql/conf.d/ folder:

[mysqld]
event_scheduler=ON

and restart the mysql server. You should now see the new scheduler process:

show processlist\G
...
     Id: 1
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 7
  State: Waiting on empty queue
   Info: NULL

Now create one event for daily creating the new partition:

DELIMITER |
CREATE EVENT `logs_add_partition` ON SCHEDULE EVERY 1 DAY STARTS '2010-06-19 00:00:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DECLARE new_partition CHAR(32) DEFAULT CONCAT('p', DATE_FORMAT(DATE_ADD(CURDATE(), INTERVAL 1 DAY), '%Y%m%d'));
    DECLARE max_day INTEGER DEFAULT TO_DAYS(NOW()) +1;
    SET @s = CONCAT('ALTER TABLE logger.logs ADD PARTITION (PARTITION ', new_partition, ' VALUES LESS THAN (', max_day, '))');
    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END |
DELIMITER ;

And one event for deleting the old ones.

DELIMITER |
CREATE EVENT `logs_del_partition` ON SCHEDULE EVERY 1 DAY STARTS '2010-06-19 00:00:02' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
    DECLARE old_partitions CHAR(64) DEFAULT '';
    SELECT CONCAT( 'ALTER TABLE logger.logs DROP PARTITION ', GROUP_CONCAT( PARTITION_NAME )) INTO @s
    FROM information_schema.PARTITIONS
    WHERE   TABLE_SCHEMA=schema() AND
    TABLE_NAME='logs' AND
    STR_TO_DATE(SUBSTRING(PARTITION_NAME, 2), '%Y%c%d') < DATE_SUB( CURDATE(), INTERVAL 14 DAY )
    GROUP BY TABLE_NAME;

    PREPARE stmt FROM @s;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    END |
DELIMITER ;

If everything went OK you should see those two lines within the mysql.event table.

Those events only work if the table is already partinioned, so you have to create an initial partition by hand:

SELECT TO_DAYS(NOW())+1
ALTER TABLE logger.logs PARTITION BY RANGE (TO_DAYS(datetime)) (PARTITION p20141119 VALUES LESS THAN (735923))

The first line gives you the number 735923 for the date 2014-11-19. Change those two values to your current yesterdays date.