最近，准备升级一组MySQL到5.7版本，在安装完MySQL5.7后，在其data目录下发现多了很多.pem类型的文件，然后通过查阅相关资料，才知这些文件是MySQL5.7使用SSL加密连接的。本篇主要介绍MySQL5.7 SSL连接加密功能、如何使用？以及使用SSL的一些注意点。
我们知道，MySQL5.7之前版本，安全性做的并不够好，比如安装时生成的root空密码账号、存在任何用户都能连接上的test库等，导致数据库存在较大的安全隐患。好在5.7版本对以上问题进行了一一修复。与此同时，MySQL 5.7版本还提供了更为简单SSL安全访问配置，且默认连接就采用SSL的加密方式，这让数据库的安全性提高一个层次。
Recently, I am going to upgrade a group of MySQL to version 5.7. After installing MySQL 5.7, I found a lot of .pem type files in the data directory. Then I found out that these files are MySQL 5.7 using SSL. Encrypted connection. This article mainly introduces the MySQL5.7 SSL connection encryption function and how to use it? And some points to note when using SSL. We know that the security of MySQL version before 5.7 is not good enough, such as the root account with an empty password generated during installation, the existence of a test library that any user can connect to, etc., resulting in greater security risks in the database. Fortunately, version 5.7 fixes the above problems one by one. At the same time, MySQL 5.7 version also provides a simpler SSL security access configuration, and the default connection uses SSL encryption, which improves the security of the database to a level. 1. Introduction to SSL SSL (Secure Socket Layer) uses data encryption, identity verification and message integrity verification mechanisms to provide security guarantees for application layer protocols based on reliable connections such as TCP. The functions provided by the SSL protocol mainly include: 1. Confidentiality of data transmission: the symmetric key algorithm is used to encrypt the transmitted data. 2. Identity verification mechanism: the server and the client are authenticated by a digital signature method based on the certificate, and the client's identity verification is optional. 3. Message integrity verification: MAC algorithm is used to verify the integrity of the message during message transmission. If the user's transmission is not through SSL, then the data in the network is transmitted in plaintext, and this brings opportunities for people with ulterior motives. Therefore, many large websites now have SSL function enabled. Similarly, in our database, if the client connects to the server to obtain data without using an SSL connection, then the data may be stolen during the transmission process. 2. MySQL5.7 SSL configuration and enablement 1. Start SSL during installation In the initial stage of MySQL5.7 installation, we found that there is one more operation than the previous version, and this operation is to install SSL. shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and upshell> bin/mysql_ssl_rsa_setup # MySQL 5.7.6 and up After running this command, the following pem files will be generated in the data_dir directory by default. These The file is used to enable the SSL function: [root mysql_data]# ll *.pem-rw------- 1 mysql mysql 1675 Jun 12 17:22 ca-key.pem #CA private key-rw-r- -r-- 1 mysql mysql 1074 Jun 12 17:22 ca.pem #Self-signed CA certificate, client connection also needs to provide -rw-r--r-- 1 mysql mysql 1078 Jun 12 17:22 client-cert .pem #The certificate file that the client needs to connect to the server-rw------- 1 mysql mysql 1675 Jun 12 17:22 client-key.pem #The private key file that the client needs to connect to the server-rw ------- 1 mysql mysql 1675 Jun 12 17:22 private_key.pem #Private member of private key/public key pair -rw-r--r-- 1 mysql mysql 451 Jun 12 17:22 public_key.pem #Shared members of private key/public key pair-rw-r--r-- 1 mysql mysql 1078 Jun 12 17:22 server-cert.pem #Server certificate file-rw------- 1 mysql mysql 1675 Jun 12 17:22 server-key.pem #The server-side private key file now enters the MySQL command line from the database server locally, you can see the following variable values: root> mysql -h 10.126.xxx.xxx -udba -p ###View the SSL opening status dba:(none)> show global variables like'%ssl%';+---------------+---------- -------+| Variable_name | Val ue |+---------------+-----------------+| have_openssl | YES || have_ssl | YES | #Already open SSL| ssl_ca | ca.pem || ssl_capath | || ssl_cert | server-cert.pem || ssl_cipher | || ssl_crl | || ssl_crlpath | || ssl_key | server-key.pem |+------ ---------+-----------------+###View how dba is connected dba:(none)> \s------ --------/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapperConnection id: 2973Current database:Current user:
[email protected] .xxxSSL: Cipher in use is DHE-RSA-AES256-SHA #Indicates that the dba user uses SSL to connect to the mysql server. If it is not ssl, it will display "Not in use" Current pager: moreUsing outfile:''Using delimiter: ;Server version: 5.7.18-log MySQL Community Server (GPL) Protocol version: 10Connection: 10.126.126.160 via TCP/IPServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8TCP port: 3306Uptime: 2 hours 35 min 48 sec [Note]: If the user uses local localhost or sock to connect to the database, Then the SSL method will not be used. 2. If mysql_ssl_rsa_setup has not been run when installing MySQL57, how to enable SSL? 1), turn off the MySQL service 2), run the mysql_ssl_rsa_setup command 3), go to the data_dir directory to modify the permissions of the .pem file user is mysqlchown -R mysql.mysql *.pem 4), start the MySQL service 3, force a user to use SSL Connect to the database#Modify the existing user ALTER USER'dba'@'%' REQUIRE SSL;#Create a new user that must use SSL grant select on *.* to'dba'@'%' identified by'xxx' REQUIRE SSL; mandatory for the above Users who connect using ssl will report an error if they are not connected using ssl, like the following: [root]# /usr/local/mysql/bin/mysql -udba -p -h10.126.xxx.xxx --ssl= 0Enter password:ERROR 1045 (28000): Access denied for user'dba'@'10.126.xxx.xxx' (using password: YES) 3. Comparison of the security of not using SSL and using SSL [Test method] Simulate data stealing through tshark packet capture on the MySQL server side. What is the difference in security between verifying and comparing unused SSL and using SSL? 1 If SSL is not used: Connect to the database on the client machine (10.126.126.161) and perform the insert operation, and use --ssl-mode=DISABLED to turn off SSL. At the same time, use tshark to capture packets on the MySQL server (10.126.126.160): [Conclusion] When SSL is not used, data can be obtained by capturing packets on the database server side, and the security is not high. 2 Using SSL: Connect to the database on the client machine (10.126.126.161) and perform the insert operation, use --ssl-mode=REQUIRED to specify SSL and use tshark to capture packets on the MySQL server (10.126.126.160) again: [Conclusion] The sentence was not caught. After using SSL encryption, tshark cannot capture the data, which is highly secure. 4. Performance comparison before and after using SSL (QPS) Server configuration: CPU: 32 cores Memory: 128G Disk: SSD In order to test QPS as accurately as possible, full-memory query is used, because our online hot data is basically in the memory; classification according to the number of concurrent threads: 1 thread, 4 threads, 8 threads , 16 threads, 24 threads, 32 threads, 64 threads; the specific data is as follows: from the test data, it can be found that after turning on SSL, the database QPS is reduced by about 23% on average, which relatively affects performance. From the perspective of SSL implementation, handshake, encryption, and decryption operations are required to establish a connection. Therefore, the time-consuming is basically in the connection establishment stage, which may cause greater performance loss for applications that use short links, such as PHP development. However, it may be much better if you use a connection pool or a long connection. V. Summary 1. MySQL5.7 defaults to open SSL connection. If users are forced to use SSL connection, then the configuration of the application also needs to specify SSL related parameters, otherwise the program will report an error. 2. Although the SSL method improves security, it also reduces QPS by about 23%. So choose carefully: 2.1. For very sensitive core data, or core data whose QPS is not high, you can use SSL to ensure data security; 2.2. For applications that use short links and require high performance, or no core data For the application of sensitive data, performance and availability are the first priority. It is recommended not to use SSL;\
一、SSL介绍
SSL（Secure Socket Layer：安全套接字层）利用数据加密、身份验证和消息完整性验证机制，为基于TCP等可靠连接的应用层协议提供安全性保证。
SSL协议提供的功能主要有：
1、 数据传输的机密性：利用对称密钥算法对传输的数据进行加密。
2.、身份验证机制：基于证书利用数字签名方法对服务器和客户端进行身份验证，其中客户端的身份验证是可选的。
3、 消息完整性验证：消息传输过程中使用MAC算法来检验消息的完整性。
如果用户的传输不是通过SSL的方式，那么其在网络中数据都是以明文进行传输的，而这给别有用心的人带来了可乘之机。所以，现在很多大型网站都开启了SSL功能。同样地，在我们数据库方面，如果客户端连接服务器获取数据不是使用SSL连接，那么在传输过程中，数据就有可能被窃取。
二、MySQL5.7 SSL配置和启用
1、安装时启动SSL
在MySQL5.7安装初始化阶段，我们发现比之前版本多了一步操作，而这个操作就是安装SSL的。
shell> bin/mysqld --initialize --user=mysql # MySQL 5.7.6 and up
shell> bin/mysql_ssl_rsa_setup # MySQL 5.7.6 and up
当运行完这个命令后，默认会在data_dir目录下生成以下pem文件，这些文件就是用于启用SSL功能的：
[root mysql_data]# ll *.pem
-rw------- 1 mysql mysql 1675 Jun 12 17:22 ca-key.pem #CA私钥
-rw-r--r-- 1 mysql mysql 1074 Jun 12 17:22 ca.pem #自签的CA证书，客户端连接也需要提供
-rw-r--r-- 1 mysql mysql 1078 Jun 12 17:22 client-cert.pem #客户端连接服务器端需要提供的证书文件
-rw------- 1 mysql mysql 1675 Jun 12 17:22 client-key.pem #客户端连接服务器端需要提供的私钥文件
-rw------- 1 mysql mysql 1675 Jun 12 17:22 private_key.pem #私钥/公钥对的私有成员
-rw-r--r-- 1 mysql mysql 451 Jun 12 17:22 public_key.pem #私钥/公钥对的共有成员
-rw-r--r-- 1 mysql mysql 1078 Jun 12 17:22 server-cert.pem #服务器端证书文件
-rw------- 1 mysql mysql 1675 Jun 12 17:22 server-key.pem #服务器端私钥文件
这时从数据库服务器本地进入MySQL命令行，你可以看到如下变量值：
root> mysql -h 10.126.xxx.xxx -udba -p
###查看SSL开启情况
dba:(none)> show global variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | YES |
| have_ssl | YES | #已经开启了SSL
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+---------------+-----------------+
###查看dba连接的方式
dba:(none)> \s
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper
Connection id: 2973
Current database:
Current user: [email protected]
SSL: Cipher in use is DHE-RSA-AES256-SHA #表示该dba用户是采用SSL连接到mysql服务器上的，如果不是ssl，那么会显示“Not in use“
Current pager: more
Using outfile: ''
Using delimiter: ;
Server version: 5.7.18-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 10.126.126.160 via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 2 hours 35 min 48 sec
【注意】：如果用户是采用本地localhost或者sock连接数据库，那么不会使用SSL方式了。
2、如果安装MySQL57时没有运行过mysql_ssl_rsa_setup，那么如何开启SSL呢？
1)、关闭MySQL服务
2)、运行mysql_ssl_rsa_setup 命令
3)、到data_dir目录下修改.pem文件的所属权限用户为mysql
chown -R mysql.mysql *.pem
4)、启动MySQL服务3、强制某用户必须使用SSL连接数据库
#修改已存在用户
ALTER USER 'dba'@'%' REQUIRE SSL;
#新建必须使用SSL用户
grant select on *.* to 'dba'@'%' identified by 'xxx' REQUIRE SSL;
对于上面强制使用ssl连接的用户，如果不是使用ssl连接的就会报错，像下面这样：
[root]# /usr/local/mysql/bin/mysql -udba -p -h10.126.xxx.xxx --ssl=0
Enter password:
ERROR 1045 (28000): Access denied for user 'dba'@'10.126.xxx.xxx' (using password: YES)
三、未使用SSL和使用SSL安全性对比
【测试方式】在MySQL服务器端通过tshark抓包的方式来模拟窃取数据。验证、对比未使用SSL和使用SSL两者在安全性上有什么不同？
1 未使用SSL情况：
在客户端机器(10.126.126.161)上连接数据库并进行insert操作，使用--ssl-mode=DISABLED关闭SSL
同时在MySQL服务器端(10.126.126.160)上用tshark进行抓包：
【结论】未使用SSL情况下，在数据库服务器端可以通过抓包的方式获取数据，安全性不高。
2 采用SSL情况：
在客户端机器(10.126.126.161)上连接数据库并进行insert操作，使用--ssl-mode=REQUIRED指定SSL
同时在MySQL服务器端(10.126.126.160)上再次用tshark进行抓包：
【结论】没有抓到该语句，采用SSL加密后，tshark抓不到数据，安全性高。
四、使用SSL前后性能对比(QPS)
服务器配置：CPU:32核心 内存:128G 磁盘：SSD
为了尽量准确测试QPS，采用全内存查询，因为我们线上热点数据基本都在内存中；按照并发线程数分类：1线程、4线程、8线程、16线程、24线程、32线程、64线程；
具体数据如下：
从测试数据可以发现，开启SSL后，数据库QPS平均降低了23%左右，相对还是比较影响性能的。从SSL实现方式来看，建立连接时需要进行握手、加密、解密等操作。所以耗时基本都在建立连接阶段，这对于使用短链接的应用程序可能产生更大的性能损耗，比如采用PHP开发。不过如果使用连接池或者长连接可能会好许多。
五、总结
1、MySQL5.7默认是开启SSL连接，如果强制用户使用SSL连接，那么应用程序的配置也需要明确指定SSL相关参数，否则程序会报错。
2、虽然SSL方式使得安全性提高了，但是相对地使得QPS也降低23%左右。所以要谨慎选择：
2.1、对于非常敏感核心的数据，或者QPS本来就不高的核心数据，可以采用SSL方式保障数据安全性；
2.2、对于采用短链接、要求高性能的应用，或者不产生核心敏感数据的应用，性能和可用性才是首要，建议不要采用SSL方式；\
