MySQL 5.7.18 encrypted connection mysql_ssl_rsa_setup install MySQL certificate login

MySQL 5.7.18 : https://dev.mysql.com/get/Downloads/MySQL-5.7/ Mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz

Installation steps: http://www.cnblogs.com/imweihao/p/7196516.html

Turn from: http://www.cnblogs.com/mysql-dba/p/7061300.html

　　　http://blog.csdn.net/tanweii163/article/details/50102769

First, SSL introduction

SSL (Secure Socket Layer: Secured sockets layers) leverages data encryption, authentication, and message integrity validation mechanisms to provide security assurances for application-layer protocols based on reliable connections such as TCP.

The features provided by the SSL protocol are mainly:

1, the confidentiality of data transmission: the use of symmetric key algorithm to encrypt the transmitted data.
2., authentication mechanism: Based on the certificate uses the digital signature method to authenticate the server and the client, where the authentication of the client is optional.
3. Message Integrity Verification: The MAC algorithm is used during message transfer to verify the integrity of the message.

If the user's transmission is not through the way of SSL, then its data in the network is transmitted in clear text, and this has brought an opportunity for the ulterior motives. As a result, many large Web sites now have SSL enabled. Similarly, in our database, if the client connects to the server to obtain the data is not using SSL connection, then in the transmission process, the data may be stolen.

Second, MySQL5.7 SSL enabled

1. log on to the database to see if SSL is turned on

Mysql> Show variables like ' Have_ssl ';

#whenHave_sslto beYESwhen, indicates that at this timeMySQLService has been supportedSSLthe. if it isdesable,you need to start theinstallationMysql_ssl_rsa_setupso that it canSupportSSLfunction

2. Installing mysql_ssl_rsa_setup

[[Email protected] mysqldata]# service mysqld stop

[Email protected] mysql]# Bin/mysql_ssl_rsa_setup

[Email protected] mysql]# cd/data/mysqldata/

[Email protected] mysqldata]# chown-r mysql.mysql *.PEM

[Email protected] mysql]# service mysqld restart

[Email protected] mysql]# cd/data/mysqldata/

[Email protected] mysqldata]# LS-LH *.PEM

run out of commands Mysql_ssl_rsa_setup you will find a number of data directories to PEM end of the file, and these files are open SSL files required for connection

3. start the MySQL database and see if the variable has changed:

Mysql> Show variables like ' Have_ssl ';

This parameter represents MySQL the server is turned on SSL function

Third, MySQL5.7 SSL Configuration

1. View the connection by means that no SSL connection is used

Mysql> \s

"Note": If the user is using a local localhost or sock Connect to the database, you will not use SSL Way out.

2. Adding SSL parameters to the MY.CNF configuration file

Adding SSL parameters to the MY.CNF configuration file

[Mysqld]

ssl-ca=/opt/mysql01/data/ca.pem

ssl-cert=/opt/mysql01/data/client-cert.pem

ssl-key=/opt/mysql01/data/client-key.pem

[MySQL]

ssl-ca=/opt/mysql01/data/ca.pem

ssl-cert=/opt/mysql01/data/client-cert.pem

ssl-key=/opt/mysql01/data/client-key.pem

3. Client Connection

[Email protected] mysql]# bin/mysql-uroot-h 192.168.31.84-p

It is best to test with a remote connection, localhost or-s UNIX socket connection, which may not use SSL.

mysql> status

--------------

Bin/mysql Ver 14.14 Distrib 5.7.9, for Linux (x86_64) using Editline Wrapper

Connection id:10

Current database:

Current User: [email protected]

Ssl:cipher in use is Dhe-rsa-aes256-sha

Current Pager:stdout

Using outfile: '

Using delimiter:;

Status in SSL displays cipher in use, indicating that the current connection uses SSL

Or view status Ssl_cipher can also, value is not empty, indicating that the client connection is SSL enabled

Mysql> Show status like ' Ssl_cipher ';

+---------------+--------------------+

| variable_name | Value |

+---------------+--------------------+

| Ssl_cipher | Dhe-rsa-aes256-sha |

+---------------+--------------------+

1 row in Set (0.00 sec)

If the customer does not want to use an SSL connection, the SSL connection can be disabled using-ssl=0 in the MySQL connection parameter, and the effect should be viewed by itself using status

mysql> bin/mysql-uroot-h 192.168.31.84--ssl=0-p

Remote Connection results need to be opened Root remote connection, actual production not recommended Root

Mysql> GRANT All privileges on * * to ' root ' @ '% ' identified by ' 123456 ' with GRANT OPTION;

mysql> flush Privileges;

Mysql> quit

force a user to use SSL connecting to a database

#修改已存在用户

mysql> ALTER USER ‘dba‘@‘%‘ REQUIRE SSL;

#新建必须使用SSL用户

mysql> grant select on *.* to ‘dba‘@‘%‘ identified by ‘xxx‘ REQUIRE SSL;

# for the above mandatory use SSL connected users, if not using the SSL the connection will be an error, like this:

[[email protected] mysql] /usr/local/mysql/bin/mysql -udba -p -h10.126.xxx.xxx --ssl=0

ERROR 1045 (28000): Access denied for user ‘dba‘@‘10.126.xxx.xxx‘ (using password: YES)

Iv. non-use of SSL and use of SSL security comparison

The "test method" simulates stealing data on the MySQL server side by Tshark the packet capture. What is the security difference between verifying, comparing not using SSL and using SSL?

1. SSL not used:

Connect the database and insert operations on the client machine (10.126.126.161), and use--ssl-mode=disabled to turn off SSL

Also use Tshark on the MySQL server side (10.126.126.160) to grab the packet:

"Conclusion" when SSL is not used, the database server can get the data by grasping the packet, and the security is not high.

2. Use SSL Condition:

Connect to the database on the client machine (10.126.126.161) and insert, using--ssl-mode=required to specify SSL

At the same time on the MySQL server side (10.126.126.160) again with the Tshark to grab the package:

"Conclusion" did not catch the statement, the use of SSL encryption, Tshark cannot grasp the data, high security.

V. Performance comparison before and after using SSL (QPS)

Server configuration: cpu:32 Core Memory: 128G Disk: SSD

In order to test the QPS as accurately as possible, use full memory query, because our online hotspot data are basically in memory, according to the number of concurrent threads classification: 1 thread, 4 thread, 8 thread, 16 thread, 24 thread, 32 thread, 64 thread;

The specific data are as follows:

From the test data can be found, the open SSL, database QPS average reduced by about 23%, relative or comparative impact performance. From the point of view of SSL implementation, it is necessary to do the handshake, encrypt, decrypt and so on when establishing the connection. So time-consuming is basically a connection phase, which can result in greater performance loss for applications that use short links, such as PHP development. However, it may be a lot better if you use connection pooling or long connections.

Vi. Summary

1, MySQL5.7 default is to turn on SSL connection, if the user is forced to use SSL connection, then the application configuration also need to explicitly specify SSL-related parameters, or the program will error.

2, although the SSL method makes the security improved, but the relative to make the QPS also reduced by about 23%. So be careful to choose:

2.1, for very sensitive core data, or QPS is not high core data, you can use the SSL method to ensure data security;

2.2, for the use of short-link, demand high-performance applications, or do not produce core sensitive data applications, performance and availability is the first, it is recommended not to use SSL method;

MySQL 5.7.18 encrypted connection mysql_ssl_rsa_setup install MySQL certificate login