Bo's Oracle Station › 博客首页 ›Oracle技术› Oracle云、Linux、MySQL和Java › 查看内容

【博客文章2026】MySQL 9.x数据库管理技巧8：使用安全的SSL加密方式连接MySQL数据库

2026-3-25 02:50| 发布者: admin| 查看: 30| 评论: 0|原作者: Bo Tang

摘要: MySQL默认使用未加密的客户端到服务器端的连接。MySQL也支持客户端与服务器端之间的SSL连接。根据具体应用的需求，可以选择普通的未加密连接，也可以选择安全的SSL加密连接。本博客通过实验展示安全连接MySQL服务器的方法。

Author: Bo Tang

MySQL默认使用未加密的客户端到服务器端的连接。MySQL也支持客户端与服务器端之间的SSL连接。根据具体应用的需求，可以选择普通的未加密连接，也可以选择安全的SSL加密连接。

SSL利用X509标准进行通信加密。X509用于在互联网上标识个人身份，最常见于电子商务应用中。整个过程简单来说，需要有一个受信任的证书颁发机构（CA），为有需要的用户颁发电子证书。证书基于非对称加密算法，该算法使用一对密钥（公钥和私钥）。证书持有者可以将证书提供给另一方以证明自己的身份。证书中包含持有者的公钥及其他信息，并由受信任的CA签名。使用该公钥加密的任何数据，只有持有对应私钥的证书持有者才能解密。

1. 查看MySQL数据库的网络连接配置：

在我们的实验环境中，MySQL 9.6服务器所在的Linux主机的IP地址是：172.25.250.95；MySQL客户端所在的主机的IP地址是：172.25.250.1。首先，查看MySQL服务器是否支持网络连接：

mysql> show variables like '%net%';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| net_buffer_length | 16384 |

| net_read_timeout | 30 |

| net_retry_count | 10 |

| net_write_timeout | 60 |

| replica_net_timeout | 60 |

| skip_networking | OFF |

| slave_net_timeout | 60 |

+---------------------+-------+

7 rows in set (0.006 sec)

“skip_networking=OFF”说明MySQL服务器支持网络连接。接下来，查看MySQL服务器所监听的TCP端口：

[root@station95 ~]# netstat -lntp | grep mysql

tcp6 0 0 :::3309 :::* LISTEN 180124/mysqld

tcp6 0 0 :::33060 :::* LISTEN 180124/mysqld

可以看到MySQL服务器在0.0.0.0上的3309端口监听，说明网络上的任何客户端都可以连接到该MySQL服务器。

2. MySQL数据库的默认非加密连接：

2.1 创建一个用于网络连接的用户。并为了方便进行实验，而给该用户授权与world_y数据库相关的所有权限：

mysql> create user 'test'@'172.25.250.1' identified by 'oracle_4U' WITH MAX_CONNECTIONS_PER_HOUR 10;

Query OK, 0 rows affected (0.016 sec)

mysql> grant all on world_y to 'test'@'172.25.250.1';

Query OK, 0 rows affected (0.002 sec)

2.2 进行默认的非加密网络连接：

[root@cc ~]# mysql -u test -h 172.25.250.95 --port=3309 -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 56

Server version: 9.6.0 MySQL Community Server - GPL

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.

mysql>

2.3 查看变量ssl_cipher：

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 | |

| 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 |

+-------------------------------------+-----------------+

25 rows in set (0.005 sec)

mysql> show status like 'ssl%';

+--------------------------------+-------+

| Variable_name | Value |

+--------------------------------+-------+

| Ssl_accept_renegotiates | 0 |

| Ssl_accepts | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_cipher | |

| Ssl_cipher_list | |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_ctx_verify_depth | 0 |

| Ssl_ctx_verify_mode | 0 |

| Ssl_default_timeout | 0 |

| Ssl_finished_accepts | 0 |

| Ssl_finished_connects | 0 |

| Ssl_server_not_after | |

| Ssl_server_not_before | |

| Ssl_session_cache_hits | 0 |

| Ssl_session_cache_misses | 0 |

| Ssl_session_cache_mode | NONE |

| Ssl_session_cache_overflows | 0 |

| Ssl_session_cache_size | 0 |

| Ssl_session_cache_timeout | 0 |

| Ssl_session_cache_timeouts | 0 |

| Ssl_sessions_reused | 0 |

| Ssl_used_session_cache_entries | 0 |

| Ssl_verify_depth | 0 |

| Ssl_verify_mode | 0 |

| Ssl_version | |

+--------------------------------+-------+

26 rows in set (0.004 sec)

发现其值为空，说明当前连接为非加密连接。

3. 查看X509的相关文件：

3.1 查看默认的CA私钥：

[root@station95 ~]# cd /var/lib/mysql

[root@station95 mysql]# ls -l ca-key.pem

-rw------- 1 mysql mysql 1680 Mar 27 23:37 ca-key.pem

3.2 查看默认的CA数字证书：

[root@station95 mysql]# ls -l ca.pem

-rw-r--r-- 1 mysql mysql 1108 Mar 27 23:37 ca.pem

3.3 查看默认的MySQL服务器的私钥：

[root@station95 mysql]# ls -l server-key.pem

-rw------- 1 mysql mysql 1676 Mar 27 23:37 server-key.pem

3.4 查看默认的由CA签名的MySQL服务器的数字证书：

[root@station95 mysql]# ls -l server-cert.pem

-rw-r--r-- 1 mysql mysql 1108 Mar 27 23:37 server-cert.pem

3.5 查看默认的MySQL客户端的私钥：

[root@station95 mysql]# ls -l client-key.pem

-rw------- 1 mysql mysql 1676 Mar 27 23:37 client-key.pem

3.6 查看默认的由CA签名的MySQ客户端的数字证书：

[root@station95 mysql]# ls -l client-cert.pem

-rw-r--r-- 1 mysql mysql 1108 Mar 27 23:37 client-cert.pem

4. 使用安全的SSL加密方式连接MySQL数据库：

4.1 在网络客户端下载服务器端的数字证书（公钥）：

在客户端172.25.250.1上操作：

[root@cc mysql]# mysql -u test -h 172.25.250.95 --port=3309 -p --get-server-public-key

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 9

Server version: 9.6.0 MySQL Community Server - GPL

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.

mysql>

4.2 执行加密连接：

[root@cc mysql]# mysql -u test -h 172.25.250.95 --port=3309 -p --ssl-mode=REQUIRED

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 12

Server version: 9.6.0 MySQL Community Server - GPL

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.

mysql> show status like 'ssl%';

+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Variable_name | Value |

| Ssl_accept_renegotiates | 0 |

| Ssl_accepts | 3 |

| Ssl_callback_cache_hits | 0 |

| Ssl_cipher | TLS_AES_128_GCM_SHA256 |

| Ssl_cipher_list | TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:TLS_CHACHA20_POLY1305_SHA256:TLS_AES_128_CCM_SHA256:ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-ECDSA-AES256-CCM:ECDHE-ECDSA-AES128-CCM:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES256-CCM:DHE-RSA-AES128-CCM:DHE-RSA-CHACHA20-POLY1305 |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_ctx_verify_depth | 18446744073709551615 |

| Ssl_ctx_verify_mode | 5 |

| Ssl_default_timeout | 7200 |

| Ssl_finished_accepts | 3 |

| Ssl_finished_connects | 0 |

| Ssl_server_not_after | Mar 24 15:37:25 2036 GMT |

| Ssl_server_not_before | Mar 27 15:37:25 2026 GMT |

| Ssl_session_cache_hits | 0 |

| Ssl_session_cache_misses | 0 |

| Ssl_session_cache_mode | SERVER |

| Ssl_session_cache_overflows | 0 |

| Ssl_session_cache_size | 128 |

| Ssl_session_cache_timeout | 300 |

| Ssl_session_cache_timeouts | 0 |

| Ssl_sessions_reused | 0 |

| Ssl_used_session_cache_entries | 0 |

| Ssl_verify_depth | 18446744073709551615 |

| Ssl_verify_mode | 5 |

| Ssl_version | TLSv1.3 |

26 rows in set (0.00 sec)

4.3 修改用户，强制执行加密连接：

mysql> alter user 'test'@'172.25.250.1' require ssl;

Query OK, 0 rows affected (0.005 sec)

mysql> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select * from user where Host='172.25.250.1' and user='test'\G

*************************** 1. row ***************************

Host: 172.25.250.1

User: test

Select_priv: N

Insert_priv: N

Update_priv: N

Delete_priv: N

Create_priv: N

Drop_priv: N

Reload_priv: N

Shutdown_priv: N

Process_priv: N

File_priv: N

Grant_priv: N

References_priv: N

Index_priv: N

Alter_priv: N

Show_db_priv: N

Super_priv: N

Create_tmp_table_priv: N

Lock_tables_priv: N

Execute_priv: N

Repl_slave_priv: N

Repl_client_priv: N

Create_view_priv: N

Show_view_priv: N

Create_routine_priv: N

Alter_routine_priv: N

Create_user_priv: N

Event_priv: N

Trigger_priv: N

Create_tablespace_priv: N

ssl_type: ANY

ssl_cipher: 0x

x509_issuer: 0x

x509_subject: 0x

max_questions: 0

max_updates: 0

max_connections: 10

max_user_connections: 0

plugin: caching_sha2_password

authentication_string: $A$00A$Z0HM,sc8.o/gJ\dohP0Hs.Iiespz2ni.LltTp6jI0FAl6HvDlCSxh0KIB

password_expired: N

password_last_changed: 2026-03-25 19:25:20

password_lifetime: NULL

account_locked: N

Create_role_priv: N

Drop_role_priv: N

Password_reuse_history: NULL

Password_reuse_time: NULL

Password_require_current: NULL

User_attributes: NULL

1 row in set (0.001 sec)

4.4 进行连接测试：在客户端172.25.250.1上操作：

mysql> show status like 'ssl_cipher';

+---------------+------------------------+

| Variable_name | Value |

+---------------+------------------------+

| Ssl_cipher | TLS_AES_128_GCM_SHA256 |

+---------------+------------------------+

1 row in set (0.01 sec)

可以看到，即使不写“--ssl-mode=REQUIRED”，由于用户属性是“require ssl”，所以使用默认使用SSL加密连接。

路过

雷人

握手

鲜花

鸡蛋

收藏邀请

上一篇：博客文章2026】MySQL 9.x数据库管理技巧7：分区和分区性能

【博客文章2026】MySQL 9.x数据库管理技巧8：使用安全的SSL加密方式连接MySQL数据库

相关分类