SpamAssassin bayes database SQL conversion guide

a. You’ll need the perl-DBI and perl-DBD-MySQL modules installed before you try this. (and of-course SpamAssassin, MySQL and MailScanner)
b. Some patience and a test environment before you go production.

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: remote_fqdn (this name should be resolvable by DNS or /etc/hosts)

First of all, create a database on the server 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@localhost IDENTIFIED BY 'sa_password';
mysql> GRANT ALL ON sa_bayes.* TO sa_user@local_fqdn IDENTIFIED BY 'sa_password';
mysql> GRANT ALL ON sa_bayes.* TO sa_user@remote_fqdn IDENTIFIED BY 'sa_password';
mysql> flush privileges;

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

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

Note: Check this link at the bottom of the page for the ‘bayes_sql_override_username’ parameter before you take a backup.

Now backup your current bayes database:

sa-learn -p /path/to/spam.assassin.prefs.conf --backup > sa_bayes_backup.txt

Warning: The next command can completely wipe out your bayes database

sa-learn -p /path/to/spam.assassin.prefs.conf --clear #(entirely optional, incase you want to rollback)

Make some changes to your spam.assassin.prefs.conf, on remote (MailScanner 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**
bayes_sql_override_username  root

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

# /etc/init.d/MailScanner stop

..sleep 10 seconds

# /etc/init.d/MailScanner start

Also add this to your crontab

minute hour * * * /path/to/sa-learn --force-expire --sync -p /path/to/spam.assassin.prefs.conf

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

If you need redundancy, here’s a link to the SQL failover patch:
http://bugzilla.spamassassin.org/show_bug.cgi?id=2197

Linked from ‘Backup your current bayes database’.
If you don’t see bayes getting used during message processing in MailScanner, follow this thread and read Steve’s comments on ‘bayes_sql_override_username’, you preferably need to set this parameter before you take a backup of the dbm based bayes.
http://www.jiscmail.ac.uk/cgi-bin/wa.exe?A2=ind0506&L=MAILSCANNER&P=R30185&I=-3 http://article.gmane.org/gmane.mail.virus.mailscanner/29441/match=bayes+sql+override+username if above doesn’t work.