CentOS7.9配置mysql开启ssl

参考文献：
MySQL5.7开启SSL
MySQL :: MySQL 5.7 Reference Manual :: 6.3.1 Configuring MySQL to Use Encrypted Connections

一、生成证书文件

可通过下述命令生成证书文件。对于使用 OpenSSL 编译的 MySQL 发行版，服务器可以在启动时自动在数据目录中生成这些文件，如果已经有了可跳过这一步。

[root@localhost mysql]bin/mysql_ssl_rsa_setup --datadir=/var/lib/mysql/
[root@localhost mysql]chown mysql:mysql /var/lib/mysql -R 

二、修改配置文件

修改mysql的配置文件，增加ssl相关配置

[root@localhost mysql]vi /etc/my.cnf
[root@localhost mysql]# cat /etc/my.cnf|grep ssl
# ssl
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

三、重启数据库

[root@localhost mysql]systemctl restart mysqld

四、查看数据库中ssl状态

mysql> show global variables like '%ssl%';
+-------------------------------------+--------------------------------+
| Variable_name                       | Value                          |
+-------------------------------------+--------------------------------+
| have_openssl                        | YES                            |
| have_ssl                            | YES                            |
| 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_key                             | /var/lib/mysql/server-key.pem  |
+-------------------------------------+--------------------------------+
10 rows in set (0.00 sec)

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using  EditLine wrapper

Connection id:		2
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.40-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			20 sec

Threads: 1  Questions: 6  Slow queries: 0  Opens: 110  Flush tables: 1  Open tables: 103  Queries per second avg: 0.300
--------------

五、创建ssl登录账号

mysql> create user 'ssl_user'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'ssl_user'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

六、登录测试

[root@localhost mysql]# mysql -ussl_user -pDF*c3000 --ssl-mode=required
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.7.40-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.40, for Linux (x86_64) using  EditLine wrapper

Connection id:		16
Current database:	
Current user:		ssl_user@localhost
SSL:			Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.40-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			29 min 10 sec

Threads: 5  Questions: 135  Slow queries: 0  Opens: 117  Flush tables: 1  Open tables: 110  Queries per second avg: 0.077
--------------

可通过下述命令查看ssl版本

mysql> show session status like 'ssl_version';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| Ssl_version   | TLSv1.2 |
+---------------+---------+
1 row in set (0.00 sec)