MySQL logging

[Brian Berglund]

    [ The following text is in the "iso-8859-1" character set. ]
    [ Your display is set for the "US-ASCII" character set.  ]
    [ Some characters may be displayed incorrectly. ]

I am trying to get Mysql logging working with version 4.34.8
 
Which of these do I need to follow?
Getting SQL mail logging up and running: step by step, quick and
dirtyhere

 (NOTE: This document is README.sql-logging in the MailScanner distribution.)

This document assumes that you have MailScanner 4.12+ installed and configured to taste.

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 ***

CREATE TABLE maillog (
  timestamp timestamp(14) NOT NULL,
  id text,
  size bigint(20) default NULL,
  from_address text,
  to_address text,
  subject text,
  clientip text,
  archives text,
  isspam tinyint(1) default NULL,
  ishighspam tinyint(1) default NULL,
  sascore decimal(7,2) default NULL,
  spamreport text,
  filename text,
  filereport text
) 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 TO mailscanner at 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 at 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 At 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;

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

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

 Is there a way to change this, so the body of the email is logged as well?

 For version 4.25-14, Custom Config appears to have changed
since the above instructions.  It uses three tables and some
other clarifications are needed.

1)The SQL should probably look more like (from Andy Magnusson):

-- MySQL dump 8.22
--
-- Host: localhost    Database: mailscanner
---------------------------------------------------------
-- Server version       3.23.56

--
-- 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;



2) In addition to uncommenting "use DBI;" Look for the following
several lines below:

  # Create database connection
  my($dbh);
  $dbh = DBI->connect("DBI:mysql:mailscanner:192.168.0.51",
                      "mailscanner", "",
                      {'PrintError' => 0})

And change it to:

  # Create database connection
  my($dbh);
  $dbh = DBI->connect("DBI:mysql:$DATABASE_NAME:$IPADDRESS",
                      "$USERNAME", "$PASSWORD",
                      {'PrintError' => 0})

where:
$DATABASE_NAME = Replace with the name of your database
$IPADDRESS = Replace with the IP address of your database server or "localhost"
if on the same server as MailSacnner
$USERNAME = Replace with the Database username you wish to connect to
$PASSWORD = Replace with the password for that username

3) I crank down the time interval that MailScanner restarts from 24
hours to about 30 min.  Upside is that at most I'll loose 30 min worth
of mail logging and have a more current database.  Downside is that
Mailscannerrestarts much more frequently and increases the load on the
server.  

To change, look for "Restart Every = 14400" in MailScanner.conf and
change it to the number of seconds you wish.  I tested my setup with
120 (2 min.) and sent bunches of test messages.  Once I was confident
it was working, I changed it to 1800 (30 Min.) YMMV of course.

 - Andy Sutton
   sutton at pessimists.net
 
------------------------ MailScanner list ------------------------ To
unsubscribe, email jiscmail at jiscmail.ac.uk with the words:
'leave mailscanner' in the body of the email.
Before posting, read the MAQ (http://www.mailscanner.biz/maq/)
and the archives (http://www.jiscmail.ac.uk/lists/mailscanner.html).