Using MySQL events in practice

For those who actively use MySQL, it is not a secret that since version 5.1, MySQL supports events (events). If you need to perform queries or separate procedures on a schedule, and there was no laziness to switch from the console launch to the built-in MySQL functionality, welcome under cat.

To begin with, let us remind ourselves what events in MySQL are and how to prepare them?
First of all, events provide cross-platform, since they do not require any external applications. Inside the event, you can run SQL commands or simply call pre-written procedures.
In our case, we will be engaged in the archiving of a rapidly swelling table, in which user requests are logged.

Turn on the scheduler

The event variable is handled by the global variable event_scheduler . For MySQL later than 5.1.11, it can take one of 3 values:
OFF (can also be 0 ): Scheduler stopped. The scheduler thread is not executed and is not shown in the output of SHOW PROCESSLIST. No scheduled events are executed. OFF is the default for event_scheduler.
ON (can also be 1 ): Scheduler is working. The scheduler thread runs itself and executes all scheduled events. The event scheduler thread is listed in the output of SHOW PROCESSLIST as a background process.
DISABLED : value makes the scheduler inactive. The scheduler thread is not executed and is not displayed in the output of SHOW PROCESSLIST.
')
We are interested in the enabled state, so you need to register in the config

event_scheduler=1

or execute the command

 SET GLOBAL event_scheduler=ON; 

Create event

I liked just calling the procedure inside the event. It is also possible because it was already created and started by the application itself;) So, let's create a procedure:

 CREATE DEFINER = 'root'@'localhost' PROCEDURE `new_proc`() NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE tbl_tmp,tbl_logarch VARCHAR(50); -- tbl_log     --       tbl_log_<>_<> SET tbl_logarch=DATE_FORMAT(CURRENT_TIMESTAMP, '%Y%m%d_%H%i'); SET tbl_tmp=CONCAT("tbl_log_", tbl_logarch); --  SQL     ; SET @archive_query:=CONCAT("CREATE TABLE ", tbl_tmp, " ENGINE=ARCHIVE AS (SELECT * FROM tbl_log)"); --    PREPARE archive_query FROM @archive_query; EXECUTE archive_query; DEALLOCATE PREPARE archive_query; --     ,       DELETE FROM tbl_log; END; 

Now run the procedure and see if it works.

 call new_proc(); 

If everything is in order, then continue. Create an event directly. The simplified syntax is:
CREATE EVENT event_name ON SCHEDULE AT {DATE AND TIME} DO {SQL COMMAND};

or
CREATE EVENT event_name ON SCHEDULE EVERY {X} {SECOND|MINUTE|HOUR|DAY|MONTH|YEAR|WEEK} DO {SQL COMMAND};


I needed to archive once a week, so my DDL looks like this:

 CREATE EVENT `new_event` ON SCHEDULE EVERY 1 WEEK STARTS CURRENT_TIMESTAMP ON COMPLETION NOT PRESERVE ENABLE COMMENT '' DO call new_proc(); 

Observe the result

Having played around with time, I made sure that the event created does exactly what I expected. One more step to automation is made. As a bonus, studied several additional materials on the topic.

PS Just in case, pay attention that I tried to present only the practical part. Therefore, I deliberately omitted the description of the extended syntax, user privileges, restrictions, depending on the versions of MySQL. If you, dear habrachelovek, failed to create and use the event according to the above instructions, you can always write about it in habrakoment (I will try to answer) or find a solution on other useful resources.

In the process of writing materials used:
dev.mysql.com/doc/refman/5.1/en/events.html
www.rldp.ru/mysql/mysqlpro/events.htm

UPD: They say that it is possible to organize interaction between the event scheduler and the operating system using federated table and MySQL Proxy. I have not tried it myself. Who in the subject, write pliz komenty.