mySQL DB purging

Furnish, Trever G TGFurnish at HERFF-JONES.COM
Mon Nov 3 15:35:04 GMT 2003

If you're talking about a mysql database with the mailwatch schema, then
this might do the trick.  Change "7" to match the number of days you want to
keep stuff.  Works for me, YMMV.  Put it into a file (ie
"/root/clean_maillog.sql") and run it with mysql like so:

mysql -u root -pyourpassword mailscanner </root/clean_maillog.sql

No space between -p and yourpassword, of course.  If you don't want that
showing up in the process table listing, you'll have to run it interactively
instead or find some other way to hide the password (for example, by putting
the password into a mode 600 ~/.my.cnf file).

Here are the contents of /root/clean_maillog.sql:

delete low_priority quick from maillog
   where timestamp < (now() - interval 7 day);
optimize table maillog;

Hope it helps,

PS: The site is extremely slow for me today, but the google cache
has a copy of the manual, including this page, which describes keeping your
password secure by putting it into the .my.cnf file like so:


> -----Original Message-----
> From: Kearney, Rob [mailto:RKearney at AZERTY.COM]
> Sent: Thursday, October 30, 2003 9:31 AM
> Subject: mySQL DB purging
> Has anyone written any scripts to purge your growing mysql
> database, and
> willing to share those scripts.
> -rob

More information about the MailScanner mailing list