Search code examples
eventsmysql-event

Create event daily, on a specific time

First event deletes all rows from a table:

CREATE DEFINER=`frontdes_user`@`localhost` EVENT `DELETE_flux_receptie` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO DELETE FROM `flux_receptie`

Second event deletes the id column from the same table:

CREATE DEFINER=`frontdes_user`@`localhost` EVENT `delete_id` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO ALTER TABLE `flux_receptie` DROP `id`

Third event adds id column into the same table:

CREATE DEFINER=`frontdes_user`@`localhost` EVENT `add_id` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO ALTER TABLE `flux_receptie` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`)

I want these events to run daily: First one daily at 06:30, the second one daily at 06:35 and the third daily at 06:40. Can anyone help me please with the correct schedule? Best regards, Bogdan.

Solution

  • If you have access to your machine you can use cronJob, the basic format of a crontab schedule consists of 6 fields, placed on a single line and separated by spaces, formatted as follows:

    minute hour day month day-of-week command-line-to-execute

    first create a script:

    First script

    #!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="CREATE DEFINER=`frontdes_user`@`localhost` EVENT `DELETE_flux_receptie` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO DELETE FROM `flux_receptie`"

    Second script

    #!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="CREATE DEFINER=`frontdes_user`@`localhost` EVENT `delete_id` ON SCHEDULE EVERY 1 DAY STARTS '2015-09-06 00:00:00' ON COMPLETION PRESERVE ENABLE DO ALTER TABLE `flux_receptie` DROP `id`"

    Third script

    #!/bin/bash

mysql --user=[username] --password=[password] --database=[db name] --execute="ALTER TABLE `flux_receptie` ADD `id` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`id`)"

    then create 3 cron using crontab -e

    crontab -e

    30 6 * * * /path/of/scriptOne

    35 6 * * * /path/of/scriptTwo

    40 6 * * * /path/of/scriptThree