修改my.cnf
vi /docker_data/mysql/conf/my.cnf
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
default_authentication_plugin=mysql_native_password
#增加ssl
ssl
保存，重启mysql容器
docker restart mysql-8.0.23
进入mysql容器
docker exec -it mysql-8.0.23 bash
容器登录mysql
root@600caf0ddad6:/# mysql -u root -p
查看是否开启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 |
+-------------------------------------+-----------------+
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
查看容器里ssl证书位置，得出证书默认位置为：/var/lib/mysql/目录下
root@600caf0ddad6:/# find / -name ca.pem
/var/lib/mysql/ca.pem
由于安装的时候把/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: [email protected]
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
出现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
正确示例如下所示：
[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
验证证书是否正确
[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