Schedule SQL Query to execute on specific time/interval

Imagine, you have created some stuff and to demo it, you have created a page where you accept dummy data from your guest users and you are populating them into your database. At some point of time, your database would be flooded with records that would be useless and this will definitely slow down the performance of your database. Now, in this case, you need to manually clean up your database in order to keep the performance of your database at it best.

But, instead of doing it manually you would think about something, which should automate cleanup work on regular interval. At this point of time, you probably think about CRON job. But, instead, we will use MySQL database inbuilt option called EVENTS, which we can use to execute certain task in database like executing SQL script, calling stored procedure etc.. 

Also, please note that, this is not like triggers. Triggers are fired on data or structure change but MySQL events are scheduled based on time/interval.

First of all Enable EVENT Scheduler

SET GLOBAL event_scheduler = ON;

Then, validate current status of Event Scheduler

SELECT @@event_scheduler;

Now, create EVENT to execute your SQL query

CREATE EVENT e_store_ts 
ON SCHEDULE
EVERY 1 HOUR
DO
UPDATE myschema.yourtable set mycolumn='N' -- update this table

List all EVENT created

SHOW EVENTS;

That's it. !!

Schedule MySQL Database backup on CPanel or Linux

If you running a business online and you are using MySQL database to keep your customer and product related information. Then, you always try to keep database backed up, just in case, if your database got crashed, you will have a copy of data. 

Taking manual backup will never be a good idea as it takes time and resource whereas you will find many paid services which will help you to keep your database backed up on regular interval. But, if you are looking for something, which can do same job without paying single penny to any third party. Then, this might be useful article for you

First of all, we create a backup folder, where we will keep our database backup dump file. And then, we will write the shell script, which will create database backup file and place it to our backup folder. 

#!/bin/sh
now="$(date +'%d_%m_%Y_%H_%M_%S')"
filename="db_backup_$now".gz
backupfolder=“"
fullpathbackupfile="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +'%Y_%m')".txt
echo "mysqldump started at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
mysqldump —user= —password= --default-character-set=utf8  | gzip > "$fullpathbackupfile"
echo "mysqldump finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
chown  "$fullpathbackupfile"
chown  "$logfile"
echo "file permission changed" >> "$logfile"
find "$backupfolder" -name db_backup_* -mtime +2 -exec rm {} \;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +'%d-%m-%Y %H:%M:%S')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

Above script will create backup file and place it to your backup folder and also it will remove dump file, which is older than 2 days. You can keep this shell script file to any location (but I would recommend to keep this file in same backup folder)

Now, depending on your requirement, you should schedule this script to run on specific time/interval by adding it to CRON job. And to add new CRON job, you need to provide following details, path of shell script file (i.e. command) and email id where you want to receive notification for any failure/success.

Once, you have successfully created CRON job, then you will see a CRON job entry created, which will be executed only specified time/interval 




Upon your specified time/interval, your backup folder will start getting populated with your database backup and log files.

That's it. !!

Let me know, if you got into any issue while configuring it.