mysql5.6配置ssl连接
环境:
OS:Centos 7
Mysql:5.6.40
1.生成一个 CA 私钥
[root@localhost tmp]# mkdir /tmp/ca
[root@localhost tmp]# cd /tmp/ca
[root@localhost ca]# openssl genrsa 2048 > ca-key.pem
2.通过 CA 私钥生成数字证书
[root@localhost ca]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
一路回车
3.创建 MySQL 服务器 私钥和请求证书
[root@localhost ca]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
Generating a 2048 bit RSA private key
.................+++
........................................+++
writing new private key to 'server-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
一路回车
4.将生成的私钥转换为 RSA 私钥文件格式
[root@localhost ca]# openssl rsa -in server-key.pem -out server-key.pem
5.用CA 证书来生成一个服务器端的数字证书
[root@localhost ca]# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Getting CA Private Key
6.创建客户端的 RSA 私钥和数字证书
[root@localhost ca]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key
..........+++
.......................................................................+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:\
一路回车
7.将生成的私钥转换为 RSA 私钥文件格式
[root@localhost ca]# openssl rsa -in client-key.pem -out client-key.pem
writing RSA key
8.用CA 证书来生成一个客户端的数字证书
[root@localhost ca]# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Getting CA Private Key
9.查看生成的文件
[root@localhost ca]# ls -al
total 32
drwxrwxr-x. 2 root root 172 Apr 25 16:50 .
drwxrwxrwt. 9 root root 126 Apr 25 16:12 ..
-rw-rw-r--. 1 root root 1679 Apr 25 16:31 ca-key.pem
-rw-rw-r--. 1 root root 1220 Apr 25 16:32 ca.pem
-rw-rw-r--. 1 root root 1090 Apr 25 16:50 client-cert.pem
-rw-rw-r--. 1 root root 1679 Apr 25 16:50 client-key.pem
-rw-rw-r--. 1 root root 952 Apr 25 16:49 client-req.pem
-rw-rw-r--. 1 root root 1090 Apr 25 16:49 server-cert.pem
-rw-rw-r--. 1 root root 1679 Apr 25 16:48 server-key.pem
-rw-rw-r--. 1 root root 952 Apr 25 16:46 server-req.pem
10.将文件拷贝到数据目录
[root@localhost ca]# cp /tmp/ca/* /opt/mysql5640/data/
11.修改数据库配置文件
[mysqld]
ssl-ca=/opt/mysql5640/data/ca.pem
ssl-cert=/opt/mysql5640/data/server-cert.pem
ssl-key=/opt/mysql5640/data/server-key.pem
12.修改文件权限
我这里将整个目录权限修改了
[root@localhost data]# chown -R mysql:mysql /opt/mysql5640/data/
13.重启动数据库
/opt/mysql5640/bin/mysqladmin -h localhost -uroot -pmysql -P23306 --socket=/opt/mysql5640/data/mysql.sock shutdown
/opt/mysql5640/bin/mysqld_safe --defaults-file=/opt/mysql5640/conf/my.cnf --user=mysql &
14.登陆查看
/opt/mysql5640/bin/mysql -h localhost -uroot -pmysql -P23306 --socket=/opt/mysql5640/data/mysql.sock
mysql> show variables like '%ssl%';
+---------------+-------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /opt/mysql5640/data/ca.pem |
| ssl_capath | |
| ssl_cert | /opt/mysql5640/data/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | /opt/mysql5640/data/server-key.pem |
+---------------+-------------------------------------+
9 rows in set (0.00 sec)
15.创建用户
mysql>grant all privileges on *.* to 'ssltest'@'%' identified by 'mysql' require 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 | % | | |
| root | % | | |
| ssltest | % | ANY | |
+------------+--------------+----------+------------+
7 rows in set (0.00 sec)
16.将客服端正式拷贝到需要连接的机器上
scp /opt/mysql5640/data/client-cert.pem root@192.168.1.118:/tmp/56ssl/
scp /opt/mysql5640/data/client-key.pem root@192.168.1.118:/tmp/56ssl/
17.客户端通过ssl连接
/opt/mysql5729/bin/mysql --host=192.168.1.85 -P23306 --ssl-cert=/tmp/56ssl/client-cert.pem --ssl-key=/tmp/56ssl/client-key.pem -ussltest -pmysql
mysql> status;
--------------
/opt/mysql5729/bin/mysql Ver 14.14 Distrib 5.7.29, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 3
Current database:
Current user: ssltest@192.168.1.118
SSL: Cipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.6.40-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 192.168.1.85 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 23306
Uptime: 6 min 22 sec
Threads: 3 Questions: 10 Slow queries: 0 Opens: 70 Flush tables: 1 Open tables: 63 Queries per second avg: 0.026
--------------
