mysql5.7启用ssl连接

环境:
OS:Centos 7
Mysql:5.7

 

1.安装mysql
安装步骤省略,mysql5.7默认安装都已经安装好了ssl的
证书在数据目录下

[root@localhost data]# ls -1
auto.cnf
ca-key.pem
ca.pem
client-cert.pem
client-key.pem
db_hxl
db_hxl01
db_test
ib_buffer_pool
ibdata1
ibtmp1
localhost.localdomain.pid
mysql
performance_schema
private_key.pem
public_key.pem
sbtest
server-cert.pem
server-key.pem
sys
test
xtrabackup_binlog_pos_innodb
xtrabackup_info

 

2.在配置文件中[mysqld]栏目加入如下证书配置

ssl-ca=/opt/mysql5730/data/ca.pem
ssl-cert=/opt/mysql5730/data/client-cert.pem
ssl-key=/opt/mysql5730/data/client-key.pem

 

 

或者在[mysqld]和[client]栏目下都做配置

 

[client]
ssl-ca=/opt/mysql5730/data/ca.pem
ssl-cert=/opt/mysql5730/data/client-cert.pem
ssl-key=/opt/mysql5730/data/client-key.pem


[mysqld]
ssl-ca=/opt/mysql5730/data/ca.pem
ssl-cert=/opt/mysql5730/data/server-cert.pem
ssl-key=/opt/mysql5730/data/server-key.pem

 

 

 

 

 

3.启动数据库登陆创建相应的用户
/opt/mysql5730/bin/mysql -h localhost -uroot -pmysql

mysql>grant all privileges on *.* to 'ssltest'@'%' identified by 'mysql' require ssl;
mysql>alter user 'ssltest'@'%' require ssl;

若是不想启用ssl需要修改下用户,这样只有密码登陆就可以了
mysql>alter user 'ssltest'@'%' require none;

 

3.连接
客户端连接(另外的机器,需要将3个证书文件拷贝的相应的机器)
/opt/mysql57/bin/mysql --host=192.168.1.118 --ssl-ca=/soft/ssl118/ca.pem --ssl-cert=/soft/ssl118/client-cert.pem --ssl-key=/soft/ssl118/client-key.pem -ussltest -pmysql

navicate连接,好像需要15版本以上
Navicat Premium 12 可以连接

 

不指定--ssl-ca参数也可以连接的

/opt/mysql57/bin/mysql --host=192.168.1.118 --ssl-cert=/soft/ssl118/client-cert.pem --ssl-key=/soft/ssl118/client-key.pem -ussltest -pmysql

 

4.说明

查看用户是否启用了ssl

 

mysql> select user,host,ssl_type,ssl_cipher from mysql.user;
+----------------+--------------+----------+------------+
| user           | host         | ssl_type | ssl_cipher |
+----------------+--------------+----------+------------+
| root           | localhost    |          |            |
| arkcontrol     | 192.168.1.85 |          |            |
| arkcontrol     | 127.0.0.1    |          |            |
| arkcontrol     | localhost    |          |            |
| repl           | %            |          |            |
| mysql.session  | localhost    |          |            |
| mysql.sys      | localhost    |          |            |
| ssltest        | %            | ANY      |            |
| monitor        | %            |          |            |
| ubackupmonitor | %            |          |            |
+----------------+--------------+----------+------------+
10 rows in set (0.00 sec)

 

 查看ssl配置情况

 

mysql> show variables like '%ssl%';           
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| have_openssl  | YES                                 |
| have_ssl      | YES                                 |
| ssl_ca        | /opt/mysql5729/data/ca.pem          |
| ssl_capath    |                                     |
| ssl_cert      | /opt/mysql5729/data/server-cert.pem |
| ssl_cipher    |                                     |
| ssl_crl       |                                     |
| ssl_crlpath   |                                     |
| ssl_key       | /opt/mysql5729/data/server-key.pem  |
+---------------+-------------------------------------+
9 rows in set (0.01 sec)

 

 登陆后查看使用情况

mysql> status;
--------------
/opt/mysql5729/bin/mysql  Ver 14.14 Distrib 5.7.29, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          5
Current database:
Current user:           ssltest@192.168.1.118
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.29-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             192.168.1.136 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               13306
Uptime:                 2 min 38 sec

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