实验：实现MySQL主从加密复制

一、实验的准备环境

一台用于CA签署和生成证书的主机，一台是MySQL的主服务器，最后一台是MySQL的从服务器。

二、CA签署证书和颁发证书

• 生成CA的自签名证书

# 生成CA的私钥
openssl genrsa -out cakey.pem 2048
# 自签名CA的证书
openssl req -new -x509 -key cakey.pem -out cacert.pem -days 3650

You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:CN
State or Province Name (full name) []:SiChuan
Locality Name (eg, city) [Default City]:SiChuan
Organization Name (eg, company) [Default Company Ltd]:test.com
Organizational Unit Name (eg, section) []:OPT    
Common Name (eg, your name or your server's hostname) []:my.test.com
Email Address []:

• 生成主服务器的证书

openssl req -newkey rsa:2048 -days 365 -nodes -keyout master.key -out master.csr
openssl x509 -req -in master.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 -out master.crt

• 生成从服务器的证书

openssl req -newkey rsa:2048 -days 365 -nodes -keyout slave.key -out slave.csr
openssl x509 -req -in slave.csr -CA cacert.pem -CAkey cakey.pem -set_serial 01 -out slave.crt

• 复制对应服务器的私钥和证书

# 主服务器（CA的证书、主服务器的证书、主服务器的私钥）
scp cacert.pem master.key master.crt 192.168.30.7:/etc/my.cnf.d
 
# 从服务器（CA的证书、从服务器的证书、从服务器的私钥）
scp cacert.pem slave.crt slave.key 192.168.30.114:/etc/my.cnf.d

三、主服务器

• 配置my.cnf

vim /etc/my.cnf
[mysqld]
log_bin
server_id=1
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/master.crt
ssl-key=/etc/my.cnf.d/ssl/master.key

• 创建主从服务器同步用帐号

mysql> grant replication slave on *.* to repluser@'192.168.30.%' identified by 'test' require ssl;

• 检查是否开启ssl

mysql > show variables like '%ssl%';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| have_openssl  | YES                          |
| have_ssl      | YES                          |
| ssl_ca        | /etc/my.cnf.d/ssl/cacert.pem |
| ssl_capath    |                              |
| ssl_cert      | /etc/my.cnf.d/ssl/master.crt |
| ssl_cipher    |                              |
| ssl_key       | /etc/my.cnf.d/ssl/master.key |
+---------------+------------------------------+

四、从服务器

• 配置 my.cnf

vim /etc/my.cnf
[mysqld]
server_id=2
ssl
ssl-ca=/etc/my.cnf.d/ssl/cacert.pem
ssl-cert=/etc/my.cnf.d/ssl/slave.crt
ssl-key=/etc/my.cnf.d/ssl/slave.key

• 开启从服务器同步

mysql> CHANGE MASTER TO 
  MASTER_HOST='192.168.30.7', 
  MASTER_USER='repluser', 
  MASTER_PASSWORD='test',
  MASTER_PORT=3306, 
  MASTER_LOG_FILE='mariadb-bin.000004', 
  MASTER_LOG_POS=29077986,
  MASTER_CONNECT_RETRY=10,   
  MASTER_SSL=1;
mysql> start slave

• 检查同步状态