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
--------------
#############################################关闭SSL#####################################
1.在配置参数文件加入如下项目：
[mysqld]
skip_ssl
2.重启数据库
3.登录查看
mysql> show variables like '%ssl%';
+-------------------------------------+----------+
| Variable_name | Value |
+-------------------------------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| performance_schema_show_processlist | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
+-------------------------------------+----------+
10 rows in set (0.01 sec)
