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)
- mysql> SHOW PROCESSLIST;
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;