MySQL OCP5.7之启用SSL以实现安全连接

2021-07-01

MySQL OCP5.7之启用SSL以实现安全连接

查看服务器是否开启SSL:
root@localhost[(none)]>show variables like 'have_ssl'
-> ;
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_ssl | DISABLED | #表示没有开启SSL
+---------------+----------+
1 row in set (0.00 sec)

通过mysql_ssl_rsa_setup生成SSL相关证书：
[root@node232 bin]#cd /usr/local/mysql/bin
[root@node232 bin]# ./mysql_ssl_rsa_setup
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'
-----

[root@node232 data]# pwd
/usr/local/mysql/data
运行命令后在数据目录生成以下文件：
-rw------- 1 root root 1679 6月 30 02:00 ca-key.pem #CA私钥
-rw-r--r-- 1 root root 1074 6月 30 02:00 ca.pem #自签的CA证书，客户端连接也需要提供
-rw------- 1 root root 1675 6月 30 02:00 server-key.pem #服务器端私钥文件
-rw-r--r-- 1 root root 1078 6月 30 02:00 server-cert.pem #服务器端证书文件
-rw------- 1 root root 1679 6月 30 02:00 client-key.pem #客户端连接服务器端需要提供的私钥文件
-rw-r--r-- 1 root root 1078 6月 30 02:00 client-cert.pem #客户端连接服务器端需要提供的证书文件
-rw------- 1 root root 1679 6月 30 02:00 private_key.pem #私钥/公钥对的私有成员
-rw-r--r-- 1 root root 451 6月 30 02:00 public_key.pem #私钥/公钥对的共有成员

[root@node232 data]# chown -R mysql.mysql ./*.pem

[root@node232 data]# service mysqld restart

root@localhost[(none)]>show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES | #说明已开启了SSL
+---------------+-------+
1 row in set (0.01 sec)

在配置文件中添加SSL相关的文件：
vim /etc/my.cnf
[mysqld]
ssl-ca=/usr/local/mysql/data/ca.pem
ssl-cert=/usr/local/mysql/data/client-cert.pem
ssl-key=/usr/local/mysql/data/client-key.pem

[mysql]
ssl-ca=/usr/local/mysql/data/ca.pem
ssl-cert=/usr/local/mysql/data/client-cert.pem
ssl-key=/usr/local/mysql/data/client-key.pem

通过SSL连接后，查看数据库的状态：
root@localhost[(none)]>status
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Uptime: 1 min 35 sec

Threads: 1 Questions: 9 Slow queries: 0 Opens: 109 Flush tables: 1 Open tables: 102 Queries per second avg: 0.094
--------------

开启另一个终端，重启数据库并手动关闭SSL:
[root@node232 ~]# service mysqld restart --ssl=0
Shutting down MySQL.... SUCCESS!
Starting MySQL.. SUCCESS!

[root@node232 ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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.

root@localhost[(none)]>status
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 4
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Uptime: 26 sec

Threads: 1 Questions: 7 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.269
--------------

root@localhost[(none)]>show variables like '%have_ssl%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_ssl | DISABLED | #说明已关闭了SSL
+---------------+----------+
1 row in set (0.01 sec)

重启数据库，以默认的方式启动:
mysql 客户端手动禁用SSL，登录系统
[root@node232 ~]# mysql -uroot -p'Rscpass123_456' --ssl-mode=DISABLED
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: no verification of server certificate will be done. Use --ssl-mode=VERIFY_CA or VERIFY_IDENTITY.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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.

root@localhost[(none)]>\s
--------------
mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 5
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Uptime: 54 sec

Threads: 1 Questions: 19 Slow queries: 0 Opens: 108 Flush tables: 1 Open tables: 101 Queries per second avg: 0.351
--------------

root@localhost[(none)]>show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.00 sec)

总结:虽然mysql数据库支持SSL,但用户通过--ssl-mode=disabled的方式登录，禁用了SSL

通过TCP/IP的方式登录数据库：
[root@node200 ~]# mysql -uroot -p'Rscpass123_456' -h 172.16.1.232
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.18-log 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.

(root@MySQL)[(none)]> \s
--------------
mysql Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id: 7
Current database:
Current user: root@172.16.1.200
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 172.16.1.232 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 4 min 59 sec

Threads: 2 Questions: 30 Slow queries: 0 Opens: 110 Flush tables: 1 Open tables: 103 Queries per second avg: 0.100
--------------

(root@MySQL)[(none)]> show variables like 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl | YES |
+---------------+-------+
1 row in set (0.01 sec)

以上表示：通过网络登录数据库，已使用的SSL加密

查看TLS的版本：
(root@MySQL)[(none)]> show global variables like 'tls_version';
+---------------+---------------+
| Variable_name | Value |
+---------------+---------------+
| tls_version | TLSv1,TLSv1.1 |
+---------------+---------------+
1 row in set (0.00 sec)

查看当前连接使用的版本：
(root@MySQL)[(none)]> show session status like 'Ssl_version';
+---------------+---------+
| Variable_name | Value |
+---------------+---------+
| Ssl_version | TLSv1.1 |
+---------------+---------+
1 row in set (0.00 sec)

查看SSL密码的值:
(root@MySQL)[(none)]> show global variables like 'ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| ssl_cipher | |
+---------------+-------+
1 row in set (0.00 sec)
结果是空白的，这意味着密码选择不受限制 mysql使用客户端和服务器的最强大的密码

查看正在使用的当前密码和当前会话允许的密码列表
(root@MySQL)[(none)]> show session status like 'Ssl_cipher%' \G;
*************************** 1. row ***************************
Variable_name: Ssl_cipher
Value: DHE-RSA-AES256-SHA
*************************** 2. row ***************************
Variable_name: Ssl_cipher_list
Value: DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES128-RMD:DES-CBC3-RMD:DHE-RSA-AES256-RMD:DHE-RSA-AES128-RMD:DHE-RSA-DES-CBC3-RMD:AES256-SHA:RC4-SHA:RC4-MD5:DES-CBC3-SHA:DES-CBC-SHA:EDH-RSA-DES-CBC3-SHA:EDH-RSA-DES-CBC-SHA:AES128-SHA:AES256-RMD
2 rows in set (0.00 sec)
结果表明，该DHE-RSA-AES256-SHA是当前的密码并显示允许使用该会话的密码列表

查看ca的数字证书的文件名:
(root@MySQL)[(none)]> show global variables like 'ssl_ca';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| ssl_ca | /usr/local/mysql/data/ca.pem |
+---------------+------------------------------+
1 row in set (0.01 sec)

查看服务器数字证书的文件名：
(root@MySQL)[(none)]> show global variables like 'ssl_cert';
+---------------+---------------------------------------+
| Variable_name | Value |
+---------------+---------------------------------------+
| ssl_cert | /usr/local/mysql/data/client-cert.pem |
+---------------+---------------------------------------+
1 row in set (0.00 sec)

查看服务器私钥文件名：
(root@MySQL)[(none)]> show global variables like 'ssl_key';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| ssl_key | /usr/local/mysql/data/client-key.pem |
+---------------+--------------------------------------+
1 row in set (0.02 sec)