mysql 5.7--------ssl连接最佳实战
1. 背景
* 在生产环境下，安全总是无法忽视的问题，数据库安全则是重中之重，因为所有的数据都存放在数据库中
* 当使用非加密方式连接mysql数据库时，在网络中传输的所有信息都是明文的，可以被网络中所有人截取，敏感信息可能被泄露。在传送敏感信息（如密码）时，可以采用ssl连接的方式。
* 版本小于5.7.6时按照 mysql 5.6 ssl配置的方式进行。
2. mysql 连接方式
* socket连接
* tcp非ssl连接
* ssl安全连接
* ssl + 密码连接 [version > mysql 5.7.5]
* ssl + 密码 + 密钥连接
3. ssl 简介
* ssl指的是ssl/tls，其是一种为了在计算机网络进行安全通信的加密协议。假设用户的传输不是通过ssl的方式，那么其在网络中以明文的方式进行传输，而这给别有用心的人带来了可乘之机。所以，现在很多网站其实默认已经开启了ssl功能，比如facebook、twtter、youtube、淘宝等。
4. 环境 [ 关闭selinux ]
* system 环境
[root@mysql ~]# cat /etc/redhat-release
centos release 6.9 (final)
[root@mysql ~]# uname -r
2.6.32-696.3.2.el6.x86_64
[root@mysql ~]# getenforce
disabled
* mysql 环境 [ mysql 5.7安装前面篇章已做详细介绍 ]
have_openssl 与 have_ssl 值都为disabled表示ssl未开启
[root@mysql ~]# mysql -p'123'
mysql: [warning] using a password on the command line interface can be insecure.
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 6
server version: 5.7.18 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.
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18 |
+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'have%ssl%';
+---------------+----------+
| variable_name | value |
+---------------+----------+
| have_openssl | disabled |
| have_ssl | disabled |
+---------------+----------+
2 rows in set (0.02 sec)
mysql> show variables like 'port';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show variables like 'datadir';
+---------------+-------------------+
| variable_name | value |
+---------------+-------------------+
| datadir | /data/mysql_data/ |
+---------------+-------------------+
1 row in set (0.01 sec)
5. ssl配置
* 利用自带工具生成ssl相关文件
[root@mysql ~]# /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql_data
generating a 2048 bit rsa private key
..........................................................................+++
.....+++
writing new private key to 'ca-key.pem'
-----
generating a 2048 bit rsa private key
.......................................................................................................................................................................+++
...+++
writing new private key to 'server-key.pem'
-----
generating a 2048 bit rsa private key
.....................+++
...........................................+++
writing new private key to 'client-key.pem'
-----
* 查看生成的ssl文件
[root@mysql ~]# ls -l /data/mysql_data/*.pem
-rw------- 1 root root 1679 jun 24 20:54 /data/mysql_data/ca-key.pem
-rw-r--r-- 1 root root 1074 jun 24 20:54 /data/mysql_data/ca.pem
-rw-r--r-- 1 root root 1078 jun 24 20:54 /data/mysql_data/client-cert.pem
-rw------- 1 root root 1675 jun 24 20:54 /data/mysql_data/client-key.pem
-rw------- 1 root root 1675 jun 24 20:54 /data/mysql_data/private_key.pem
-rw-r--r-- 1 root root 451 jun 24 20:54 /data/mysql_data/public_key.pem
-rw-r--r-- 1 root root 1078 jun 24 20:54 /data/mysql_data/server-cert.pem
-rw------- 1 root root 1675 jun 24 20:54 /data/mysql_data/server-key.pem
* 重启 mysql 服务
[root@mysql ~]# /etc/init.d/mysqld restart
shutting down mysql.. success!
starting mysql. success!
* 连接mysql 查看ssl开启状态 have_openssl 与 have_ssl 值都为yes表示ssl开启成功
mysql> show variables like 'have%ssl%';
+---------------+-------+
| variable_name | value |
+---------------+-------+
| have_openssl | yes |
| have_ssl | yes |
+---------------+-------+
2 rows in set (0.03 sec)
6. ssl + 密码连接测试
* 创建用户并指定 ssl 连接 [ mysql 5.7后推荐使用create user 方式创建用户 ]
mysql> create user 'ssl_test'@'%' identified by '123' require ssl;
query ok, 0 rows affected (0.00 sec)
* 通过密码连接测试 [ 默认采用ssl连接，需要指定不使用ssl连接 ]
[root@mysql ~]# mysql -h 192.168.60.129 -ussl_test -p'123' --ssl=0
mysql: [warning] using a password on the command line interface can be insecure.
error 1045 (28000): access denied for user 'ssl_test'@'192.168.60.129' (using password: yes)
* 通过 ssl + 密码 连接测试 ssl: cipher in use is dhe-rsa-aes256-sha 表示通过ssl连接
[root@mysql ~]# mysql -h 192.168.60.129 -ussl_test -p'123' --ssl
mysql: [warning] using a password on the command line interface can be insecure.
warning: --ssl is deprecated and will be removed in a future version. use --ssl-mode instead.
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 12
server version: 5.7.18 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.
mysql> \s
--------------
mysql ver 14.14 distrib 5.7.18, for linux-glibc2.5 (x86_64) using editline wrapper
connection id: 12
current database:
current user: ssl_test@192.168.60.129
ssl: cipher in use is dhe-rsa-aes256-sha
current pager: stdout
using outfile: ''
using delimiter: ;
server version: 5.7.18 mysql community server (gpl)
protocol version: 10
connection: 192.168.60.129 via tcp/ip
server characterset: latin1
db characterset: latin1
client characterset: utf8
conn. characterset: utf8
tcp port: 3306
uptime: 7 min 34 sec
threads: 1 questions: 29 slow queries: 0 opens: 112 flush tables: 1 open tables: 105 queries per second avg: 0.063
--------------
7. ssl + 密码 + 密钥连接
* 创建用户并指定 x509 [ ssl+密钥 ] 连接 [ mysql 5.7后推荐使用create user 方式创建用户 ]
mysql> create user 'x509_test'@'%' identified by '123' require x509;
query ok, 0 rows affected (0.00 sec)
* 通过密码连接测试
[root@mysql ~]# mysql -h 192.168.60.129 -ux509_test -p'123' --ssl=0
mysql: [warning] using a password on the command line interface can be insecure.
error 1045 (28000): access denied for user 'x509_test'@'192.168.60.129' (using password: yes)
* 通过 ssl +密码 连接测试
[root@mysql ~]# mysql -h 192.168.60.129 -ux509_test -p'123' --ssl
mysql: [warning] using a password on the command line interface can be insecure.
error 1045 (28000): access denied for user 'x509_test'@'192.168.60.129' (using password: yes)
* 通过 ssl + 密码＋密钥连接测试 ssl: cipher in use is dhe-rsa-aes256-sha 表示通过ssl连接
[root@mysql ~]# mysql -h 192.168.60.129 -ux509_test -p'123' --ssl-cert=/data/mysql_data/client-cert.pem --ssl-key=/data/mysql_data/client-key.pem
mysql: [warning] using a password on the command line interface can be insecure.
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 21
server version: 5.7.18 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.
mysql> \s
--------------
mysql ver 14.14 distrib 5.7.18, for linux-glibc2.5 (x86_64) using editline wrapper
connection id: 21
current database:
current user: x509_test@192.168.60.129
ssl: cipher in use is dhe-rsa-aes256-sha
current pager: stdout
using outfile: ''
using delimiter: ;
server version: 5.7.18 mysql community server (gpl)
protocol version: 10
connection: 192.168.60.129 via tcp/ip
server characterset: latin1
db characterset: latin1
client characterset: utf8
conn. characterset: utf8
tcp port: 3306
uptime: 18 min 27 sec
threads: 1 questions: 40 slow queries: 0 opens: 118 flush tables: 1 open tables: 111 queries per second avg: 0.036
--------------
以上就是ssl连接的实例教程的详细内容。