SA 3.0.2 and MySQL

[Steve Campbell]

    [ 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. ]

Excellent!
 
What a great way to teach others. Although I may not implement this, it
certainly educates me about SA and more.
 
Thank you for your time and efforts.
 
Steve Campbell
campbell at cnpapers.com
Charleston Newspapers
      ----- Original Message -----
From: Dhawal Doshy
To: MAILSCANNER at JISCMAIL.AC.UK
Sent: Tuesday, April 05, 2005 12:41 PM
Subject: Re: SA 3.0.2 and MySQL

Pete Russell wrote:
      Mmm that sounds like a treat. Would make a great
      addition to the new
      wiki documentation project.

      Care to write a guide and post it up?

okay, here goes, apologies for the HTML:

You'll need the perl-DBI and perl-DBD-MySQL modules installed
before you try this.

Assumptions and Variables:
SpamAssassin Bayes Database Name: sa_bayes
SpamAssassin Bayes Database UserName: sa_user
SpamAssassin Bayes Database Password: sa_password
Your database server's real name: local_fqdn
Every other server that'll connect to and use this database, this
name should be resolvable by the DNS or /etc/hosts: remote_fqdn

First of all, create a database where you intend on storing the
bayesian information
# mysql -u root -p #(some distros like redhat and clones have no
password set by default.)
mysql> create database sa_bayes;
mysql> GRANT ALL ON sa_bayes.* TO sa_user at localhost IDENTIFIED BY
'sa_password';
mysql> GRANT ALL ON sa_bayes.* TO sa_user at local_fqdn IDENTIFIED BY
'sa_password';
mysql> GRANT ALL ON sa_bayes.* TO sa_user at remote_fqdn IDENTIFIED BY
'sa_password';

Locate the bayes_mysql.sql file
For RedHat(like) systems:
/usr/share/doc/spamassassin-%version%/sql/bayes_mysql.sql

# mysql -u sa_user -p sa_bayes < /path/to/bayes_mysql.sql

Now backup your current bayes database:
sa-learn -p /path/to/spam.assassin.prefs.conf --backup >
sa_bayes_backup.txt
sa-learn -p /path/to/spam.assassin.prefs.conf --clear #(optional,
incase you want to rollback)

Make some changes to your spam.assassin.prefs.conf, on remote (MS
Front-end) servers replace 'localhost' with the value of
'local-fqdn'

bayes_store_module      Mail::SpamAssassin::BayesStore::SQL
bayes_sql_dsn           DBI:mysql:sa_bayes:localhost
bayes_sql_username      sa_user
bayes_sql_password      sa_password

and comment out the following lines
bayes_path /etc/MailScanner/bayes/bayes
bayes_file_mode 0660

Now for recovering the bayes_dbm to bayes_sql
# sa-learn -p /path/to/spam.assassin.prefs.conf --restore
sa_bayes_backup.txt

And finally the acid test
# spamassassin -x -D -p /path/to/spam.assassin.prefs.conf --lint

Check for lines like
debug: bayes: Database connection established
debug: bayes: found bayes db version 3
debug: bayes: Using userid: 2
and some more like
debug: bayes: tok_get_all: Token Count: 20
debug: bayes token 'somewhat' => 0.978
debug: bayes: score = 0.845189622547555

Make sure you stop and start MailScanner..

Futther reading / discussion can be taken up here:
/usr/share/doc/spamassassin-%version%/sql/README.bayes OR on the
sa-users / sa-dev lists

cheers,
- dhawal

PS: Any mistakes are entirely mine (any yours if you follow this
blindly), please test thoroughly before you go production.
Also i couldn't find any way to post this to the new wiki, can
someone guide me?
------------------------ 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).

Support MailScanner development - buy the book off the website!

------------------------ 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).

Support MailScanner development - buy the book off the website!