mysql启用ssl

作者：【吴业亮】

博客：https://wuyeliang.blog.youkuaiyun.com/

1、制作ssl证书

[root@www ~]# cd /etc/pki/tls/certs

[root@www certs]# make server.key

umask 77 ; \
/usr/bin/openssl genrsa -aes128 2048 > server.key
Generating RSA private key, 2048 bit long modulus
...
...
e is 65537 (0x10001)
Enter pass phrase:# 输入密码
Verifying - Enter pass phrase:#确认
 
# 从private key 中删除密码

[root@www certs]# openssl rsa -in server.key -out server.key

Enter pass phrase for server.key:# input passphrase
writing RSA key

[root@www certs]# make server.csr

umask 77 ; \
/usr/bin/openssl req -utf8 -new -key server.key -out server.csr
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) []:shanghai   # 省
Locality Name (eg, city) [Default City]: shanghai   # 市
Organization Name (eg, company) [Default Company Ltd]:openstack  # 公司
Organizational Unit Name (eg, section) []:Server World   # 部门
Common Name (eg, your name or your server's hostname) []:www.srv.world   # 主机名
Email Address []:xxx@srv.world # 邮箱
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:#回车
An optional company name []:# Enter

#  openssl x509 -in server.csr -out server.crt -req -signkey server.key -days 3650

Signature ok
subject=/C=CN/ST=shanghai/L=shanghai/O=openstack/OU=computer/CN=www.openstack.com/emailAddress=example@openstack.com
Getting Private key

首先在 MySQL 上执行如下命令, 查询是否 MySQL 支持 SSL:

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.02 sec)

当 have_ssl 为 YES 时, 表示此时 MySQL 服务已经支持 SSL 了. 如果是 DESABLE, 则表示当前环境不支持启用ssl.

修改配置文件/etc/percona-server.conf.d/mysqld.cnf，在[mysqld]中新增

[mysqld]
ssl-ca=/etc/mariadb/pki/ca-bundle.crt
ssl-cert=/etc/mariadb/pki/server.crt
ssl-key=/etc/mariadb/pki/server.key

创建证书目录mkdir -p /etc/mariadb/pki/

拷贝证书（CA 证书, 服务器端的 RSA 私钥, 服务器端的数字证书）

# cp -a /etc/pki/tls/certs/server.key \
/etc/pki/tls/certs/server.crt \
/etc/pki/tls/certs/ca-bundle.crt \
/etc/mariadb/pki/

重启服务

service mysqld restart

验证

mysql>  show variables like '%ssl%'; 
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /etc/mariadb/pki/ca-bundle.crt |
| ssl_capath    |                                |
| ssl_cert      | /etc/mariadb/pki/server.crt    |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /etc/mariadb/pki/server.key    |
+---------------+--------------------------------+
9 rows in set (0.00 sec)

通过ssl登录

mysql -u root -p --ssl-ca=/etc/mariadb/pki/server.crt
mysql> show status like 'ssl_cipher'; 
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| Ssl_cipher    | ECDHE-RSA-AES128-GCM-SHA256 |
+---------------+-----------------------------+
1 row in set (0.00 sec)

不通过ssl登录

# mysql -u root -pChangeme_123
mysql> show status like 'ssl_cipher'; 
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher    |       |
+---------------+-------+
1 row in set (0.00 sec)

强制用户ssl登录

# mysql -u root -p 
mysql> create user admin identified by 'Changeme_123' require ssl; 
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,ssl_type from mysql.user; 
+---------------+-----------+----------+
| user          | host      | ssl_type |
+---------------+-----------+----------+
| root          | localhost |          |
| mysql.session | localhost |          |
| mysql.sys     | localhost |          |
| admin         | %         | ANY      |
+---------------+-----------+----------+
4 rows in set (0.00 sec)

对已经存在的用户配置ssl

mysql> grant usage on *.* to 'test'@'%' require ssl; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>  select user,host,ssl_type from mysql.user; 
+---------------+-----------+----------+
| user          | host      | ssl_type |
+---------------+-----------+----------+
| root          | localhost |          |
| mysql.session | localhost |          |
| mysql.sys     | localhost |          |
| admin         | %         | ANY      |
| test          | %         | ANY      |
+---------------+-----------+----------+

验证通过证书登录

# mysql -uadmin -pChangeme_123 --ssl-ca=/etc/mariadb/pki/server.crt 

mysql>  show status like 'ssl_cipher'; 
+---------------+-----------------------------+
| Variable_name | Value                       |
+---------------+-----------------------------+
| Ssl_cipher    | ECDHE-RSA-AES128-GCM-SHA256 |
+---------------+-----------------------------+
1 row in set (0.00 sec)

关于启用ssl后性能对比这篇文章写得很好
https://www.cnblogs.com/mysql-dba/p/7061300.html