12 июня 2009 г. cron MySQL

MySQL Event Scheduler или встроенный диспетчер событий в MySQL

mysql-crontab

В MySQL 5.1 появился свой планировщик задач! Ранее, мне и многим другим разработчикам приходилось использовать cron для таких целей, как "подчистка устаревшей информации" или "пересчет счетчиков" в базе данных и т.д.

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

Планировщик по умолчанию выключен, для того чтобы его включить надо добавить в my.ini или в my.cnf

[mysqld]
event_scheduler = on

Так же вы можете для конкретной сессии запустить планировщик в mysql-консоле

mysql> set global event_scheduler = ON;

Для проверки, работает ли планировщик, выполните запрос:

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set (0.00 sec)

Официальное руководство по настройке.

Создание задания

Синтаксис достаточно прост, при создании задания необходимо обязательно указать:

  • Имя задания (event_name)
  • Интервал или точное время (schedule и interval)
  • SQL-запрос для выполнения (sql_statement)

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO sql_statement;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

Так же вы можете указать:

  • DEFINER - имя пользователя, от кого запускать задание (с его правами соответственно)
  • COMMENT - комментарий
  • ON COMPLETION PRESERVE - задание после выполнения сохраняется
  • ON COMPLETION NOT PRESERVE - задание выполняется один раз, после чего уничтожается из списка заданий (по умолчанию)

Пример короткой записи:

CREATE EVENT `myevent`
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
      ON COMPLETION PRESERVE
    DO
      UPDATE `myschema`.`mytable` SET `mycol` = `mycol` + 1;

Официальное руководство по CREATE EVENT

Изменение задания

При изменении, вы можете сменить владельца, переименовать название, изменить SQL-запрос и поменять другие свойства задания.

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO sql_statement]

Для отображения всех свойств задания используйте:

SELECT * FROM `INFORMATION_SCHEMA`.`EVENTS`
    WHERE `EVENT_NAME` = 'myevent' AND EVENT_SCHEMA = 'myschema'\G
*************************** 1. row ***************************
   EVENT_CATALOG: NULL
    EVENT_SCHEMA: myschema
      EVENT_NAME: myevent
         DEFINER: john@smith
      EVENT_BODY: SQL
EVENT_DEFINITION: INSERT INTO myschema.mytable VALUES (UNIX_TIMESTAMP())
      EVENT_TYPE: RECURRING
      EXECUTE_AT: NULL
  INTERVAL_VALUE: 5
  INTERVAL_FIELD: SECOND
        SQL_MODE: NULL
          STARTS: 0000-00-00 00:00:00
            ENDS: 0000-00-00 00:00:00
          STATUS: ENABLED
   ON_COMPLETION: NOT PRESERVE
         CREATED: 2006-02-09 22:36:06
    LAST_ALTERED: 2006-02-09 22:36:06
   LAST_EXECUTED: NULL
   EVENT_COMMENT:
1 row in set (0.00 sec)

Либо используйте:

show events like "event_name%"\G

Официальное руководство по ALTER EVENT

Удаление задания

DROP EVENT [IF EXISTS] event_name

Официальное руководство по DROP EVENT

Диспетчер событий и привилегии в MySQL

Официальное руководство по установке привелегий

Примеры использования

Задача: Удаление не активированных учетных записей пользователей, которые не активировались в течении 3-х дней. Запускать событие необходимо каждый день.

CREATE EVENT `not_active_users`
    ON SCHEDULE
      EVERY 1 DAY
    DO
      DELETE FROM `users`
        WHERE `active` = 'not', UNIX_TIMESTAMP(`date`) >= UNIX_TIMESTAMP(NOW()) - (3 * 24 * 3600);

Задача: А теперь сменим для задания "not_active_users" временной интервал.

ALTER EVENT `not_active_users`
    ON SCHEDULE
      EVERY 12 HOUR;

Задача: Удалить задание.

DROP EVENT `not_active_users`;

Официальные страницы руководства: Using the Event Scheduler

Комментарии

Для событий исполняемых с определенной периодичностью, можно также указать время старта и время прекращения цикличного выполнения, например для примера приведенного в статье:

CREATE EVENT not_active_users
ON SCHEDULE
EVERY 1 DAY START '2009-06-12 23:59:59' END CURRENT_TIMESTAMP + INTERVAL 1 WEEK
DO
DELETE FROM users
WHERE active = 'not', UNIX_TIMESTAMP(date) >= UNIX_TIMESTAMP(NOW()) - (3 * 24 * 3600);

Событие исполнится -2009-06-12 23:59:59 и не будет исполнятся через 1 неделю, подробнее смотрите http://dev.mysql.com/doc/refman/5.1/en/create-event.html

В популярном менеджере EMS можно работать с событиями, для версий совместимых с соответствующей версией MySQL.

Спасибо Константин, за очень ценный комментарий!

Эвенты - классная штука, ...на ранних версиях mysql правда был баг с временем срабатывания, но MySQL AB уже пофиксил. А вот ещё нашел отличный редактор для mysql - Devart dbForge studio for MySQL, там для событий ещё более удобный редактор с настройками объектов.
Devart dbForge studio for MySQL, русская бесплатная

alexeym, да, я эту софтину юзал :)

выполняю в консоли следующий код

CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COMPLETION PRESERVE DO UPDATE lafs.users SET counter = counter + 1;

пишет ошибку

You have an error at your SQL syntax near 'CREATE EVENT myevent ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE ON COM'

должен ли я в консоли заранее выполнить запрос для выбора базы данных

должен ли я в консоли заранее выполнить запрос для выбора базы данных
это можно не читать)

Какая версия mysql?

Да, выбери БД и еще покажи

show variables like 'event_scheduler';

Vatiable_name Value
event_scheduler ON
1 row in set

мистика емае

короче в топку, буду на C# делать сервак

быстро ты сдался... ты use database_name то делал?
дай доступ к бд - сам посмотрю

Телевизор 17 декабря 2010 г. 5:32

а может хостер запретить эту функцию в целях безопасности или предотвращения нагрузки сервера?
проверил состояние пишет:
event_scheduler OFF - означает что выключен хостером или просто выключен и нуна его включить другим запросом (каким запросом?) ? ;

Телевизор 17 декабря 2010 г. 5:36

хм... кстати у меня нету доступа к файлам my.ini и my.cnf да и вообще где они должны быть расположены?

Телевизор 17 декабря 2010 г. 5:40

эх... Access denied; you need the SUPER privilege for this operation - походу я сосу (пардон)...
а у хостер есть смысл просить эту функцию как вы думаете?

запросом (каким запросом?)
mysql> SET global event_scheduler = ON;
не помогает?
хм... кстати у меня нету доступа к файлам my.ini и my.cnf да и вообще где они должны быть расположены?
обычно /etc/my.cnf, но его даже может не быть вовсе (будет работать по дефолтным настройкам), поэтому надо создать хотя если у вас хостинг, то скорее всего ничего у вас не выйдет
эх... Access denied; you need the SUPER privilege for this operation — походу я сосу (пардон)...

=)

а у хостер есть смысл просить эту функцию как вы думаете?

Спросить всегда можно, некоторые идут на уступки. Но лучше бы вы взяли вдс (300-600 руб в мес)

Скажите пожалуйста, такой планировщик можно настроить на автоматическое создание дампа (бэкапа) базы данных в конкретное время(в конкретные дни)?

CREATE EVENT not_active_users
ON SCHEDULE
EVERY 1 DAY
DO

   mysqldump -uroot -h82.82.82.82 -p database > database.sql  ( тут я для наглядности добавил)

Можно как то прикрутить такую операцию?

Смотрите http://adw0rd.ru/2009/mysqldump-and-cheat-sheet/#online-backup
Либо только через cron и т.д., т.е. вне MySQL

ps. http://dev.mysql.com/doc/refman/5.0/en/backup-methods.html

А существует ли стандартный механизм проверки "выполняется ли событие" для исключения ситуаций, когда за интервал между срабатывания не успевает выполниться первое срабатывание события?

Ну если подумать, то получается что вам нужен некий коллбек, который срабатывает "когда событие выполнилось" и вызывает некую процедуру. То получается что можно в конце евента добавить вызов этой процедуры.

Или вы хотите чтобы не запускался новый инстанс евента_X, если текущий инстанс евента_X еще не выполнился?

CREATE EVENT `foo_bar`
    ON SCHEDULE EVERY 1 DAY STARTS '2013-01-01 12:00:00'
    DO
        DELETE FROM `obyavi` WHERE `srok` = CURDATE()
;

есть такой запрос, который удаляет из таблици объявы у которых "дата истекла". В какой файл вставить это все. и как он будет запускаться сам?

В какой файл вставить это все

это надо выполнить в mysql

и как он будет запускаться сам?

этим будет заниматься mysqld

я так понял вставляем это в msql и все?

Я вставил, оно теперь будет само запускаться, где можно посмотреть что оно работает?

В статье написано

show events like "event_name%"\G

Событие не работает,почему INTERVAL_VALUE, INTERVAL_FIELD, STARTS, ENDS у меня NULL?

CREATE EVENT `myevent`
    ON SCHEDULE
      AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
      ON COMPLETION PRESERVE
    DO
      UPDATE `test`.`tracks` SET `track` = `track` + 1;     

     EVENT_CATALOG: def
        EVENT_SCHEMA: test
          EVENT_NAME: myevent
             DEFINER: root@localhost
           TIME_ZONE: SYSTEM
          EVENT_BODY: SQL
    EVENT_DEFINITION: UPDATE `test`.`tracks` SET `track` = `track` + 1
          EVENT_TYPE: ONE TIME
          EXECUTE_AT: 2013-10-07 11:32:53
      INTERVAL_VALUE: NULL
      INTERVAL_FIELD: NULL
            SQL_MODE:
              STARTS: NULL
                ENDS: NULL
              STATUS: ENABLED
       ON_COMPLETION: PRESERVE
             CREATED: 2013-10-07 11:31:53
        LAST_ALTERED: 2013-10-07 11:31:53
       LAST_EXECUTED: NULL
       EVENT_COMMENT:
          ORIGINATOR: 1
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci

Оставьте свой комментарий

Markdown