树莓派5 raspberry pi 5 MariaDB ssl证书配置连接
- 服务器： Localhost via UNIX socket
- 服务器类型： MariaDB
- 服务器连接： SSL 未被使用
- 服务器版本： 10.11.14-MariaDB-0+deb12u2 – Debian 12
- 协议版本： 10
- 用户： root@localhost
- 服务器字符集： UTF-8 Unicode (utf8mb4)
一、MariaDB 数据库配置
1、sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld] # SSL 配置 ssl = ON ssl-ca = /etc/mysql/certs/ca-cert.pem ssl-cert = /etc/mysql/certs/server-cert.pem ssl-key = /etc/mysql/certs/server-key.pem require-secure-transport = on #tls_version = TLSv1.3 # 配置兼容性更好的加密套件 ssl_cipher = TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:AES128-GCM-SHA256:AES256-GCM-SHA384 # 允许 TLS 1.2 和 1.3（增加兼容性） tls_version = TLSv1.2,TLSv1.3
2、
sudo systemctl restart mariadb
二、数据库，用户名权限配置
1、
stevenroc@stevenroc:~ $ mysql -u root -p -e "SHOW VARIABLES LIKE '%ssl%';" Enter password: +---------------------+-----------------------------------------------------------------------------------+ | Variable_name | Value | +---------------------+-----------------------------------------------------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/certs/ca-cert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/certs/server-cert.pem | | ssl_cipher | TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:AES128-GCM-SHA256:AES256-GCM-SHA384 | | ssl_crl | | | ssl_crlpath | | | ssl_key | /etc/mysql/certs/server-key.pem | | version_ssl_library | OpenSSL 3.0.20 7 Apr 2026 | +---------------------+-----------------------------------------------------------------------------------+
2、
mysql -u root -p
MariaDB [(none)]> SHOW VARIABLES LIKE 'ssl_cipher'; +---------------+-----------------------------------------------------------------------------------+ | Variable_name | Value | +---------------+-----------------------------------------------------------------------------------+ | ssl_cipher | TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384:AES128-GCM-SHA256:AES256-GCM-SHA384 | +---------------+-----------------------------------------------------------------------------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SHOW VARIABLES LIKE 'tls_version'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | tls_version | TLSv1.2,TLSv1.3 | +---------------+-----------------+ 1 row in set (0.001 sec) MariaDB [(none)]> SELECT User, Host FROM mysql.user WHERE User = 'steven_roc_ssl'; +----------------+--------------+ | User | Host | +----------------+--------------+ | steven_roc_ssl | 192.168.10.% | | steven_roc_ssl | 192.168.11.% | | steven_roc_ssl | localhost | +----------------+--------------+ MariaDB [(none)]> SELECT user, host, ssl_type, authentication_string FROM mysql.user WHERE user = 'steven_roc_ssl'; +----------------+---------------------+----------+-------------------------------------------+ | User | Host | ssl_type | authentication_string | +----------------+---------------------+----------+-------------------------------------------+ | steven_roc_ssl | localhost | X509 | *E82369254BC3EFBF640132F10F53B6BBEAFD77BB | | steven_roc_ssl | 192.168.10.% | X509 | | | steven_roc_ssl | 192.168.11.% | X509 | | | steven_roc_ssl | stevenroc.lan | X509 | *E82369254BC3EFBF640132F10F53B6BBEAFD77BB | | steven_roc_ssl | desktop-vsfb8b9.lan | ANY | *E82369254BC3EFBF640132F10F53B6BBEAFD77BB | +----------------+---------------------+----------+-------------------------------------------+ MariaDB [(none)]> -- 查看用户权限<br /> MariaDB [(none)]> SHOW GRANTS FOR 'steven_roc_ssl'@'DESKTOP-VSFB8B9.lan'; <br /> +---------------------------------------------------------------------------------------------------------------------------------------------+<br /> | Grants for steven_roc_ssl@desktop-vsfb8b9.lan |<br /> +---------------------------------------------------------------------------------------------------------------------------------------------+<br /> | GRANT USAGE ON *.* TO `steven_roc_ssl`@`desktop-vsfb8b9.lan` IDENTIFIED BY PASSWORD '*E82369254BC3EFBF640132F10F53B6BBEAFD77BB' REQUIRE SSL |<br /> | GRANT ALL PRIVILEGES ON `chanking`.* TO `steven_roc_ssl`@`desktop-vsfb8b9.lan` |<br /> +---------------------------------------------------------------------------------------------------------------------------------------------+<br /> 2 rows in set (0.000 sec)<br /> -- 删除所有 steven_roc_ssl 用户 DROP USER IF EXISTS 'steven_roc_ssl'@'localhost'; DROP USER IF EXISTS 'steven_roc_ssl'@'192.168.10.%'; DROP USER IF EXISTS 'steven_roc_ssl'@'192.168.11.%'; DROP USER IF EXISTS 'steven_roc_ssl'@'stevenroc.lan'; DROP USER IF EXISTS 'steven_roc_ssl'@'desktop-vsfb8b9.lan'; -- 重新创建，只保留需要的条目，全部强制 X509 CREATE USER 'steven_roc_ssl'@'192.168.10.%' IDENTIFIED BY 'xiaocaoNB=' REQUIRE X509; CREATE USER 'steven_roc_ssl'@'192.168.11.%' IDENTIFIED BY 'xiaocaoNB=' REQUIRE X509; -- 授予权限 GRANT ALL PRIVILEGES ON chanking.* TO 'steven_roc_ssl'@'192.168.10.%'; GRANT ALL PRIVILEGES ON chanking.* TO 'steven_roc_ssl'@'192.168.11.%'; FLUSH PRIVILEGES; 10、其他 mysql -u steven_roc -p 密码修改 \ =--ssl-ca=/home/stevenroc/MariaDBSSL_certs/ca.pem \ --ssl-cert=/home/stevenroc/MariaDBSSL_certs/client-cert.pem \ --ssl-key=/home/stevenroc/MariaDBSSL_certs/client-key.pem \ -e "SHOW STATUS LIKE 'Ssl_cipher';"
3
1）用户名 steven_roc_ssl 需要ssl证书连接
2）用户名 steven_roc 不需要ssl证书连接
4、
三、
3、
1）用户名 steven_roc_ssl 需要ssl证书连接
2）用户名 steven_roc 不需要ssl证书连接
自适应IP