mySQL DB purging

[Furnish, Trever G]

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,
Trever

PS: The mysql.com 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:

[client]
password=your_pass

http://216.239.37.104/search?q=cache:_N_M9VjlRhUJ:www.mysql.com/doc/en/Passw
ord_security.html+mysql+manual+keeping+your+password+secure&hl=en&ie=UTF-8

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