MySQL5.6/ 5.7 SSL配置

短命女 2022-05-29 06:51 392阅读 0赞

专题一：mysql5.7上开启并配置ssl

\[root@mysqlmaster01 bin\]\# ./mysql\_ssl\_rsa\_setup --datadir=/data/mysql\_data1/ --user=mysql

Generating a 2048 bit RSA private key  
............................................................................+++  
............+++  
writing new private key to 'ca-key.pem'  
\-----  
Generating a 2048 bit RSA private key  
.......................+++  
..........................+++  
writing new private key to 'server-key.pem'  
\-----  
Generating a 2048 bit RSA private key  
...........+++  
..........+++  
writing new private key to 'client-key.pem'  
\-----

查看

（SSL还是没有启用）

解决办法：把数据目录下.pem的文件，属主和属组改成mysql

\[root@mysqlmaster01 mysql\_data1\]\# chown -R mysql.mysql \*.pem

然后重启服务

\[root@mysqlmaster01 mysql\_data1\]\# /etc/init.d/mysqld\_multi stop 1

\[root@mysqlmaster01 mysql\_data1\]\# /etc/init.d/mysqld\_multi start 1  
\[root@mysqlmaster01 mysql\_data1\]\# /etc/init.d/mysqld\_multi report  
Reporting MySQL servers  
MySQL server from group: mysqld1 is running

\[root@mysqlmaster01 mysql\_data1\]\# mysql --login-path=mysql1 -e "show variables like 'have%ssl%';"  
\+---------------+-------+  
| Variable\_name | Value |  
\+---------------+-------+  
| have\_openssl | YES |  
| have\_ssl | YES |  
\+---------------+-------+

(说明ssl已经启用咯）

![1263760-20171124112432171-1599655006.png][]

\[root@mysqlmaster01 mysql\_data1\]\# ll \*.pem  
\-rw-------. 1 mysql mysql 1679 Nov 24 11:14 ca-key.pem  
\-rw-r--r--. 1 mysql mysql 1107 Nov 24 11:14 ca.pem  
\-rw-r--r--. 1 mysql mysql 1107 Nov 24 11:14 client-cert.pem  
\-rw-------. 1 mysql mysql 1679 Nov 24 11:14 client-key.pem  
\-rw-------. 1 mysql mysql 1679 Nov 24 11:14 private\_key.pem  
\-rw-r--r--. 1 mysql mysql 451 Nov 24 11:14 public\_key.pem  
\-rw-r--r--. 1 mysql mysql 1107 Nov 24 11:14 server-cert.pem  
\-rw-------. 1 mysql mysql 1675 Nov 24 11:14 server-key.pem

如何通过ssl进行连接

\[root@mysqlmaster01 mysql\_data2\]\# mysql -u ssl -p -h 10.2.11.226 --ssl-cert=/data/mysql\_data2/client-cert.pem --ssl-key=/data/mysql\_data2/client-key.pem -P 3307  
Enter password:   
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 15  
Server version: 5.7.20-log MySQL Community Server (GPL)

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> \\q

（默认如果授权没有做任何限制，用户既可以通过秘钥登录，也可以通过用户名和密码登录）

用户授权规定只能通过ssl方式登录

mysql> create user 'tom'@'10.2.11.%' identified by 'Aa123456';  
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on \*.\* to 'tom'@'10.2.11.%' require ssl;  
Query OK, 0 rows affected, 1 warning (0.00 sec)

测试

\[root@mysqlmaster01 ~\]\# mysql -u tom -p -h 10.2.11.226 --ssl-mode 'REQUIRED' -P 3306   
Enter password:   
Welcome to the MySQL monitor. Commands end with ; or \\g.

mysql>

mysql> \\s  
\--------------  
mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86\_64) using EditLine wrapper

Connection id: 25  
Current database:  
Current user: tom@10.2.11.226  
SSL: Cipher in use is DHE-RSA-AES256-SHA  
Current pager: stdout  
Using outfile: ''  
Using delimiter: ;  
Server version: 5.7.20-log MySQL Community Server (GPL)  
Protocol version: 10  
Connection: 10.2.11.226 via TCP/IP  
Server characterset: latin1  
Db characterset: latin1  
Client characterset: utf8  
Conn. characterset: utf8  
TCP port: 3306  
Uptime: 1 hour 34 min 11 sec

Threads: 2 Questions: 56 Slow queries: 0 Opens: 124 Flush tables: 1 Open tables: 117 Queries per second avg: 0.009  
\--------------

如果不仅需要ssl还需要秘钥，那么怎么操作呢？

mysql> alter user 'tom'@'10.2.11.%' require x509;  
Query OK, 0 rows affected (0.01 sec)

或者新建一个用户，要求ssl+秘钥登录

mysql> grant all on \*.\* to 'test'@'10.2.11.%' identified by 'Aa123456' require x509;  
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all on \*.\* to 'test'@'10.2.18.%' identified by 'Aa123456' require x509;  
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

测试登录：

\[root@mysqlmaster01 mysql\_data1\]\# mysql -u test -p -h 10.2.11.226 -P 3306 --ssl  
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.  
Enter password:   
ERROR 1045 (28000): Access denied for user 'test'@'10.2.11.226' (using password: YES)

（发现通过ssl登录不了）

![1263760-20171124133948875-72642899.png][]

远程客户端连接测试（我以mysql workbench为例）

![1263760-20171124135231734-50422475.png][]

![1263760-20171124135256484-1613377423.png][]

![1263760-20171124135324421-227121977.png][]

第二专题：mysql5.6上开启并配置ssl

https://dev.mysql.com/doc/refman/5.6/en/using-encrypted-connections.html

1、加密连接服务端配置

\[mysqld\]

ssl-ca=ca.pem

ssl-cert=server-cert.pem

ssl-key=server-key.pem

说明：

ss-ca：证书颁发机构(CA)证书文件的路径名

ssl-cert：服务器公钥证书文件的路径名。这可以发送到客户端，并通过CA证书进行身份验证。

ssl-key：服务器的私钥证书文件的路径名

2、客户端使用ssl

案例：

mysql  \--ssl\-ca=ca.pem  \--ssl\-cert=client\-cert.pem  \--ssl\-key=client\-key.pem

![1263760-20171124142020687-349811375.png][]

通过openssl 制作生成 SSL 证书

\[root@mysqlmaster01 CA\]\# touch index.txt  
\[root@mysqlmaster01 CA\]\# echo 01>serial

创建CA证书

\[root@server mysql56\]\# openssl genrsa 2048 > ca-key.pem  
Generating RSA private key, 2048 bit long modulus  
...............................................+++  
......................................................................................................................+++  
e is 65537 (0x10001)  
\[root@server mysql56\]\# 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\]:CN  
State or Province Name (full name) \[\]:shanghai  
Locality Name (eg, city) \[Default City\]:shanghai  
Organization Name (eg, company) \[Default Company Ltd\]:als  
Organizational Unit Name (eg, section) \[\]:ops  
Common Name (eg, your name or your server's hostname) \[\]:ca.test.com  
Email Address \[\]:  
\[root@server mysql56\]\# ll \*.pem  
\-rw-r--r--. 1 root root 1679 Nov 24 15:15 ca-key.pem  
\-rw-r--r--. 1 root root 1314 Nov 24 15:16 ca.pem

创建服务器证书

\[root@server mysql56\]\# 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\]:CN  
State or Province Name (full name) \[\]:shanghai  
Locality Name (eg, city) \[Default City\]:shanghai  
Organization Name (eg, company) \[Default Company Ltd\]:als  
Organizational Unit Name (eg, section) \[\]:ops  
Common Name (eg, your name or your server's hostname) \[\]:server.test.com  
Email Address \[\]:

\[root@server mysql56\]\# 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=CN/ST=shanghai/L=shanghai/O=als/OU=ops/CN=server.test.com  
Getting CA Private Key

创建客户端证书

\[root@server mysql56\]\# 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\]:CN  
State or Province Name (full name) \[\]:shanghai  
Locality Name (eg, city) \[Default City\]:shanghai  
Organization Name (eg, company) \[Default Company Ltd\]:als  
Organizational Unit Name (eg, section) \[\]:ops  
Common Name (eg, your name or your server's hostname) \[\]:client.test.com   
Email Address \[\]:

Please enter the following 'extra' attributes  
to be sent with your certificate request  
A challenge password \[\]:  
An optional company name \[\]:  
\[root@server mysql56\]\# openssl rsa -in client-key.pem -out client-key.pem   
writing RSA key  
\[root@server mysql56\]\# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set\_serial 02 -out client-cert.pem  
Signature ok  
subject=/C=CN/ST=shanghai/L=shanghai/O=als/OU=ops/CN=client.test.com  
Getting CA Private Key

检测：

\[root@mysqlmaster01 mysql56\]\# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem   
server-cert.pem: OK  
client-cert.pem: OK

说明：

*  `ca.pem`: Use this as the argument to [`--ssl-ca`][--ssl-ca] on the server and client sides. (The CA certificate, if used, must be the same on both sides.)
 *  `server-cert.pem`, `server-key.pem`: Use these as the arguments to [`--ssl-cert`][--ssl-cert] and [`--ssl-key`][--ssl-key] on the server side.
 *  `client-cert.pem`, `client-key.pem`: Use these as the arguments to [`--ssl-cert`][--ssl-cert] and [`--ssl-key`][--ssl-key] on the client side.

\[root@mysqlmaster01 mysql56\]\# chown -R mysql.mysql \*.pem （更改属主和属组）

编写my.cnf文件，在【mysqld】下填写

ssl-ca=/data/mysql56/ca.pem   
ssl-cert=/data/mysql56/server-cert.pem   
ssl-key=/data/mysql56/server-key.pem

测试

![1263760-20171124164135203-1746601647.png][]

mysql> grant all on \*.\* to 'test'@'10.2.11.%' identified by 'Aa123456' require x509; （授权test用户通过ssl+秘钥登录）  
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;  
Query OK, 0 rows affected (0.00 sec)

\[root@mysqlmaster01 ~\]\# mysql -u test -h 10.2.11.226 -p -P 3308  
Enter password:   
ERROR 1045 (28000): Access denied for user 'test'@'10.2.11.226' (using password: YES)

（直接用密码登录错误）

\[root@mysqlmaster01 ~\]\# mysql -u test -h 10.2.11.226 -p -P 3308 -ssl-cert=client-cert.pem --ssl-key=client-key.pem --ssl-ca=ca.pem  
mysql: \[ERROR\] mysql: unknown option '-l'  
\[root@mysqlmaster01 ~\]\# mysql -u test -h 10.2.11.226 -p -P 3308 --ssl-cert=client-cert.pem --ssl-key=client-key.pem --ssl-ca=ca.pem  
Enter password:   
ERROR 2026 (HY000): SSL connection error: SSL\_CTX\_set\_default\_verify\_paths failed  
\[root@mysqlmaster01 ~\]\# mysql -u test -h 10.2.11.226 -p -P 3308 \--ssl-cert=/data/mysql56/client-cert.pem --ssl-key=/data/mysql56/client-key.pem  
Enter password:   
Welcome to the MySQL monitor. Commands end with ; or \\g.  
Your MySQL connection id is 5  
Server version: 5.6.38-log MySQL Community Server (GPL)

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>

（如果要在其他电脑上通过ssl登录该机器的数据库，必须要ca.pem,client-cert.pem,client-key.pem拷贝到其他电脑上，然后配置连接数据库的工具使用ssl）

[1263760-20171124112432171-1599655006.png]: /images/20220529/36031ba9afe8421d8677b807aa808bca.png
[1263760-20171124133948875-72642899.png]: /images/20220529/425b55aec9784c15ae1747283fdd301b.png
[1263760-20171124135231734-50422475.png]: /images/20220529/9240f6ea5406483fb29f2d2551e4fb3c.png
[1263760-20171124135256484-1613377423.png]: /images/20220529/a2d9f46657e64847a31413317ddcb9a2.png
[1263760-20171124135324421-227121977.png]: /images/20220529/5f912273a5c942a383f971538128f2dd.png
[1263760-20171124142020687-349811375.png]: /images/20220529/26814261dbf6400880a8a1870d93477a.png
[--ssl-ca]: https://dev.mysql.com/doc/refman/5.6/en/encrypted-connection-options.html#option_general_ssl-ca
[--ssl-cert]: https://dev.mysql.com/doc/refman/5.6/en/encrypted-connection-options.html#option_general_ssl-cert
[--ssl-key]: https://dev.mysql.com/doc/refman/5.6/en/encrypted-connection-options.html#option_general_ssl-key
[1263760-20171124164135203-1746601647.png]: /images/20220529/2a3dccb64aca429daa94538726370188.png