As you all are aware you can create a job is MS SQL using the SQL server Job agent and run the jobs in at any interval. Unlike MS SQL, MySQL does not have a Job agent to schedule the jobs and hence you need to create a CRON job in Linux or the MySQL 5.1.12 and above has been introduced with EVENT. You can write a event to run a SP at an interval.
We will see how to write a CRON job to run a Stored Procedure at an Interval. Follow the steps below
At the Prompt> CRON –e
0,5,10,15,20,25,30,35,40,45,50,55 * * * * /root/Call_SP.sh 2>&1 >> /root/call_sp.log
Here I’m specifying an interval of 5 mins, hence the cron job will run the sp every 5 mins
Now create a shell script to call the sp from a database.
At the Prompt> vi Call_SP.sh
mysql -h ‘IPADDRESS’ -u root –p’password’ mydatabase -e "call usp_mydatabasesp()"
Replace the IPADDRESS with the IP and Password with your working password and database and sp names respectively.
This should schedule the CRON job on Linux.
You can create EVENTS in MySQL 5.1.12 or greater, to do the same follow the steps
EDIT My.cnf at /etc directory
SAVE & EXIT
Restart the MYSQL daemon by using the following
At prompt> mysqld –u root &
Get into MySQL and run this Event against the mydatabase DB
CREATE EVENT Event_callmydatabasesp
ON SCHEDULE EVERY ‘5’ MINUTE