mysql 8.0 开启自带 ssl证书

作者：马顺华

从事运维管理工作多年，目前就职于某科技有限公司，熟悉运维自动化、OceanBase部署运维、MySQL 运维以及各种云平台技术和产品。并已获得OceanBase认证OBCA、OBCP 证书、OpenGauss社区认证结业证书。OceanBase & 墨天轮第二、三、四届技术征文大赛，多次获得 一、二、三 等奖，时常在墨天轮发布原创技术文章，并多次被首页推荐。

MYSQL版本8.0.32，默认是开启ssl的，同时也是自带证书的在/var/lib/mysql/下

一、概述

当 MySQL 8 在启动时自动在 datadir 目录下生成一堆证书，这通常与新的 MySQL 8 默认配置中的加密功能有关。从 MySQL 8.0 开始，默认情况下启用了对客户端和服务器的 SSL/TLS 加密连接。

二、ssl证书设置

获取CA（证书授权）证书、服务器证书和服务器私钥。这些文件通常为.pem格式。
确保这些文件的权限设置正确，只有数据库管理员能够访问。
auto_generate_certs
默认为on，会影响datadir目录下证书：
ca.pem
ca-key.pem
server-cert.pem
server-key.pem
client-cert.pem
client-key.pem

设置为off，可以将当前证书删除后，重启mysql8的时候，不再自动生成上述证书

三、rsa证书设置

sha256_password_auto_generate_rsa_keys 和 caching_sha2_password_auto_generate_rsa_keys
默认都为on，会影响datadir目录下证书：
private_key.pem
public_key.pem

这俩参数同时设置为off，可以将当前证书删除后，重启mysql8的时候，不再自动生成上述证书

四 、连接Mysql数据库需要使用SSL时，可以按一下步骤进行设置：

前置条件（可跳过）：

1.新建SSL用户

2.对用户使用ssl

3.查看用户情况

mysql> CREATE USER 'qq'@'%' IDENTIFIED BY 'tootbaip#2023';
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON *.* TO 'qq'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> ALTER USER 'qq'@'%' REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ssl_type From mysql.user Where user="qq";
+----------+
| ssl_type |
+----------+
| ANY      |
+----------+
1 row in set (0.00 sec)

mysql> 

4.直接登录 ，无法登录

[root@worker2 mysql]# mysql -uqq -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'qq'@'localhost' (using password: YES)
[root@worker2 mysql]# 

5.使用ssl登录，可以登录

[root@worker2 mysql]# mysql --ssl-ca=/var/lib/mysql/ca.pem --ssl-cert=/var/lib/mysql/client-cert.pem --ssl-key=/var/lib/mysql/client-key.pem --ssl-cipher=AES128-SHA -u qq -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, 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 connection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.
mysql> 

如果连接成功，那么SSL配置就已经生效。

6.查看ssl证书状态

登录到MySQL客户端，然后执行 status 命令：

mysql> status;
--------------
mysql  Ver 8.0.33 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		13
Current database:	
Current user:		qq@localhost
SSL:			Cipher in use is AES128-SHA
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.33 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/lib/mysql/mysql.sock
Binary data as:		Hexadecimal
Uptime:			17 min 14 sec

Threads: 2  Questions: 25  Slow queries: 0  Opens: 172  Flush tables: 3  Open tables: 88  Queries per second avg: 0.024
--------------

7、查看 Mysql 是否启用ssl

show variables like '%ssl%';
mysql> show 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                              | ca.pem          |
| ssl_capath                          |                 |
| ssl_cert                            | server-cert.pem |
| ssl_cipher                          |                 |
| ssl_crl                             |                 |
| ssl_crlpath                         |                 |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
| ssl_session_cache_mode              | ON              |
| ssl_session_cache_timeout           | 300             |
+-------------------------------------+-----------------+
27 rows in set (0.00 sec)

mysql> 

8.进入后输入\s查看ssl加密方式，如果有则为加密ssl登录

mysql> \s
--------------
mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		11
Current database:	
Current user:		qq@localhost
SSL:			Cipher in use is AES128-SHA
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.16 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/lib/mysql/mysql.sock
Uptime:			9 min 42 sec

Threads: 2  Questions: 15  Slow queries: 0  Opens: 159  Flush tables: 3  Open tables: 63  Queries per second avg: 0.025
--------------

mysql> 

9.通过 Navicat 的SSL 连接Mysql