当前位置:   article > 正文

mysql的ssl路径_Mysql 中的SSL 连接

mysql配置文件ssl在哪

Mysql 中的SSL 连接

以下来自网络参考和自己测试整理,没有查找相关资料。若有错误之处,欢迎指正。

当前的Mysql 客户端版本基本都不太能支持 caching_sha2_password 认证,使用Mysql 8.0 的话,建议添加参数:

default-authentication-plugin=mysql_native_password

否则可能导致客户端连接失败。以下的案例也是基于此。客户端的不支持会导致不能反映真实结果。

当前数据库中的用户

root@(none):53: >select host,user,ssl_type,ssl_cipher,x509_issuer,x509_subject,plugin from mysql.user;

+--------------+------------------+----------+------------+-------------+--------------+-----------------------+

| host | user | ssl_type | ssl_cipher | x509_issuer | x509_subject | plugin |

+--------------+------------------+----------+------------+-------------+--------------+-----------------------+

| % | rep | | | | | mysql_native_password |

| % | root | | | | | mysql_native_password |

| % | test | | | | | mysql_native_password |

| % | test1 | | | | | caching_sha2_password |

| 192.168.20.% | cat | X509 | | | | mysql_native_password |

| localhost | mysql.infoschema | | | | | caching_sha2_password |

| localhost | mysql.session | | | | | caching_sha2_password |

| localhost | mysql.sys | | | | | caching_sha2_password |

| localhost | root | | | | | caching_sha2_password |

+--------------+------------------+----------+------------+-------------+--------------+-----------------------+

10 rows in set (0.00 sec)

require_secure_transport = OFF 模式

此为默认设置,该模式下用户可以不通过ssl加密连接到数据库

-- 不通过SSL连接方式

mysql -h mysql1 -utest -ptest --ssl-mode=DISABLED

SSL: Not in use

-- 通过SSL连接方式

mysql -h mysql1 -utest -ptest

mysql -h mysql1 -utest -ptest --ssl-mode=PREFERRED

mysql -h mysql1 -utest1 -ptest1 --ssl-mode=PREFERRED

mysql -h mysql1 -utest1 -ptest1 --ssl-mode=REQUIRED

SSL: Cipher in use is DHE-RSA-AES128-GCM-SHA256

navicate 只配置常规选项卡即可连接

70f5c1ab4e7bff3fb799036df55d19b0.png

连接之后为非加密模式

show status like 'ssl_cipher';

6ba2d6fea9704cfa5f5a108ce9dbff67.png

但我们同样可以使用加密连接

27a294b7f783d05118e77dae555c3d95.png

4d380293724764d72877c24882a961e4.png

require_secure_transport = ON 模式下

强制要求配置ssl

如果不使用SSL 连接会报错,MySQL 命令行

-- 不使用SSL

[root@mysql2 ~]# mysql -h mysql1 -utest -ptest --ssl-mode=disabled

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.

-- 使用SSL

[root@mysql2 ~]# mysql -h mysql1 -utest -ptest

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 17

Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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.

mysql> show status like 'ssl_cipher';

+---------------+---------------------------+

| Variable_name | Value |

+---------------+---------------------------+

| Ssl_cipher | DHE-RSA-AES128-GCM-SHA256 |

+---------------+---------------------------+

1 row in set (0.01 sec)

SSL-MODE 有如下5中选项

'DISABLEDD'--不使用SSL加密

'PREFERRED','REQUIRED'--使用SSL 加密,加密算法没差别

'VERIFY_CA','VERIFY_IDENTITY' --需附加--ssl-ca 等选项

91956597d74c8b6377500c1dccd79192.png

PEM 的使用

官方文档会推荐我们初始化Mysql 后执行如下命令

mysql_ssl_rsa_setup --datadir=datadir路径,该命令会在数据目录下生成如下这些文件。

那么该如何使用这些文件呢?

[root@mysql1 mydata1]# ll /u01/mydata1/*.pem

-rw-------. 1 mysql mysql 1680 Jun 4 03:12 /u01/mydata1/ca-key.pem

-rw-r--r--. 1 mysql mysql 1112 Jun 4 03:12 /u01/mydata1/ca.pem

-rw-r--r--. 1 mysql mysql 1112 Jun 4 03:12 /u01/mydata1/client-cert.pem

-rw-------. 1 mysql mysql 1680 Jun 4 03:12 /u01/mydata1/client-key.pem

-rw-------. 1 mysql mysql 1676 Jun 4 03:12 /u01/mydata1/private_key.pem

-rw-r--r--. 1 mysql mysql 452 Jun 4 03:12 /u01/mydata1/public_key.pem

-rw-r--r--. 1 mysql mysql 1112 Jun 4 03:12 /u01/mydata1/server-cert.pem

-rw-------. 1 mysql mysql 1680 Jun 4 03:12 /u01/mydata1/server-key.pem

创建用户

root@(none):39: >create user cat@'192.168.20.%' identified with mysql_native_password by 'cat' require ssl; -- 这时要求该用户必须使用SSL,即使参数require_secure_transport = OFF

root@(none):39: >grant all on *.* to cat@'192.168.20.%';

root@(none):39: >alter user cat@'192.168.20.%' require x509; -- 这里就要求使用pem 文件了

root@(none):39: >select host,user,ssl_type,ssl_cipher,x509_issuer,x509_subject,plugin from mysql.user where user='cat' \G

*************************** 1. row ***************************

host: 192.168.20.%

user: cat

ssl_type: X509

ssl_cipher:

x509_issuer:

x509_subject:

plugin: mysql_native_password

1 row in set (0.00 sec)

测试 SSL 常规连接,将会失败

[root@mysql2 ~]# mysql -h mysql1 -ucat -pcat

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'cat'@'192.168.20.82' (using password: YES)

[root@mysql2 ~]# mysql -h mysql1 -ucat -pcat --ssl-mode=PREFERRED

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 1045 (28000): Access denied for user 'cat'@'192.168.20.82' (using password: YES)

复制pem文件

再想成功连接就需要使用到PEM 文件了,将server端pem 文件copy 到客户端

#mysql1

[root@mysql1 mydata1]# scp client-cert.pem mysql2:/home/mysql

client-cert.pem 100% 1112 873.8KB/s 00:00

[root@mysql1 mydata1]# scp client-key.pem mysql2:/home/mysql

client-key.pem 100% 1680 169.9KB/s 00:00

#mysql2

[root@mysql2 ~]# ll /home/mysql

total 8

-rw-r--r-- 1 root root 1112 Jul 14 09:43 client-cert.pem

-rw------- 1 root root 1680 Jul 14 09:43 client-key.pem

使用pem,成功连接

[root@mysql2 ~]# mysql -h mysql1 -ucat -pcat --ssl-cert=/home/mysql/client-cert.pem --ssl-key=/home/mysql/client-key.pem

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 50

Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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.

mysql>

--ssl-ca 参数也可以使用,同样需要将server 段的ca.pem 复制到客户端。如

[root@mysql2 ~]# mysql -h mysql1 -ucat -pcat --ssl-ca=/home/mysql/ca.pem --ssl-cert=/home/mysql/client-cert.pem --ssl-key=/home/mysql/client-key.pem

工具连接

需要下载server端的pem 文件到自己电脑,为连接工具指定pem文件位置方可连接到数据库。

bac10b1dce6cf7f9d15b705478085b43.png

df55ea8c1057229da143be7effa785d7.png

添加用户需要重新copy pem 文件吗?

-- 添加用户

root@(none):09: >create user dog@'192.168.20.%' identified with mysql_native_password by 'dog' require x509; -- 使用x509 可以不指定require ssl,但仍会强制要求SSL

Query OK, 0 rows affected (0.00 sec)

root@(none):09: >grant all on *.* to dog@'192.168.20.%';

Query OK, 0 rows affected (0.01 sec)

添加新用户,不用重新copy

-- 成功连接

[root@mysql2 ~]# mysql -h mysql1 -udog -pdog --ssl-cert=/home/mysql/client-cert.pem --ssl-key=/home/mysql/client-key.pem

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 13

Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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.

mysql>

7bf8d81aca4e607a6af5fd0d393f250c.png

db652e12e9b44b23bcbbb2d85451367f.png

说明pem 文件在安装好数据库后是固定不变的。copy 到客户端后可以永久使用,不受数据库变更的影响。

服务端的pem 文件和ssl连接有关联吗?

#将所有的pem文件移动走

[root@mysql1 mydata1]# mkdir pemdefault

[root@mysql1 mydata1]# mv *.pem pemdefault/

[root@mysql1 mydata1]# ll *.pem

ls: cannot access *.pem: No such file or directory

# 测试客户端连接

[root@mysql2 ~]# mysql -h mysql1 -udog -pdog --ssl-cert=/home/mysql/client-cert.pem --ssl-key=/home/mysql/client-key.pem

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 35

Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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.

mysql>

说明,服务端的*.pem文件被删除并不影响客户端的SSL 连接.

但是,如果重启数据库, 服务端会重新生成新的pem文件。

[root@mysql1 mydata1]# ll *.pem

-rw------- 1 mysql mysql 1680 Jul 14 10:24 ca-key.pem

-rw-r--r-- 1 mysql mysql 1112 Jul 14 10:24 ca.pem

-rw-r--r-- 1 mysql mysql 1112 Jul 14 10:24 client-cert.pem

-rw------- 1 mysql mysql 1680 Jul 14 10:24 client-key.pem

-rw------- 1 mysql mysql 1676 Jul 14 10:24 private_key.pem

-rw-r--r-- 1 mysql mysql 452 Jul 14 10:24 public_key.pem

-rw-r--r-- 1 mysql mysql 1112 Jul 14 10:24 server-cert.pem

-rw------- 1 mysql mysql 1680 Jul 14 10:24 server-key.pem

这时再远程连接就报错了

[root@mysql2 ~]# mysql -h mysql1 -udog -pdog --ssl-cert=/home/mysql/client-cert.pem --ssl-key=/home/mysql/client-key.pem

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2026 (HY000): SSL connection error: error:1409441B:SSL routines:ssl3_read_bytes:tlsv1 alert decrypt error

重新copy pem文件到客户端, 再次连接测试

#copy pem文件

[root@mysql1 mydata1]# scp client-cert.pem mysql2:/home/mysql

client-cert.pem 100% 1112 1.4MB/s 00:00

[root@mysql1 mydata1]# scp client-key.pem mysql2:/home/mysql

client-key.pem

#连接恢复

[root@mysql2 ~]# mysql -h mysql1 -udog -pdog --ssl-cert=/home/mysql/client-cert.pem --ssl-key=/home/mysql/client-key.pem

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 19

Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, 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.

mysql>

所以综上,虽然pem ssl 远程连接不需要通过服务端的pem 文件匹配验证,但仍然不能删除服务端pem文件,因为删除后重启数据库会重新生成新的pem 文件,将导致远程连接失败,需要重新copy。

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/Li_阴宅/article/detail/854862
推荐阅读
相关标签
  

闽ICP备14008679号