OpenBSD 4.6 + Postfix + Mysql + Dovecot + Amavisd + ClamAV + Spamassassin + Roundcubemail (二)

2. MySQL

設定好 Postfix 之後，我們再來就是要設定 MySQL 當作 Postfix 的多網域和虛擬用戶的資料來源。MySQL 是開源軟體中最多人使用的資料庫軟體，有著高效能和設定簡單的方便性。設定好資料庫之後，可以讓 Postfix 和 Dovecot 當作資料來源。

用 Ports 安裝 MySQL：

# cd /usr/ports/databases/mysql
# env SUBPACKAGE="-server" make install

MySQL 初始設定：

# /usr/local/bin/mysql_install_db
[ ... ]
# mysqld_safe &
[ ... ]
# /usr/local/bin/mysql_secure_installation
[ ... ]
Enter current password for root (enter for none): 
OK, successfully used password, moving on...
[ ... ]
Set root password? [Y/n] Y
New password: root
Re-enter new password: root
Password updated successfully!
[ ... ]
Remove anonymous users? [Y/n] Y
... Success!
[ ... ]
Disallow root login remotely? [Y/n] Y
... Success!
[ ... ]
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
[ ... ]
Reload privilege tables now? [Y/n] Y
... Success!
[ ... ]

設定開機啟動 MySQL，這裡特別要說明的是，我們特別在 /var/www/var/run/mysql 裡建立一個mysql.sock 的連結，這樣可以讓我們的Web Server 存取 MySQL時可以使用 localhost 而不是127.0.0.1。

/etc/rc.local

if [ -x /usr/local/bin/mysqld_safe ]; then
  echo -n ' MySQL'
  /usr/local/bin/mysqld_safe > /dev/null 2>&1 &


  for i in 1 2 3 4 5 6 ;do
  if [ -S /var/run/mysql/mysql.sock ]; then
    break
  else
    sleep 1
    echo -n "."
  fi
  done
  ln -f /var/run/mysql/mysql.sock /var/www/var/run/mysql/mysql.sock
fi

接下來，修改 Postfix 設定檔，讓它可以去讀 Mysql 的資料：

/etc/postfix/main.cf
virtual_mailbox_domains = mysql:/etc/postfix/mysql_virtual_domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql_virtual_mailboxes.cf
virtual_alias_maps = mysql:/etc/postfix/mysql_virtual_alias_maps.cf

接下來我們要建立 Postfix 和 Dovecot 存取的資料庫：

# mysql -u root -p
password: root
mysql> CREATE DATABASE mail;
Query OK, 1 row affected (0.01 sec)

mysql> use mail;
Database changed
mysql> CREATE TABLE domains (
->              id         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
->              domain     VARCHAR(255) NOT NULL UNIQUE);
Query OK, 0 rows affected (0.02 sec)


mysql> CREATE TABLE users (
->              id          INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
->              account     VARCHAR(255) NOT NULL,
->              domain      VARCHAR(255) NOT NULL,
->              name        VARCHAR(255) NOT NULL,
->              password    CHAR(13) NOT NULL,
->              uid         SMALLINT NOT NULL DEFAULT 2000,
->              gid         SMALLINT NOT NULL DEFAULT 2000,
->              home        VARCHAR(255) NOT NULL DEFAULT '/var/vmail',
->              maildir     VARCHAR(255) NOT NULL,
->              quota       VARCHAR(10) NOT NULL DEFAULT '10MB');
Query OK, 0 rows affected (0.01 sec)


mysql> CREATE TABLE alias_maps (
->              id         INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
->              account    VARCHAR(255) NOT NULL UNIQUE,
->              alias      VARCHAR(255) NOT NULL);
Query OK, 0 rows affected (0.00 sec)


mysql> GRANT SELECT ON mail.* to 'vmail'@'localhost' IDENTIFIED BY 'vmail';
Query OK, 0 rows affected (0.01 sec)


mysql> INSERT INTO domains (domain) VALUES ('skycat.no-ip.org');
Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO users (account, domain, name, password, maildir)
-> VALUES ('sfyeng', 'skycat.no-ip.org', 'Syfeng Lu', ENCRYPT('password'),
-> 'skycat.no-ip.org/syfeng/');
Query OK, 1 row affected (0.01 sec)


mysql> INSERT INTO alias_maps (account, alias)
-> VALUES ('postmaster@skycat.no-ip.org', 'syfeng@skycat.no-ip.org');
Query OK, 1 row affected (0.00 sec)


mysql> INSERT INTO alias_maps (account, alias)
-> VALUES ('abuse@skycat.no-ip.org', 'syfeng@skycat.no-ip.org');
Query OK, 1 row affected (0.00 sec)

然後，我們要建立在 Postfix 中所指定的三個設定檔，一樣是放在 /etc/postfix 下面

/etc/postfix/mysql_virtual_domains.cf
user = vmail
password = vmail
hosts = 127.0.0.1
dbname = mail
query = SELECT domain FROM domains WHERE domain='%s'


/etc/postfix/mysql_virtual_alias_maps.cf
user = vmail
password = vmail
hosts = 127.0.0.1
dbname = mail
query = SELECT alias FROM alias_maps WHERE account='%s'


/etc/postfix/mysql_virtual_mailboxes.cf
user = vmail
password = vmail
hosts = 127.0.0.1
dbname = mail
query = SELECT maildir FROM users WHERE account='%s'

好了，我們已經完成所有的設定，現在我們可以重新啟動 Postfix

# postfix reload