How to create a SQL Agent Job for MySQL for scheduling the Database Task Using Event Scheduler

To enable or disable the execution of scheduled events, it is necessary to set the value of the global event_scheduler system variable:

GRANT EVENT ON myschema.mytable TO 'root'@'%';

To rescind the EVENT privilege, use the REVOKE statement. In this example, the EVENT privilege on the table mytable of the schema myschema is removed from the 'root'@'%' user account:

REVOKE EVENT ON myschema.mytable FROM 'root'@'%';

Create an event to insert in mytable at every 7 minutes:

CREATE EVENT `insert_event`
    ON SCHEDULE
      EVERY 7 MINUTE
    DO
      INSERT INTO `myschema`.`mytable`
      VALUES (1, 'hiroshi');

You can alter/disable/drop the created events:

ALTER EVENT `insert_event` ON SCHEDULE EVERY 2 MINUTE;
ALTER EVENT `insert_event` DISABLE;
DROP EVENT `insert_event`;

You can also list all created events:

SELECT * FROM INFORMATION_SCHEMA.EVENTS;

If you want to schedule an event at a specific time every day:

CREATE EVENT IF NOT EXISTS `insert_event_fixed`
  ON SCHEDULE
    EVERY 1 DAY
    STARTS (CURRENT_DATE + INTERVAL 1 DAY + INTERVAL 1 HOUR)
    ON COMPLETION PRESERVE
    COMMENT 'Insert at 01:00 daily!'
    DO
      INSERT INTO `myschema`.`mytable` VALUES (1, 'hiroshi');

HiroshiFuu
49 声望7 粉丝