【MySQL】启用SSL连接
环境:MySLQ5.7 CentOS7
1:利用MySQL5.7自带工具生成ssl证书
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
# 在Linux命令行下执行下面命令 # 注:--datadir /data/db/mysql/3306 为存放生成证书的位置 [root@mss ~]# mysql_ssl_rsa_setup --datadir /data/db/mysql/3306 [root@mss ~]# cd /data/db/mysql/3306/ [root@mss 3306]# [root@mss 3306]# ll |grep pem -rw------- 1 root root 1679 Sep 2 09:25 ca-key.pem -rw-r--r-- 1 root root 1074 Sep 2 09:25 ca.pem -rw-r--r-- 1 root root 1078 Sep 2 09:25 client-cert.pem -rw------- 1 root root 1679 Sep 2 09:25 client-key.pem -rw------- 1 root root 1679 Sep 2 09:25 private_key.pem -rw-r--r-- 1 root root 451 Sep 2 09:25 public_key.pem -rw-r--r-- 1 root root 1078 Sep 2 09:25 server-cert.pem -rw------- 1 root root 1675 Sep 2 09:25 server-key.pem [root@mss 3306]# ## 修改属主 [root@mss 3306]# chown mysql:mysql *.pem [root@mss 3306]# [root@mss 3306]# ll |grep pem -rw------- 1 mysql mysql 1679 Sep 2 09:25 ca-key.pem -rw-r--r-- 1 mysql mysql 1074 Sep 2 09:25 ca.pem -rw-r--r-- 1 mysql mysql 1078 Sep 2 09:25 client-cert.pem -rw------- 1 mysql mysql 1679 Sep 2 09:25 client-key.pem -rw------- 1 mysql mysql 1679 Sep 2 09:25 private_key.pem -rw-r--r-- 1 mysql mysql 451 Sep 2 09:25 public_key.pem -rw-r--r-- 1 mysql mysql 1078 Sep 2 09:25 server-cert.pem -rw------- 1 mysql mysql 1675 Sep 2 09:25 server-key.pem [root@mss 3306]# |
2:修改/etc/my.cnf配置文件
1 2 3 4 5 6 7 |
[root@mss ~]# vi /etc/my.cnf …… [mysqld] ssl-ca=/data/db/mysql/3306/ca.pem ssl-cert=/data/db/mysql/3306/server-cert.pem ssl-key=/data/db/mysql/3306/server-key.pem …… |
3:重启MySQL Server
1 2 3 4 |
[root@mss ~]# service mysql restart Shutting down MySQL... SUCCESS! Starting MySQL....... SUCCESS! [root@mss ~]# |
4:此时依旧可以通过TCP/IP协议登录数据
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
# 登录命令行,确认ssl相关参数已配置成功(注:未启用ssl时,have_openssl、have_ssl为DISABLED,其他ssl相关参数均为空) mysql> show variables like '%ssl%'; +---------------+-------------------------------------+ | Variable_name | Value | +---------------+-------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /data/db/mysql/3306/ca.pem | | ssl_capath | | | ssl_cert | /data/db/mysql/3306/server-cert.pem | | ssl_cipher | | | ssl_crl | | | ssl_crlpath | | | ssl_key | /data/db/mysql/3306/server-key.pem | +---------------+-------------------------------------+ 9 rows in set (0.01 sec) mysql> |
5:创建测试用户
1 2 3 4 5 6 7 8 |
# 创建ssl登录用户 grant all privileges on *.* to 'xxf'@'%' identified by 'oracle' require ssl; # 修改之前的用户为ssl登录用户 alter user 'ttt'@'%' require ssl; # 将ssl用户改为TCP/IP登录用户 alter user 'ttt'@'%' require none; |
6:在服务器端利用ssl登录数据库
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
[root@mss ~]# mysql --ssl-ca=/data/db/mysql/3306/ca.pem --ssl-cert=/data/db/mysql/3306/client-cert.pem --ssl-key=/data/db/mysql/3306/client-key.pem -u ttt -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 49 Server version: 5.7.19-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. You are enforcing ssl conection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. mysql> |
7:如果客户端使用ssl登录远端数据库
1)将服务器端生成的ssl证书拷贝到客户端,存放在一个固定位置;
2)登录命令与服务器端登录相似,只需要加上一个参数 -h xxx.xxx.xxx