Menu
I have to update my MySQL database every hour, and I was wondering what the advantages/disadvantages of using a cronjob VS a MySQL event? For example, which is faster? Which is safer? Thanks!
214
What is MySql Event Scheduler? MySQL Events are tasks that execute on a specified time and can be scheduled hence also knowns as Scheduled events. Scheduler events are set off one or more than one SQL statement that executes on single or multiple times at specified intervals.
MySQL events are similar to a cronjob on Linux or a task scheduler on Windows. MySQL Event Scheduler manages the schedule and execution of Events. MySQL Events can be very useful in many cases such as optimizing database tables, cleaning up logs, archiving data, or generating complex reports during off-peak time.
SELECT * FROM INFORMATION_SCHEMA. events; You can also use SHOW CREATE EVENT yourevent to see what it does.
Some database tasks require/prefer mysql to be off-line (eg full backup), so you've got to use cron for those - it's a bad idea to have some tasks done with cron and some done with mysql; you'll be unsure where to look You can chain up other events that should follow if you've got a shell script
For example, you can create an event that optimizes all tables in the database that runs at 1:00 AM every Sunday. MySQL Events are also known as “temporal triggers” because they are triggered by time, not by DML events like normal triggers. MySQL events are similar to a cronjob on Linux or a task scheduler on Windows.
However, the abbreviation also alludes to Chronos, the ancient Greek god of time. In operating systems such as Linux or macOS, however, the command runs as a cron daemon (also called a cron system). So, there you have it. The allusion to the Greek god of time is, by the way, an excellent mnemonic to recall the purpose of a CronJob.
With MySQL 5.1 the guys at MySQL introduced a new cool feature: The MySQL Event Scheduler ! With the Event Scheduler you can schedule tasks that you want to perform on your database. This is great for web developers who can’t create cron jobs on their webspace, because their host won’t let them!
I would always go a cron job, because:
And finally, just because you can do something, doesn't mean it's a good idea. Mysql is good at data stuff. Don't use it for "shell" stuff.
138
MySQL Event Scheduler – A good replacement for cron.
We all know about cron, an easy way to schedule certain processes, like truncating your log tables in your MySQL database every week.
With MySQL 5.1 the guys at MySQL introduced a new cool feature: The MySQL Event Scheduler !
With the Event Scheduler you can schedule tasks that you want to perform on your database. This is great for web developers who can’t create cron jobs on their webspace, because their host won’t let them! It really is a great replacement for cron!
A few examples:
you want to truncate your application log table every week, this is how your event schedule should look like:
CREATE EVENT PurgeLogTable ON SCHEDULE EVERY 1 WEEK DO BEGIN DELETE FROM `logs` WHERE `LogTime` <= DATE_SUB(CURRENT_TIMESTAMP,INTERVAL 1 WEEK); INSERT INTO `audit` (`AuditDate`, `Message`) VALUES(NOW(), "Log table purged succesfully!"); END If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
