Getting SQL mail logging up and running: step by step, quick and dirty

This document assumes that you have a recent vintage of MailScanner installed and configured.

1) Install MySQL and start the daemon running. Instructions are available at
http://www.mysql.com/doc/

2) Create a database called 'mailscanner'.

# mysqladmin create mailscanner

3) Create a table called maillog. The easiest way is to copy the following text to a file and
'source' it. That is, open the MySQL client (as root)

# mysql

and type 'use mailscanner', then 'source <filename>'.

-- BEGIN SQL ***

--
-- Table structure for table 'maillog_mail'
--

CREATE TABLE maillog_mail (
  id int(10) unsigned NOT NULL auto_increment,
  time timestamp(14) NOT NULL,
  msg_id varchar(15) binary default NULL,
  size bigint(20) default NULL,
  from_user varchar(35) default NULL,
  from_domain varchar(35) default NULL,
  subject text,
  clientip text,
  archives text,
  isspam int(1) default NULL,
  ishighspam int(1) default NULL,
  sascore decimal(7,2) default NULL,
  spamreport text,
  PRIMARY KEY  (id),
  KEY msg_id (msg_id),
  KEY from_domain (from_domain,from_user)
) TYPE=MyISAM;

--
-- Table structure for table 'maillog_recipient'
--

CREATE TABLE maillog_recipient (
  id bigint(20) NOT NULL auto_increment,
  msg_id varchar(15) binary default NULL,
  to_user varchar(35) default NULL,
  to_domain varchar(35) default NULL,
  PRIMARY KEY  (id),
  KEY msg_id (msg_id),
  KEY to_domain (to_domain,to_user)
) TYPE=MyISAM;

--
-- Table structure for table 'maillog_report'
--

CREATE TABLE maillog_report (
  id bigint(20) NOT NULL auto_increment,
  msg_id varchar(15) binary default NULL,
  filename text,
  filereport text,
  PRIMARY KEY  (id),
  KEY msg_id (msg_id)
) TYPE=MyISAM;

-- END SQL

4) (optional) Create a dedicated user for this logging function. The default code in
MailScanner uses the 'root' MySQL user, with no password. While easy, it's very insecure.
In the MySQL client:

> GRANT ALL ON mailscanner.maillog TO mailscanner@localhost IDENTIFIED BY '<password>';

This command creates the 'mailscanner' user and grants it privileges to the 'mailscanner' 
database from 'localhost' only. To allow remote logins (say, if MySQL is located on a 
different machine), replace 'localhost' with an IP address. (NB: '%' is the wildcard 
character, so you can use it, e.g. 'mailscanner@10.12.%' to allow connections from 
specified networks.)

5) Test that you can log in to the database with that username and password.

$ mysql -u mailscanner -p<password> (NOTE: No space between -p and <password>)
> use mailscanner
> show tables;

If this is successful, then you should be good to go. If MySQL is on a separate machine,
use -h <host> in the above command. Test it from the MailScanner machine to make sure that
you did the 'grant' command correctly.

6) If you haven't gotten it already, install the Perl 'DBI' and 'DBD-MySQL' packages. I
had trouble when I got them through CPAN and had better luck with RPMs; YMMV. 

At this point, you should be ready to enable SQL logging in MailScanner.

7) Uncomment the 'use DBI;' line in the SQL logging section of CustomConfig.pm

8) In MailScanner.conf, change the value of 'Always Looked Up Last' from 'No' to 
'&SQLLogging'

9) Restart MailScanner. Check your mail log for 'Starting SQL Logging' message. If
you don't see any errors, you should be all set. Send some test messages and restart
MailScanner to force it to write the logs to the database. Check the mail log again
to see if it kicked up DB errors.

10) Open up mysql and check the database to see if it logged your messages.

# mysql
> use mailscanner
> SELECT to_address, from_address, subject FROM maillog_mail;

If it returns a table with that information, you should be all set!

------------------
Send corrections, additions to andrew.magnusson@cocc.com