(1) By Creating Mysql Event Scheduler
Refer Article : Event Scheduler in Mysql
(2) By using Cron Job
I explained the process using Mysql command line tool. If you are using Oracle, Postgres etc. , you have to use that server’s command line tool to build queries. Changes will be in Sample.sh file.
(1) Create .sql file which will contain series of queries that you want to run periodically.
delete * from mytable;
insert into mytable select * from mytable2;
(2) Now, create script file and write this. To create script file, write vi Sample.sh to terminal and later copy below command to script.
mysql –user=root –password=root database_name < /home/Sample.sql
Replace above line with your username, password and database name.
(3) Now, using crontab, schedule your script which will execute our queries periodically.
0 1 * * * /home/Sample.sh
This will run trigger Sample.sh file every 1 hour which will run our queries.
Note:- We have created both Sample.sql and Sample.sh at location /home.