docker mysql8使用SSL及使用openssl生成自定义证书_mysql 8 cert生成

《docker安装MySQL8》

修改my.cnf

vi /docker_data/mysql/conf/my.cnf
1

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
#增加ssl
ssl
1
2
3
4
5
6
7
8
9

保存，重启mysql容器

docker restart mysql-8.0.23
1

进入mysql容器

docker exec -it mysql-8.0.23 bash
1

容器登录mysql

root@600caf0ddad6:/# mysql -u root -p
1

查看是否开启ssl

mysql> show variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name                       | Value           |
+-------------------------------------+-----------------+
| have_openssl                        | YES             |
| have_ssl                            | YES             |
| ssl_ca                              | ca.pem          |
| ssl_cert                            | server-cert.pem |
| ssl_fips_mode                       | OFF             |
| ssl_key                             | server-key.pem  |
+-------------------------------------+-----------------+
1
2
3
4
5
6
7
8
9
10
11

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

CREATE USER 'x2'@'%' IDENTIFIED WITH mysql_native_password BY 'x2' require ssl PASSWORD EXPIRE NEVER;
grant all on *.* to 'x2'@'%';
FLUSH PRIVILEGES;
exit
1
2
3
4

查看容器里ssl证书位置，得出证书默认位置为：/var/lib/mysql/目录下

root@600caf0ddad6:/# find / -name ca.pem
/var/lib/mysql/ca.pem
1
2

由于安装的时候把/var/lib/mysql/目录映射到了宿主机的/docker_data/mysql/data/目录，因此我直接去这个目录下载证书到windows主机即可。

把这三个证书下载到桌面，用windows的mysql8去连接服务器的mysql，也可以用navicat

windows10 mysql8连服务器的mysql8

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 -u x2 -p
Enter password: **
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42
Server version: 8.0.23 MySQL Community Server - GPL

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:          42
Current database:
Current user:           x2@192.168.1.105
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

出现SSL: Cipher in use is TLS_AES_256_GCM_SHA384表示成功

windows10 navicat连服务器的mysql8

使用openssl生成自定义证书

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

cd /docker_data/mysql/data/

openssl genrsa 2048 > ca-key.pem

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

openssl req -newkey rsa:2048 -days 3600 -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 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

openssl req -newkey rsa:2048 -days 3600 -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 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

正确示例如下所示：

[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 3600 \
>         -key ca-key.pem -out ca.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
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 (eg, your name or your server's hostname) []:a
Email Address []:a
[root@node1 data]# openssl req -newkey rsa:2048 -days 3600 \
>         -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key
.....................................................+++
........................+++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:bb
State or Province Name (full name) []:b
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) []: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 3600 \
>         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=bb/ST=b/L=b/O=b/OU=b/CN=b/emailAddress=b
Getting CA Private Key
[root@node1 data]# openssl req -newkey rsa:2048 -days 3600 \
>         -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key
..............................................................+++
...+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:bb
State or Province Name (full name) []:b
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 3600 \
>         -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=bb/ST=b/L=b/O=b/OU=b/CN=c/emailAddress=b
Getting CA Private Key
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87

验证证书是否正确

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

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

openssl x509 -text -in ca.pem
openssl x509 -text -in server-cert.pem
openssl x509 -text -in client-cert.pem
1
2
3