mysql with ssl 5.7,mysql 5.7使用ssl连接

1、使用脚本工具生成密钥文件

-rw-r--r--. 1 root root 451 11月 28 12:26 public_key.pem

-rw-------. 1 root root 1.7K 11月 28 12:26 private_key.pem

-rw-r--r--. 1 root root 1.1K 11月 28 12:26 client-cert.pem

-rw-------. 1 root root 1.7K 11月 28 12:26 client-key.pem

-rw-r--r--. 1 root root 1.1K 11月 28 12:26 server-cert.pem

-rw-------. 1 root root 1.7K 11月 28 12:26 server-key.pem

-rw-r--r--. 1 root root 1.1K 11月 28 12:26 ca.pem

-rw-------. 1 root root 1.7K 11月 28 12:26 ca-key.pem

2、启动数据库，看是否开启了ssl 验证

mysql> show variables like '%ssl%';

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

| Variable_name | Value |

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

| have_openssl | DISABLED |

| have_ssl | DISABLED |

| ssl_ca | ca.pem |

| ssl_capath | |

| ssl_cert | server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | server-key.pem |

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

前面两项是 disable 的，说明没有开启，查看 日志文件，发下以下报错：

[ERROR] SSL error: Unable to get private key from 'server-key.pem'

原来是该文件权限为 600 ，将权限修改为644 ，重启数据库，发现开了ssl 验证了：

mysql> show variables like '%ssl%';

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

| Variable_name | Value |

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

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | ca.pem |

| ssl_capath | |

| ssl_cert | server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | server-key.pem |

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

3、ssl 配置 ，修改my.cnf 文件

[mysql]

ssl-ca=/mysqldata/mysql_data/ca.pem

ssl-cert=/mysqldata/mysql_data/client-cert.pem

ssl-key=/mysqldata/mysql_data/client-key.pem

[mysqld]

ssl-ca=/mysqldata/mysql_data/ca.pem

ssl-cert=/mysqldata/mysql_data/client-cert.pem

ssl-key=/mysqldata/mysql_data/client-key.pem

创建用户：

grant select on *.* to 'dba'@'%' identified by 'dba';

从客户端连接：

[root@localhost ~]# mysql -udba -p -h 10.10.19.201

Enter password:

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status

--------------

mysql Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 118

Current database:

Current user: dba@10.10.19.202

SSL: Cipher in use is DHE-RSA-AES256-SHA

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.7.16-log MySQL Community Server (GPL)

ssl 不为空，说明使用了 ssl 验证，

如果强制用户登录时不使用ssl 验证，查看用户状态：

[root@localhost ~]# mysql -udba -p -h 10.10.19.201 --ssl=0

WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 193

Server version: 5.7.16-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> status

--------------

mysql Ver 14.14 Distrib 5.7.16, for linux-glibc2.5 (x86_64) using EditLine wrapper

Connection id: 193

Current database:

Current user: dba@10.10.19.202

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

ssl 项的值显示 “not in use”

如果修改用户必须使用ssl 连接，可以使用下面命令：

mysql> ALTER USER 'dba'@'%' REQUIRE SSL;

Query OK, 0 rows affected (0.06 sec)

mysql>

这时还使用 --ssl = 0 的模式登录就会被拒绝：

[root@localhost ~]# mysql -udba -p -h 10.10.19.201 --ssl=0

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 'dba'@'10.10.19.202' (using password: YES)