Ubuntu - Postfix + Dovecot + MySql - 数据库编

1. 准备数据库户口

$ mysqladmin -p create servermail

$ mysql -u root -p

$ GRANT SELECT ON servermail.* TO 'mail'@'127.0.0.1' IDENTIFIED BY 'password';

$ FLUSH PRIVILEGES;

$ USE servermail;

2. 创建 3个数据库 TABLES

CREATE TABLE `virtual_domains` (
`id`  INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_users` (
`id` INT NOT NULL AUTO_INCREMENT,
`domain_id` INT NOT NULL,
`password` VARCHAR(106) NOT NULL,
`email` VARCHAR(120) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `virtual_aliases` (
`id` INT NOT NULL AUTO_INCREMENT,
`domain_id` INT NOT NULL,
`source` varchar(100) NOT NULL,
`destination` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 准备数据

INSERT INTO `servermail`.`virtual_domains` (`id` ,`name`) VALUES ('1', 'member.dlinkddns.com');

INSERT INTO `servermail`.`virtual_users` (`id`, `domain_id`, `password` , `email`) VALUES
('1', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'boy@member.dlinkddns.com'),
('2', '1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'cat@member.dlinkddns.com');

INSERT INTO `servermail`.`virtual_aliases` (`id`, `domain_id`, `source`, `destination`) 
VALUES ('1', '1', 'all@member.dlinkddns.com', 'boy@member.dlinkddns.com'),
VALUES ('2', '1', 'all@member.dlinkddns.com', 'cat@member.dlinkddns.com');

4. 准备 3 个 mysql-virtual 文件

$ gedit /etc/postfix/mysql-virtual-alias-maps.cf

user = root
password = password
hosts = member.dlinkddns.com
dbname = servermail
query = SELECT destination FROM virtual_aliases WHERE source='%s'

$ gedit /etc/postfix/mysql-virtual-mailbox-domains.cf

user = root
password = password
hosts = member.dlinkddns.com
dbname = servermail
query = SELECT 1 FROM virtual_domains WHERE name='%s'

$ gedit /etc/postfix/mysql-virtual-mailbox-maps.cf

user = root
password = password
hosts = member.dlinkddns.com
dbname = servermail
query = SELECT 1 FROM virtual_users WHERE email='%s'

5. 测试

$ systemctl restart postfix

$ postmap -q all@member.dlinkddns.com mysql:/etc/postfix/mysql-virtual-alias-maps.cf
$ postmap -q member.dlinkddns.com mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
$ postmap -q boy@member.dlinkddns.com mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf

重启后, 用 DNS 连接数据库, 确保能连接
$ mysql -h member.dlinkddns.com -u root -p