easterism Jul 4 2011 at 22:32

Использование событий MySQL на практике

3 min

65K

Для тех, кто активно пользуется MySQL, не секрет, что начиная с версии 5.1, MySQL поддерживает события (events). Если вам нужно выполнять запросы или отдельные процедуры по расписанию, а перейти с запуска консоли на встроенный функционал MySQL ~~было лень~~ не было времени, добро пожаловать под кат.

Для начала напомним самим себе, что же такое события в MySQL и как их готовить?
Прежде всего события обеспечивают кросплатформенность, так как не требуют никаких внешних приложений. Внутри события можно запускать SQL команды или просто вызывать заранее написанные процедуры.
В нашем случае будем заниматься архивированием быстро разбухающей таблицы, в которой логируются запросы пользователей.

Включаем планировщик

За работу планировщика событий отвечает глобальная переменная event_scheduler. Для MySQL позже 5.1.11 она может принимать одно из 3-х значений:
OFF (может быть также 0): Планировщик остановлен. Поток планировщика не выполняется и не показывается в выводе SHOW PROCESSLIST. Никакие планируемые события не выполняются. OFF является значением по умолчанию для event_scheduler.
ON (может быть также 1): Планировщик работает. Поток планировщика выполняется сам и выполняет все планируемые события. Поток планировщика событий перечислен в выводе SHOW PROCESSLIST как фоновый процесс
DISABLED: значение делает планировщик неактивным. Поток планировщика не выполняется и не отображается в выводе SHOW PROCESSLIST.

Нас интересует включенное состояние, поэтому нужно прописать в конфиге

event_scheduler=1

или выполнить команду

SET GLOBAL event_scheduler=ON;

Создаем событие

Мне больше понравилось просто вызывать процедуру внутри события. Возможно еще и потому, что она уже была создана и запускалась самим приложением ;) Итак, создадим процедуру:


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;

Теперь запустим процедуру и посмотрим, работает ли она

call new_proc();

Если все в порядке, тогда продолжим. Создадим непосредственно событие. Упрощенный синтаксис выглядит так:

CREATE EVENT event_name ON SCHEDULE AT {DATE AND TIME} DO {SQL COMMAND};

или

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

Мне нужно было выполнять архивирование 1 раз в неделю, поэтому мой DDL выглядит так:


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

Наблюдаем результат

Поигравшись со временем, я убедился, что созданное событие делает именно то, что я ожидал. Еще один шаг к автоматизации сделан. В качестве бонуса, изучено несколько дополнительных материалов по теме.

P.S. На всякий случай обращаю внимание, что я старался изложить лишь практическую часть. Поэтому, я сознательно опустил описание расширенного синтаксиса, пользовательских привилегий, ограничений в зависимости от версий MySQL. Если у тебя, дорогой хабрачеловек, не получилось создать и использовать событие по вышеописанной инструкции, ты всегда можешь написать об этом в хабракоментах (я постараюсь ответить) или найти решение на других полезных ресурсах.

В процессе написания использовались материалы:
dev.mysql.com/doc/refman/5.1/en/events.html
www.rldp.ru/mysql/mysqlpro/events.htm

UPD: Говорят, что можно организовать взаимодействие планировщика событий и операционной системы, используя federated table и MySQL Proxy. Сам не пробовал. Кто в теме, напишите плиз в коменты.

Tags:

Hubs:

MySQL