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)


分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS