Dear all,

how do i purge all records older than 1 month from mysql database, there are 30 tables in this db. and how do schedule this to occure automatically.
thanks.

MySQL does not automatically keep track of when each record was inserted into its databases. To do this, you will need to include a "date_posted" field in each record, and supply that information when the record is inserted into the database.

Once this is done, it is simple to remove all the old records:Of course, you can improve on this by noting that not every month has 30 days, ...

To make this happen automatically, you can write a simple shell script that runs mysql, which in turn will source a script containing your DB commands. Then, use the cron facilities to schedule this script to run at the interval you want.

I was looking for something like this, I needed to set the INTERVAL value to get one month previous. As below:


select date_sub(curdate(), INTERVAL 1 MONTH);
+---------------------------------------+
| date_sub(curdate(), INTERVAL 1 MONTH) |
+---------------------------------------+
| 2011-06-13 |
+---------------------------------------+

Try following code

CREATE EVENT 'AutoDeleteOldNotifications'
ON SCHEDULE EVERY 1 DAY STARTS '00:00:00'

DO BEGIN

DELETE * FROM tableName WHERE datetime< DATE_SUB(NOW(),INTERVAL 30 DAY)

END