MySQL Event Scheduler

Enable the MySQL Event Scheduler

• Log in to your MySQL database as root (if necessary, connect to the server first)
  • ssh user@host
• Verify if event_scheduler is enabled by connecting to mysql server and executing SHOW PROCESSLIST command.
  • mysql -h localhost -u root -p
  • SHOW PROCESSLIST;
• If the event scheduler is not enabled, add event_scheduler = ON to MySQL configuration file, at the [mysqld] section
  • sudo vi /etc/mysql/my.cnf
•  Restart the MySQL server, for the changes to take effect
  • sudo /etc/init.d/mysql stop
  • sudo /etc/init.d/mysql start
• event_scheduler should now be enabled, check through SHOW PROCESSLIST command, again
  • mysql> SHOW PROCESSLIST;
    +—-+—————–+———–+——+———+——+————————+——————+
    | Id | User | Host | db | Command | Time | State | Info |
    +—-+—————–+———–+——+———+——+————————+——————+
    | 1 | event_scheduler | localhost | NULL | Daemon | 18 | Waiting on empty queue | NULL |
    | 38 | root | localhost | NULL | Query | 0 | NULL | SHOW PROCESSLIST |
    +—-+—————–+———–+——+———+——+————————+——————+
    2 rows in set (0.00 sec)

Creating an Event

https://dev.mysql.com/doc/refman/5.7/en/create-event.html

Example:

CREATE EVENT myevent
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
    DO
      UPDATE myschema.mytable SET mycol = mycol + 1;