Rotate log tables with a MySQL EVENT

Unix crontab can be used for database tasks that need to be run on a schedule, but since MySQL version 5.1.6 there is a better way to handle periodic database tasks - Events. 

http://dev.mysql.com/doc/refman/5.1/en/events.html

Events in MySQL are like a stored procedure that can run automatically at a set time. The two main advantages over using a crontab for scheduled jobs are:

1. No need to put the database password in the crontab file for the connection.
2. When you move your database, the scheduled EVENTS go with it.

Rotate MySQL log tables

Be sure to add global event_scheduler=1 to your my.ini config file so events will be enabled at database startup.

set global event_scheduler=1;

DROP EVENT IF EXISTS log_clean_daily;

CREATE EVENT log_clean_daily
ON SCHEDULE EVERY 1 day
DO CALL rotate_logs;

The rotate_logs stored procedure:

The two versions I'll show here, are for a regular MySQL server and for an RDS MySQL server.

The regular MySQL procedure for rotate_logs

DROP PROCEDURE IF EXISTS rotate_logs;
DELIMITER |
CREATE PROCEDURE rotate_logs()
BEGIN
drop table if exists general_log_tmp;
drop table if exists general_log_old;
CREATE TABLE general_log_tmp LIKE general_log;
RENAME TABLE general_log TO general_log_old, general_log_tmp TO general_log;
drop table if exists slow_log_tmp;
drop table if exists slow_log_old;
CREATE TABLE slow_log_tmp LIKE slow_log;
RENAME TABLE slow_log TO slow_log_old, slow_log_tmp TO slow_log;
END
|
DELIMITER ;

The Amazon RDS MySQL procedure for rotate_logs 

DROP PROCEDURE IF EXISTS rotate_logs;

DELIMITER |
CREATE PROCEDURE rotate_logs()
BEGIN
  call mysql.rds_rotate_general_log;
  call mysql.rds_rotate_slow_log;
END
|
DELIMITER ;

Make sure to enable the event_scheduler for your parameter group since RDS does not allow setting the event_scheduler global variable directly.

rds-modify-db-parameter-group mygroup --parameters "name=event_scheduler, value=ON, method=immediate"