为MySQL设置SSL主从同步

零、前言

公司准备上线mobile版,但由于PHP版本和主站不一致,所以打算部署到不同的服务器,然后共用同一个MySQL服务器。更不幸的是新服务器和旧服务器处于不同的数据中心,不能直接使用内网连接,所以只能将数据库的读写操作分开——将主站的数据库同步到Mobile服务器,只执行读错做;写操作直接使用SSL连接到主站。幸好访问量不是太大,所以同步造成的延迟可以忽略不计。

一、设置SSL

首先是启用SSL并为其添加证书。进入MySQL命令行界面,输入以下命令

1
mysql> show variables like '%ssl%';

如果出现以下结果,则表明SSL没有开启,如果Value是NO,则表明没有安装SSL模块,这样只能再编译安装了。

1
2
3
4
5
6
7
8
9
10
11
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
+---------------+----------+

主服务器SSL 接着,现在主服务器生成SSL证书,再配置MySQL是SSL生效。首先是用OpenSSL在/etc/mysql/certs/目录生成CA、服务器证书和客户端证书

1
2
3
4
5
6
7
8
9
10
11
12
13
mkdir -p /etc/mysql/certs && cd /etc/mysql/certs
#生成CA
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem > ca-cert.pem
#生成服务器端证书
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
#生成客户端证书
openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem

主服务器只用到ca-cert.pemserver-cert.pemserver-key.pem。修改my.cnf文件,添加SSL配置

1
2
3
4
5
6
[mysqld]
#在mysqld下添加以下
ssl
ssl-ca=/etc/mysql/newcerts/ca-cert.pem
ssl-cert=/etc/mysql/newcerts/server-cert.pem
ssl-key=/etc/mysql/newcerts/server-key.pem

重启MySQL服务器即可

从服务器SSL

将主服务器生成的ca-cert.pem、client-cert.pem和client-key.pem 放到从服务器相对应的目录下,然后修改配置文件my.cnf,在[mysqld]下添加ssl之后重启即可。

二、设置同步

设置好SSL连接后,就可以开始配置同步了。

主服务器 首先是创建slave,在MySQL命令行运行以下指令,其中slave_user是从服务器用到的username,slave_ip是从服务器的ip地址

1
GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'slave_ip' IDENTIFIED BY 'slave_password' REQUIRE SSL;

接着修改配置,是MySQL开启bin log,步骤是在my.cnf的[mysqld]下增加以下语句,然后重启。注意,server-id在几个服务器之间必须是唯一的。

1
2
3
4
5
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = yourdatabase

然后先将现有的数据库dump出来,注意一定要加上–master-data=1,这样可以在不锁表的情况下知道bin文件和对应的位置。

1
/usr/local/webserver/mysql/bin/mysqldump -u root -p -S /tmp/mysql.sock --databases yourdatabase --master-data=1 > slave-source.sql

数据导出后,用scp将slave-source.sql 复制到从服务器。

从服务器

先创建对应的数据库,然后导入刚才的数据

1
/usr/local/webserver/mysql/bin/mysql -u root -p DBNAME < slave-source.sql

然后,使用以下指令找到slave-source.sql对应主服务器的bin文件及位置 head -n 50 slave-source.sql | grep POS 会得到以下输出:

CHANGE MASTER TO MASTER\_LOG\_FILE='standby-bin.000072', MASTER\_LOG\_POS=898280714;

记录下红色部分,一个是对应的文件,另一个就是位置了。然后就可以在从服务器的MySQL命令行开启同步了:

1
CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='slave_user', MASTER_PASSWORD='slave_password', MASTER_LOG_FILE='standby-bin.000072', MASTER_LOG_POS=898280714, MASTER_SSL=1, MASTER_SSL_CA = '/etc/certs/ca-cert.pem', MASTER_SSL_CERT = '/etc/certs/client-cert.pem', MASTER_SSL_KEY = '/etc/certs/client-key.pem';

只需注意替换以上命令对象的ip、用户、密码、文件、位置即可,还有3个证书的路径。 完成后,启用slave即可开启同步(在MySQL命令行执行)

1
2
3
4
START SLAVE;
#查看状态
SHOW SLAVE STATUS \G

参考: 1. How To Set Up MySQL Database Replication With SSL Encryption On CentOS 5.4