Differences

This shows you the differences between two versions of the page.

--- spamassassin:mysql_config [2016/11/14 14:07] – peter
+++ spamassassin:mysql_config [2019/12/04 21:14] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== SpamAssassin - MySQL config ======
-configure SpamAssassin to store its volatile data in a MySQL database
-In **/etc/default/spamassassin** add the options **--sql-config (-q)** and **--nouser-config (-x)** to the OPTIONS configuration.
-===== Create a database and a user in MySQL =====
-<code bash>
-mysql -u root -p mysql <<-EOF
-  CREATE DATABASE spamassassin;
-  CREATE USER 'spamassassin'@'localhost' IDENTIFIED BY PASSWORD 'my_spamassassin_password';
-  GRANT ALL ON spamassassin.* TO 'spamassassin'@'localhost';
-  FLUSH PRIVILEGES;
-EOF
-</code>
-===== Setting up users' score =====
-SpamAssassin can load user specific settings from the database, if the **user_scores_dsn** variable is set (and spamd is started with the --sql-config option).
-<code>
-user_scores_dsn              dbi:mysql:spamassassin:localhost
-user_scores_sql_username     spamassassin
-user_scores_sql_password     my_spamassassin_password
-</code>
-For the default query to work the **userpref** table must have at least the username, preference and value fields.  The username field contains the username whose e-mail is being filtered or **@GLOBAL** for a global option.
-<code mysql>
-CREATE TABLE userpref (
-  username varchar(100) NOT NULL default '',
-  preference varchar(50) NOT NULL default '',
-  value varchar(100) NOT NULL default '',
-  prefid int(11) NOT NULL auto_increment,
-  PRIMARY KEY (prefid),
-  KEY username (username));
-INSERT INTO userpref (username, preference, value) VALUES ('@GLOBAL', 'required_hits', '4.0');
-# INSERT INTO userpref (username, preference, value) VALUES ('alice', 'whitelist_from', '*@example.com');
-</code>
-===== Setting up the auto-whitelist configuration =====
-The setting below tells SpamAssassin to use the auto-whitelist in the SQL database.
-<code>
-auto_whitelist_factory       Mail::SpamAssassin::SQLBasedAddrList
-user_awl_dsn                 dbi:mysql:spamassassin:localhost
-user_awl_sql_username        spamassassin
-user_awl_sql_password        my_spamassassin_password
-user_awl_sql_table           awl
-</code>
-Here is the default table layout:
-<code mysql>
-CREATE TABLE awl (
-  username varchar(100) NOT NULL default '',
-  email varchar(255) NOT NULL default '',
-  ip varchar(40) NOT NULL default '',
-  count int(11) NOT NULL default '0',
-  totscore float NOT NULL default '0',
-  signedby varchar(255) NOT NULL default '',
-  PRIMARY KEY (username, email, signedby, ip));
-</code>
-SpamAssassin only adds data to the auto-whitelist table and does not delete from it.  Hence it is suggested to change the table to include a timestamp of the last modification for each record.  With this additional column it is possible to selectively delete old and unused records.
-<code mysql>
-ALTER TABLE awl ADD lastupdate timestamp default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
-UPDATE awl SET lastupdate = NOW() WHERE lastupdate < 1;
-</code>
-The following statements delete entries that are older than 6 months and addresses that occurred only once in the past 15 days.
-<code mysql>
-DELETE FROM awl WHERE lastupdate <= DATE_SUB(SYSDATE(), INTERVAL 6 MONTH);
-DELETE FROM awl WHERE count = 1 AND lastupdate <= DATE_SUB(SYSDATE(), INTERVAL 15 DAY);
-</code>
-===== Setting up the Bayes configuration =====
-The Bayes settings are similar to the ones seen above.
-<code>
-bayes_store_module           Mail::SpamAssassin::BayesStore::SQL
-bayes_sql_dsn                dbi:mysql:spamassassin:localhost
-bayes_sql_username           spamassassin
-bayes_sql_password           my_spamassassin_password
-#bayes_sql_override_username  mail
-</code>
-The tables with the default layout are created with the following commands.
-<code mysql>
-CREATE TABLE bayes_expire (
-  id int(11) NOT NULL default '0',
-  runtime int(11) NOT NULL default '0',
-  KEY bayes_expire_idx1 (id));
-CREATE TABLE bayes_global_vars (
-  variable varchar(30) NOT NULL default '',
-  value varchar(200) NOT NULL default '',
-  PRIMARY KEY  (variable));
-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));
-CREATE TABLE bayes_token (
-  id int(11) NOT NULL default '0',
-  token char(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));
-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));
-</code>