基于ssl mysql_基于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)