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

[MYSQLD]

event_scheduler=ON

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

DELIMITER $$

CREATE EVENT Event_callmydatabasesp

ON SCHEDULE EVERY ’5′ MINUTE

STARTS CURRENT_TIMESTAMP

DO

BEGIN

CALL usp_mydatabasesp();

END$$

DELIMITER ;

-Death

About these ads