CODEWITHSUNDEEP
mysqldatabasecron
Gian Kusgiantoro
Gian Kusgiantoro

Reputation: 1

How to delete expired data with cron job

I want to auto delete my data on mysql database which has expired date with cron job every 5 minutes.

For example I have a table (TableName) with one of column (ExpDate) as "2015-04-10 21:30:00", it's timestamp format type and UTC time zone.

I have tried creat a cron job command like this:

*/5 * * * * delete * from TableName where ExpDate > NOW();

But it didn't work, I've searched around in SO and elsewhere but have not found a direct answer to this. So, I created this question and hope someone can help me. I am a beginner in cron job, I would be very grateful if you could help me.

Upvotes: 0

Views: 2616

Answers (1)

pala_
pala_

Reputation: 9010

Cron doesn't know about SQL commands. Cron knows about running executable commands. This leads us to the answer - you need to either directly call the MySQL command line client in the crontab (probably a bad idea), or you need to write a script that does what you want, and then get cron to execute it.

A sample script would be:

USERNAME=[username]
PASSWORD=[password]
DATABASE=[database]
MYSQL=/usr/bin/mysql

$MYSQL -u $USERNAME -p$PASSWORD $DATABASE -e "DELETE from ....."

You would put this script somewhere safe, because it contains your password. Let's say you called it purge_expired.sh, you would need to make it executable like this chmod +x purge_expired.sh, and then tell cron to execute that script instead of trying to make it run the SQL commands itself.

Somehow I missed the memo where MySQL got its own event scheduler, you might want to use that instead.

An example specific to you would be something like:

CREATE EVENT DeleteExpired
  ON SCHEDULE EVERY 5 MINUTE
  DO
    DELETE FROM TableName WHERE ExpDate < NOW();

You also need to make sure the event_scheduler variable is set to on in order for this to run.

Upvotes: 1

Related Questions