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)