海运的博客

1.当插入列为主健或单一索引（可多列）值重复时执行update操作：

INSERT INTO `records` (`domain_id`, `name`, `TYPE`, `content`, `ttl`) VALUES 
(:id, :name, :type, :content, :ttl) ON DUPLICATE KEY UPDATE `change_date` = '1';

2.重复时先删除之前数据再插入新数据：

REPLACE INTO `records` (`domain_id`, `name`, `TYPE`, `content`, `ttl`) VALUES 
(:id, :name, :type, :content, :ttl);

1.使用PHP GeoIP扩展，功能不全，如不能查询AS号：

yum install php-pecl-geoip
#下载最新geo数据库
cd /usr/share/GeoIP/
wget http://www.maxmind.com/download/geoip/database/GeoLiteCountry/GeoIP.dat.gz
gzip -d GeoIP.dat.gz

<?php
echo geoip_country_code_by_name('www.haiyun.me') . "\n";
?>

2.使用GeoIP官方提供的PHP API，不可和上扩展共存，函数冲突。

git clone https://github.com/maxmind/geoip-api-php.git

<?php
include("./src/geoip.inc");
$gi = geoip_open("/usr/share/GeoIP/GeoIP.dat", GEOIP_STANDARD);
echo geoip_country_code_by_name($gi, "www.haiyun.me") . "\n";
?>

GeoIP数据库下载：http://dev.maxmind.com/geoip/legacy/geolite/

安装：

yum install dovecot-mysql

新建数据库：

CREATE DATABASE mailserver;
GRANT SELECT ON mailserver.* TO 'mailuser'@'127.0.0.1' IDENTIFIED BY 'mailuserpass';
FLUSH PRIVILEGES;
USE mailserver;

#虚拟域表
CREATE TABLE `virtual_domains` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#虚拟用户表
CREATE TABLE `virtual_users` (
  `id` int(11) NOT NULL auto_increment,
  `domain_id` int(11) NOT NULL,
  `password` varchar(106) NOT NULL,
  `email` varchar(100) 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(11) NOT NULL auto_increment,
  `domain_id` int(11) 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;

添加测试数据：
INSERT INTO `mailserver`.`virtual_domains`
  (`name`)
VALUES
  ('haiyun.me');
INSERT INTO `mailserver`.`virtual_users`
  (`domain_id`, `password` , `email`)
VALUES
  ('1', ENCRYPT('password', CONCAT('$6$', SUBSTRING(SHA(RAND()), -16))), 'user@haiyun.me');
INSERT INTO `mailserver`.`virtual_aliases`
  (`domain_id`, `source`, `destination`)
VALUES
  ('1', 'alias@haiyun.me', 'user@haiyun.me');

Postfix添加以下配置：

virtual_mailbox_domains = mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
virtual_mailbox_maps = mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
virtual_alias_maps = mysql:/etc/postfix/mysql-virtual-alias-maps.cf
#使用dovecot lmtp交付虚拟域邮件，省却postfix配置用户邮件目录及权限问题
virtual_transport = lmtp:unix:private/dovecot-lmtp 
#使用dovecot验证，后面dovecot配置
smtpd_sasl_type = dovecot
smtpd_sasl_path = private/auth
smtpd_sasl_auth_enable = yes

smtpd_recipient_restrictions =
        permit_sasl_authenticated,
        permit_mynetworks,
        reject_unauth_destination

配置Postfix支持Mysql查询表：

#虚拟域
/etc/postfix/mysql-virtual-mailbox-domains.cf
user = mailuser
password = mailuserpass
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_domains WHERE name='%s'
#虚拟用户
/etc/postfix/mysql-virtual-mailbox-maps.cf 
user = mailuser
password = mailuserpass
hosts = 127.0.0.1
dbname = mailserver
query = SELECT 1 FROM virtual_users WHERE email='%s'
#虚拟别名
/etc/postfix/mysql-virtual-alias-maps.cf 
user = mailuser
password = mailuserpass
hosts = 127.0.0.1
dbname = mailserver
query = SELECT destination FROM virtual_aliases WHERE source='%s'

测试查询：

postmap -q haiyun.me mysql:/etc/postfix/mysql-virtual-mailbox-domains.cf
postmap -q user@haiyun.me mysql:/etc/postfix/mysql-virtual-mailbox-maps.cf
postmap -q alias@haiyun.me mysql:/etc/postfix/mysql-virtual-alias-maps.cf

Dovecot配置：

/etc/dovecot/conf.d/10-mail.conf 
mail_location = maildir:/var/mail/vhosts/%d/%n

/etc/dovecot/conf.d/10-auth.conf
auth_verbose = yes  # 认证详细日志  
auth_debug = yes    # 认证debug
disable_plaintext_auth = no #开启明文验证
auth_mechanisms = plain login #支持验证方法
#!include auth-system.conf.ext #取消默认系统验证
!include auth-sql.conf.ext #使用mysql验证

/etc/dovecot/conf.d/auth-sql.conf.ext
#用户验证
passdb {
  driver = sql
  args = /etc/dovecot/dovecot-sql.conf.ext
}
#用户邮箱目录 %d为domain，%n为user
userdb {
  driver = static
  args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
}

/etc/dovecot/dovecot-sql.conf.ext
driver = mysql
connect = host=127.0.0.1 dbname=mailserver user=mailuser password=mailuserpass
default_pass_scheme = SHA512-CRYPT
password_query = SELECT email as user, password FROM virtual_users WHERE email='%u';

/etc/dovecot/conf.d/10-master.conf
#lmtp服务，postfix通过lmtp交付邮件
service lmtp {
 unix_listener /var/spool/postfix/private/dovecot-lmtp {
   mode = 0600
   user = postfix
   group = postfix
  }
}
#验证服务，postfix也通过此验证用户
service auth {
  unix_listener /var/spool/postfix/private/auth {
    mode = 0666
    user = postfix
    group = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
    #group =
  }
  user = dovecot
}
service auth-worker {
  user = vmail
}

新建用户用以访问邮件目录数据：

useradd -M -u 5000 vmail -d /var/mail -s /sbin/nologin 
chown -R vmail:vmail /var/mail/

参考：https://library.linode.com/email/postfix/postfix2.9.6-dovecot2.0.19-mysql

在使用PHP pcre_match时遇到错误：

Compilation failed: this version of PCRE is not compiled with PCRE_UTF8 support at offset 0

pcretest -C
Compiled with UTF-8 not support

下载对应版本src rpm重新编译pcre：

yum install rpm-build
wget http://vault.centos.org/6.4/os/Source/SPackages/pcre-7.8-6.el6.src.rpm
rpmbuild --rebuild --with enable-utf8 pcre-7.8-6.el6.src.rpm

重新安装编译的软件包：

rpm -iv --replacepkgs /root/rpmbuild/RPMS/i386/pcre-7.8-6.el6.i386.rpm