Linux MariaDB使用OpenSSL安装SSL证书

进入到证书存放目录，批量删除.pem证书
警告：确保已经进入到证书存放目录

find . -type f -iname \*.pem -delete

查看是否安装OpenSSL

openssl version

没有则安装

yum install openssl openssl-devel

开启SSL

编辑/etc/my.cnf文件（没有的话就创建，但是要注意，在/etc/my.cnf.d/server.cnf配置了datadir的，在/etc/my.cnf也要配置，否则datadir就会失效，数据重新生成到/var/lib/mysql/目录下）

vim /etc/my.cnf

[mysqld]
ssl
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

OpenSSL生成SSL文件（进入mariadb的datadir目录，如果是自定义的则进入到自定义的datadir生成SSL文件，如果使用系统默认路径，则进入到/var/lib/mysql/）

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

警告：
ca和server、client的值必须不一致！
server、client的Common Name的值必须不一致！server、client的其它值必须一致！
正确内容如下所示：

[root@centos7 mariaDB_data]# openssl genrsa 2048 > ca-key.pem

[root@centos7 mariaDB_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@centos7 mariaDB_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) []:b
Locality Name (eg, city) [Default City]:b
Organization Name (eg, company) [Default Company Ltd]:b
Organizational Unit Name (eg, section) []:b
#这里必须和client的不一样
Common Name []:b
Email Address []:b
A challenge password []:
An optional company name []:

[root@centos7 mariaDB_data]# openssl rsa -in server-key.pem -out server-key.pem
writing RSA key

[root@centos7 mariaDB_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/ST=b/L=b/O=b/OU=b/CN=b/emailAddress=b
Getting CA Private Key

[root@centos7 mariaDB_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) []:b
Locality Name (eg, city) [Default City]:b
Organization Name (eg, company) [Default Company Ltd]:b
Organizational Unit Name (eg, section) []:b
#这里必须和server的不一样
Common Name (eg, your name or your server's hostname) []:c
Email Address []:b
A challenge password []:
An optional company name []:

[root@centos7 mariaDB_data]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
[root@centos7 mariaDB_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/ST=b/L=b/O=b/OU=b/CN=c/emailAddress=b
Getting CA Private Key

#验证证书是否正确
[root@centos7 mariaDB_data]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

#重启mariadb，使/etc/my.cnf配置文件生效
[root@centos7 mariaDB_data]# systemctl restart mariadb

文件说明：
ca.pem: CA 证书, 用于生成服务器端/客户端的数字证书.
ca-key.pem: CA 私钥, 用于生成服务器端/客户端的数字证书.
server-key.pem: 服务器端的 RSA 私钥
server-req.pem: 服务器端的证书请求文件, 用于生成服务器端的数字证书.
server-cert.pem: 服务器端的数字证书.
client-key.pem: 客户端的 RSA 私钥
client-req.pem: 客户端的证书请求文件, 用于生成客户端的数字证书.
client-cert.pem: 客户端的数字证书.

进入MariaDB创建一个使用SSL链接的用户

[root@centos7 data]# mysql -uroot -p
#创建使用SSL的用户x
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'x'@'%' IDENTIFIED BY 'x' REQUIRE SSL;
MariaDB [(none)]> FLUSH PRIVILEGES;
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.0.2k-fips  26 Jan 2017   |
+---------------------+------------------------------------+

window使用客户端SSL连接MariaDB（确保Windows10安装了MySQL或MariaDB）

下载客户端证书到桌面
ca.pem
client-cert.pem
client-key.pem

Win+R cmd进入到MySQL的bin目录用命令行链接MariaDB服务端，如：D:\softwareWork\mysql-8.0.23-winx64\bin

d:
cd D:\softwareWork\mysql-8.0.23-winx64\bin
#然后使用桌面的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.56.11 -u x -p
#查看是否使用ssl连上的，SSL:                    Cipher in use is AES128-SHA表示使用了SSL
mysql> \s
--------------
Current user:           x@192.168.56.122
SSL:                    Cipher in use is AES128-SHA
--------------

连不上的话看看MariaDB是否开放防火墙

开放防火墙3306端口以及重启防火墙

firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload