Mysql8.0 开启 SSL加密 mysql_ssl_rsa_setup_mysql8.0 开启ssl

1.安装证书

权限不足会失败

mysql_ssl_rsa_setup --datadir=/var/lib/mysql -vvv

chmod 0777 *.pem  
1
2
3

生成如下

[root@10-6-8-197 mysql]# ll *.pem
-rwxrwxrwx 1 root root 1675 Dec 16 11:31 ca-key.pem
-rwxrwxrwx 1 root root 1107 Dec 16 11:31 ca.pem
-rwxrwxrwx 1 root root 1107 Dec 16 11:31 client-cert.pem
-rwxrwxrwx 1 root root 1675 Dec 16 11:31 client-key.pem
-rwxrwxrwx 1 root root 1675 Dec 16 11:31 private_key.pem
-rwxrwxrwx 1 root root  451 Dec 16 11:31 public_key.pem
-rwxrwxrwx 1 root root 1107 Dec 16 11:31 server-cert.pem
-rwxrwxrwx 1 root root 1679 Dec 16 11:31 server-key.pem

1
2
3
4
5
6
7
8
9
10
11

2.修改/etc/my.cnf 在 [mysqld] 下面添加

ssl-ca = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key = /var/lib/mysql/server-key.pem
1
2
3

3.查看

systemctl restart mysqld

mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'AAbb1122!!';

1
2
3
4
5
6

mysql> show global variables like '%ssl%';
+-------------------------------------+--------------------------------+
| Variable_name                       | Value                          |
+-------------------------------------+--------------------------------+
| admin_ssl_ca                        |                                |
| admin_ssl_capath                    |                                |
| admin_ssl_cert                      |                                |
| admin_ssl_cipher                    |                                |
| admin_ssl_crl                       |                                |
| admin_ssl_crlpath                   |                                |
| admin_ssl_key                       |                                |
| have_openssl                        | YES                            |
| have_ssl                            | YES                            |
| mysqlx_ssl_ca                       |                                |
| mysqlx_ssl_capath                   |                                |
| mysqlx_ssl_cert                     |                                |
| mysqlx_ssl_cipher                   |                                |
| mysqlx_ssl_crl                      |                                |
| mysqlx_ssl_crlpath                  |                                |
| mysqlx_ssl_key                      |                                |
| performance_schema_show_processlist | OFF                            |
| ssl_ca                              | /var/lib/mysql/ca.pem          |
| ssl_capath                          |                                |
| ssl_cert                            | /var/lib/mysql/server-cert.pem |
| ssl_cipher                          |                                |
| ssl_crl                             |                                |
| ssl_crlpath                         |                                |
| ssl_fips_mode                       | OFF                            |
| ssl_key                             | /var/lib/mysql/server-key.pem  |
+-------------------------------------+--------------------------------+
25 rows in set (0.00 sec)


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

4.创建用户可以登录

创建用户

create user 'user0001'@'%' identified by 'User0001!';

GRANT ALL PRIVILEGES ON *.* TO 'user0001'@'%';

FLUSH PRIVILEGES;

ALTER USER 'user0001'@'%' IDENTIFIED WITH mysql_native_password BY 'User0001!';
1
2
3
4
5
6
7

设置ssl

alter user user0001@'%' require ssl; 
1

取消ssl

alter user user0001@'%' require none; 


1
2
3

5.查看用户是否使用ssl

mysql> select user,host,ssl_type,ssl_cipher  from mysql.user ;
+------------------+-----------+----------+------------------------+
| user             | host      | ssl_type | ssl_cipher             |
+------------------+-----------+----------+------------------------+
| user0001         | %         |  ANY     | 0x                     |
| mysql.infoschema | localhost |          | 0x                     |
| mysql.session    | localhost |          | 0x                     |
| mysql.sys        | localhost |          | 0x                     |
| root             | localhost |          | 0x                     |
+------------------+-----------+----------+------------------------+
5 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12

6.总结

1、MySQL 默认是开启SSL连接，如果强制用户使用SSL连接，那么应用程序的配置也需要明确指定SSL相关参数，否则程序会报错。
2、虽然SSL方式使得安全性提高了，但是相对地使得QPS也降低23%左右。所以要谨慎选择：

• 2.1、对于非常敏感核心的数据，或者QPS本来就不高的核心数据，可以采用SSL方式保障数据安全性；
• 2.2、对于采用短链接、要求高性能的应用，或者不产生核心敏感数据的应用，性能和可用性才是首要，建议不要采用SSL方式；