MySQL archive records based on date column

Photo credit: 27892629@N04 - cc
Photo credit: 27892629@N04cc

Let’s assume you have a logs table and you want to delete the logs which are more than 1 year old.

Ideally you would like to automate this using a cron job.

MySQL Between query

Using the above query we can delete all the logs for the year 2014. However we cannot automate this query since we are providing the dates manually.


Above query deletes all the records which are older than a year. Here we do not need to provide dates. It automatically finds the records which are older than a year using NOW and INTERVAL parameters.

So let’s say you want to delete all the records which are 6 months old then the query would be

Now lets automate the process of archiving our logs table

This can be done through a number of ways.

Shell Script

A shell script can be created with above code. The script can then be added to a cron job.

MySQL event scheduler

Read more

PHP Script

Create a PHP page to run the query and then create a cron job for the PHP page

A php script is the most recommeded system in this case because deleting records does not need lot of memory and it is easier to manage the PHP Script.