If you leave your MailWatch database grow, it might fill up your disk and reduce performance. Here is how to trim it:
Set up a cronjob:
minute hour * * * /path/to/mysql mailwatch_database -u mailwatch_user --password=mailwatch_password < /path/to/mailwatch_mysql_maintenance.sql
Content of /path/to/mailwatch_mysql_maintenance.sql:
delete from maillog where timestamp < date_sub(curdate(), interval 14 day); optimize table maillog;
You may also first archive old log entries into a “archive database”. This way you can always refer to old entries when needed.
1. create a new database called mailscannerarchive. You can achieve this by editing the create.sql script shipped with the installation
Modify the top of the file as below:
CREATE DATABASE /*!32312 IF NOT EXISTS*/ mailscannerarchive; USE mailscannerarchive;
2. Then execute the sql script using the following command
mysql -p < create.sql
This will create a database called “mailscannerarchive” using the same schema as the productive database.
3. Now create a small SQL script called “archive-mailwatchdb.sql” (you can use this script inside cron to automate the archival).
Content of archive-mailwatchdb.sql:
insert into mailscannerarchive.maillog select * from mailscanner.maillog where timestamp < date_sub(curdate(), interval 14 day); delete from mailscanner.maillog where timestamp < date_sub(curdate(), interval 14 day); optimize table mailscanner.maillog;
1. First copy old data (at least 14 days old) to the archive database.
2. drop all data (at least 14 days old) from the production database (you just made a backup it in step 1)
3. optimize the productive database, basically reduce the database and clear all empty spaces from the DB.
now that you have archived the old data and made some space on the production database (which should have also reduced your web response times), you can configure a new web application, which allows users to view the old data when needed.
You can achive this in just a view steps:
1. copy your mailwatch web folder to mailwatch-archive.
2. edit conf.php to tell the archive web application to use the new archive database
Open up mailwatch-archive/conf.php and change the database name. In our case “mailscannerarchive”
// Database settings define(DB_TYPE, 'mysql'); define(DB_USER, 'user'); define(DB_PASS, 'password'); define(DB_HOST, 'localhost'); define(DB_NAME, 'mailscannerarchive');
3. copy the existing web user accounts over to the archive database (you can later use the GUI to customize this easier)
use mailscanner; insert into mailscannerarchive.users select * from users;
First set the database context to mailscanner then copy all users over to the archive database.