Differences

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

--- spamassassin:mysql_config [2016/11/14 14:04] – created peter
+++ spamassassin:mysql_config [2019/12/04 21:14] (current) – removed peter
@@ Line 1: / Line 1: @@
-====== SpamAssassin - MySQL config ======
-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>