docker MariaDB使用SSL及使用OpenSSL生成自定义证书

《docker安装MariaDB》

修改my.cnf

vi /docker_data/mariadb/conf/my.cnf

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
#增加ssl
ssl
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

保存，重启MariaDB容器

docker restart mariadb-10.6.10

进入容器

docker exec -it mariadb-10.6.10 bash

登录mariadb

root@0619cfa2dcd1:/# mysql -uroot -p

查看是否开启ssl

MariaDB [(none)]> show variables like '%ssl%';
+---------------------+-----------------------------+
| Variable_name       | Value                       |
+---------------------+-----------------------------+
| have_openssl        | YES                         |
| have_ssl            | YES                         |
| ssl_ca              | ca.pem                      |
| ssl_cert            | server-cert.pem             |
| ssl_key             | server-key.pem              |
| version_ssl_library | OpenSSL 1.1.1f  31 Mar 2020 |
+---------------------+-----------------------------+

have_openssl和have_ssl必须为YES
创建必须使用ssl登录的账号

GRANT ALL PRIVILEGES ON *.* TO 'x'@'%' IDENTIFIED BY 'x' REQUIRE SSL;
FLUSH PRIVILEGES;

查看容器证书存放路径（没有的话，是因为你没生成。后面生成好了，重启一下MariaDB容器）

root@0619cfa2dcd1:/# find / -name ca.pem
/var/lib/mysql/ca.pem

使用openssl生成自定义证书

由于安装的时候把/var/lib/mysql/目录映射到了宿主机的/docker_data/mariadb/data/目录，因此我直接去这个目录生成证书，然后下载到windows主机即可。
《MariaDB官方文档openssl生成自定义证书》

cd /docker_data/mariadb/data/

openssl genrsa 2048 > ca-key.pem

openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca.pem

openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem

openssl rsa -in server-key.pem -out server-key.pem

openssl x509 -req -in server-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem

openssl rsa -in client-key.pem -out client-key.pem

openssl x509 -req -in client-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

完整示例如下

[root@node1 data]# cd /docker_data/mariadb/data/
[root@node1 data]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
......................+++
...............................................................................................................+++
e is 65537 (0x10001)
[root@node1 data]# openssl req -new -x509 -nodes -days 365000 -key ca-key.pem -out ca.pem
-----
Country Name (2 letter code) [XX]:aa
State or Province Name (full name) []:a
Locality Name (eg, city) [Default City]:a
Organization Name (eg, company) [Default Company Ltd]:a
Organizational Unit Name (eg, section) []:a
Common Name  []:a
Email Address []:a

[root@node1 data]# openssl req -newkey rsa:2048 -days 365000 -nodes -keyout server-key.pem -out server-req.pem
Country Name (2 letter code) [XX]:bb
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:b
Organization Name (eg, company) [Default Company Ltd]:b
Organizational Unit Name (eg, section) []:b
Common Name[]:b
Email Address []:b

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@node1 data]# openssl rsa -in server-key.pem -out server-key.pem
writing RSA key
[root@node1 data]# openssl x509 -req -in server-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=bb/L=b/O=b/OU=b/CN=b/emailAddress=b
Getting CA Private Key

[root@node1 data]# openssl req -newkey rsa:2048 -days 365000 -nodes -keyout client-key.pem -out client-req.pem
Country Name (2 letter code) [XX]:bb
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:b
Organization Name (eg, company) [Default Company Ltd]:b
Organizational Unit Name (eg, section) []:b
Common Name (eg, your name or your server's hostname) []:c
Email Address []:b

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

[root@node1 data]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key

[root@node1 data]# openssl x509 -req -in client-req.pem -days 365000 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=bb/L=b/O=b/OU=b/CN=c/emailAddress=b
Getting CA Private Key

验证证书

[root@node1 data]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

查看证书的内容（例如，检查证书有效的日期范围）

openssl x509 -text -in ca.pem
openssl x509 -text -in server-cert.pem
openssl x509 -text -in client-cert.pem

window使用客户端SSL连接MariaDB

D:\softwareWork\mysql-8.0.23-winx64\bin>mysql --ssl-ca=C:\Users\x\Desktop/ca.pem --ssl-cert=C:\Users\x\Desktop/client-cert.pem --ssl-key=C:\Users\x\Desktop/client-key.pem --ssl-cipher=AES128-SHA -h 192.168.1.111 --port=3307 -u x -p
Enter password: *
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.5.5-10.6.10-MariaDB-1:10.6.10+maria~ubu2004 mariadb.org binary distribution

Copyright (c) 2000, 2021, 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.

mysql> \s
--------------
mysql  Ver 8.0.23 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          8
Current database:
Current user:           x@192.168.1.105
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter:        ;
Server version:         5.5.5-10.6.10-MariaDB-1:10.6.10+maria~ubu2004 mariadb.org binary distribution
Protocol version:       10
Connection:             192.168.1.111 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3307
Binary data as:         Hexadecimal
Uptime:                 12 min 29 sec

Threads: 2  Questions: 16  Slow queries: 0  Opens: 17  Open tables: 10  Queries per second avg: 0.021

SSL: Cipher in use is TLS_AES_256_GCM_SHA384说明使用SSL加密连接

navicat使用SSL连接MariaDB