MySQL Event

Optimize MySQL database

In MySQL you can add an event to a database that runs in a interval to automate database tasks. You can wait for intervals and check the table to see the changes.

By default, the event scheduler thread is not enabled in the database. You need to enable this:

SET GLOBAL event_scheduler = ON;

Or you can off this by:

SET GLOBAL event_scheduler = OFF;

You can see by below command whether event scheduler thread is running or not:

SHOW PROCESSLIST;

Create an MySQL Event:

CREATE EVENT evt_newsletter
ON SCHEDULE EVERY 2 MINUTE
DO
INSERT INTO newsletter(message,post_date)
VALUES('Newsletter added',NOW());

Changing schedule:

ALTER EVENT evt_newsletter
ON SCHEDULE EVERY 5 MINUTE;

You can change the body also

ALTER EVENT evt_newsletter
DO
INSERT INTO newsletter(message,post_date)
VALUES('Newsletter changed',NOW());

Disable event:

ALTER EVENT evt_newsletter DISABLE;

Enable event:

ALTER EVENT evt_newsletter ENABLE;

Rename event:

ALTER EVENT evt_newsletter RENAME TO evt_newsletter_new;

Drop MySQL event:

DROP EVENT [IF EXIST] evt_newsletter;

Leave a Reply

Your email address will not be published. Required fields are marked *