基于SSL的MySQL主从

master 端

配置CA和证书

[root@baseos-1_192.168.31.140 ~]# cd /etc/pki/CA/
#生成根证书的私钥
[root@baseos-1_192.168.31.140 CA]# (umask 077; openssl genrsa 2048 > private/cakey.pem) 
***
#生成自签证书
[root@baseos-1_192.168.31.140 CA]# openssl req -new -x509 -key private/cakey.pem  -out cacert.pem -days 3650 
***
Common Name (eg, your name or your server's hostname) []:master # 自己填写，与下面的一直即可。
***
[root@baseos-1_192.168.31.140 CA]# mkdir certs crl newcerts 
[root@baseos-1_192.168.31.140 CA]# touch index.txt 
[root@baseos-1_192.168.31.140 CA]# echo 01 > serial  #指明证书的开始编号
[root@baseos-1_192.168.31.140 CA]# mkdir -p /data/ssl && cd /data/ssl
#生成key
[root@web4399_smsmq_121.14.36.73 ssl]# (umask 077; openssl genrsa -out master.key 2048)    
Generating RSA private key, 2048 bit long modulus
.........+++
...........................................................................+++
e is 65537 (0x10001)
#制作证书申请文件
[root@baseos-1_192.168.31.140 ssl]# openssl req -new -key master.key -out master.csr -days 365  
***
Common Name (eg, your name or your server's hostname) []:master
***
#签署证书
[root@baseos-1_192.168.31.140 ssl]# openssl ca -in master.csr -out master.crt -days 3650 
***
Sign the certificate? [y/n]:y
***
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
[root@baseos-1_192.168.31.140 ssl]# ll
total 12
-rw-r--r-- 1 root root 3614 Jun 27 16:22 master.crt
-rw-r--r-- 1 root root  623 Jun 27 16:15 master.csr
-rw------- 1 root root  891 Jun 27 16:15 master.key

[root@baseos-1_192.168.31.140 ssl]# cp /etc/pki/CA/cacert.pem . 
chown -R mysql:mysql /data/ssl/master*
[root@baseos-1_192.168.31.140 ssl]# chown -R mysql:mysql /data/ssl/master*

至此，证书生成完毕，如果签发证书时，遇到如下错误：

[root@baseos-1_192.168.31.140 ssl]# openssl ca -in master.csr -out master.crt -days 3650
Using configuration from /etc/pki/tls/openssl.cnf
Check that the request matches the signature
Signature ok
The mandatory stateOrProvinceName field was missing

修改openssl 配置文件：

[root@baseos-1_192.168.31.140 ssl]# vim /etc/pki/tls/openssl.cnf
***
stateOrProvinceName = optional
organizationName    = optional
***

配置mysql

[root@baseos-1_192.168.31.140 ssl]# vim /etc/my.cnf  
ssl
ssl-ca=/data/ssl/cacert.pem
ssl-cert=/data/ssl/master.crt
ssl-key=/data/ssl/master.key

重启MySQL之后，查看SSL是否生效

(root@localhost) [(none)] show variables like '%ssl%';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| have_openssl  | YES                  |
| have_ssl      | YES                  |
| ssl_ca        | /data/ssl/cacert.pem |
| ssl_capath    |                      |
| ssl_cert      | /data/ssl/master.crt |
| ssl_cipher    |                      |
| ssl_crl       |                      |
| ssl_crlpath   |                      |
| ssl_key       | /data/ssl/master.key |
+---------------+----------------------+
9 rows in set (0.07 sec)

将证书传递的slave端，并授权

[root@baseos-1_192.168.31.140 ssl]# scp -P 22-o StrictHostKeyChecking=no -r cacert.pem master.crt master.key  192.168.31.130:/data/ssl/
[root@baseos-1_192.168.31.140 ssl]#  mysql -uroot -p
(root@localhost) [(none)] grant replication slave,replication client on *.* to 'slave_user'@'192.168.31.130' identified by 'slave12346' require X509;
Query OK, 0 rows affected (0.00 sec)

slave 端

[root@baseos-2_192.168.31.130 ~]# chown mysql:mysql -R /data/ssl/*
[root@baseos-2_192.168.31.130 ~]#  mysql -uroot -p
(root@localhost) [(none)] stop slave;
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [(none)]  CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000022', MASTER_LOG_POS=120 , MASTER_HOST = "192.168.31.140" , MASTER_USER = "slave_user" , master_password = "slave12346" ,
                               master_ssl=1,master_ssl_ca='/data/ssl/cacert.pem', master_ssl_cert='/data/ssl/master.crt', master_ssl_key='/data/ssl/master.key';
Query OK, 0 rows affected, 2 warnings (0.01 sec)
(root@localhost) [(none)] start slave;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [(none)] show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.140
                  Master_User: slave_user
                  Master_Port: 3306
***
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
***
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /data/ssl/cacert.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /data/ssl/master.crt
            Master_SSL_Cipher: 
               Master_SSL_Key: /data/ssl/master.key
***
1 row in set (0.00 sec)