First of all we create a DB for SA operations if we don't have any yet (otherwise we can use the existing one) and following tables in it (and don't overlook that INSERT after creating bayes_global_vars table):
Code: Select all
CREATE TABLE bayes_expire (
id int(11) NOT NULL default '0',
runtime int(11) NOT NULL default '0',
KEY bayes_expire_idx1 (id)
) ENGINE=InnoDB;
CREATE TABLE bayes_global_vars (
variable varchar(30) NOT NULL default '',
value varchar(200) NOT NULL default '',
PRIMARY KEY (variable)
) ENGINE=InnoDB;
INSERT INTO bayes_global_vars VALUES ('VERSION','3');
CREATE TABLE bayes_seen (
id int(11) NOT NULL default '0',
msgid varchar(200) binary NOT NULL default '',
flag char(1) NOT NULL default '',
PRIMARY KEY (id,msgid)
) ENGINE=InnoDB;
CREATE TABLE bayes_token (
id int(11) NOT NULL default '0',
token binary(5) NOT NULL default '',
spam_count int(11) NOT NULL default '0',
ham_count int(11) NOT NULL default '0',
atime int(11) NOT NULL default '0',
PRIMARY KEY (id, token),
INDEX bayes_token_idx1 (id, atime)
) ENGINE=InnoDB;
CREATE TABLE bayes_vars (
id int(11) NOT NULL AUTO_INCREMENT,
username varchar(200) NOT NULL default '',
spam_count int(11) NOT NULL default '0',
ham_count int(11) NOT NULL default '0',
token_count int(11) NOT NULL default '0',
last_expire int(11) NOT NULL default '0',
last_atime_delta int(11) NOT NULL default '0',
last_expire_reduce int(11) NOT NULL default '0',
oldest_token_age int(11) NOT NULL default '2147483647',
newest_token_age int(11) NOT NULL default '0',
PRIMARY KEY (id),
UNIQUE bayes_vars_idx1 (username)
) ENGINE=InnoDB;
that's all with mySQL.
---------------------------------------------------------------
in etc/spamassassin we write a new .cf (mySQL_bayes.cf for instance) with following content:
Code: Select all
# mySQL bayes
ifplugin Mail::SpamAssassin::Plugin::Bayes
bayes_store_module Mail::SpamAssassin::BayesStore::MySQL
bayes_sql_dsn DBI:mysql:<db_name>:127.0.0.1:3306
bayes_sql_username <mySQL_user_name>
bayes_sql_password <mySQL_password>
bayes_sql_override_username unknown
endif # Mail::SpamAssassin::Plugin::Bayes
---------------------------------------------------------------
before proceeding, we make a backup of old flat file database:
Code: Select all
sa-learn.exe --siteconfigpath="<_path_to_your_etc\spamassassin>" --dbpath "<_path_in_your_OS>\.spamassassin>" --backup > "<_path_in_your_OS>\.spamassassin>\backup\bayes_backup.bak"
(it's a good idea to pause HMS at that stage if it's a spam-busy server. bayes won't work reliable or at all till next step is finished)
if all looks ok, we're almost done.
as last step, we import old flat file database to mySQL by --restore:
Code: Select all
sa-learn.exe --restore="<_path_in_your_OS>\.spamassassin\backup\bayes_backup.bak" -D
note that this may take some time (in my box 2-3 minutes for ~ 180.000 tokens from ~ 5MB file) and command line seems hanging. no worry.
that's all. resume HMS if paused, see if all is running as expected.
training is as usual. you don't need to put any --dbpath or --backup or --sync in your sa-learn batchfile commands anymore. just --spam and --ham are fine.
you may want to backup and delete below files in <_path_in_your_OS>\.spamassassin directory. they're retired :
bayes_journal
bayes_toks
bayes_seen
if you fail to make it work or something goes wrong or you don't see any benefit in using a DB for bayes, reverting to old config is very easy.
just delete mySQL_bayes.cf, restore above flat files (if deleted), restart spamd.