奈何项目系统使用的MYSQL使用的是5.6版本,只能针对MYSQL5.6这个版本的SSL使用做一下记录。
1、检查当前SSL状态
SHOW VARIABLES LIKE '%SSL%';
Output
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
+---------------+----------+
9 rows in set (0.01 sec)
have_openssl和have_ssl字段显示DISABLE,表示当前MYSQL拥有SSL的功能,但是SSL功能未启动。
2、生成SSL证书和密钥
要使用到MYSQL的SSL链接,我们首先需要生成相应的证书和密钥文件。
MYSQL5.7及更高版本用 mysql_ssl_rsa_setup 自动生成秘匙不同,5.6需要通过openssl命令来生成秘匙。
2.1、创建一个certs目录用于存放证书和密钥
cd /data/mysql
mkdir certs
cd certs
2.2、首先生成所需key
CA
openssl genrsa 2048 > ca_key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
小提示:CA的Country Name 要与server/client的Country Name不同,否则Verify这步会出现错误,出现类似“error 18 at 0 depth lookup:self signed certificate”的错误
SERVER
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
CLIENT
openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
VERIFY
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
如果‘server-cert.pem’和’client-cert.pem’提示OK,则表示证书生成成功
3、配置MYSQL的my.cnf文件
[mysqld]
ssl-ca=/data/mysql/certs/ca.pem
ssl-cert=/data/mysql/certs/server-cert.pem
ssl-key=/data/mysql/certs/server-key.pem
[client]
ssl-ca=/data/mysql/certs/ca.pem
ssl-cert=/data/mysql/certs/client-cert.pem
ssl-key=/data/mysql/certs/client-key.pem
4、创建一个用户,并设置其使用SSL链接
mysql> CREATE USER 'ssluser'@'%' identified by '123';
mysql> GRANT USAGE ON *.* TO 'ssluser'@'%' identified by '123' require ssl;
mysql> FLUSH PRIVILEGES;
5、重启下mysql服务,然后通过以下命令连接
mysql -ussluser -p --ssl-ca=/data/mysql/data/certs/ca.pem --ssl-cert=/data/mysql/data/certs/client-cert.pem --ssl-key=/data/mysql/data/certs/client-key.pem
查看Ssl_cipher状态
SHOW STATUS LIKE 'Ssl_cipher';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| Ssl_cipher | DHE-RSA-AES256-SHA |
+---------------+--------------------+