Shulou(Shulou.com)06/01 Report--

Contents [hide]

1 preface 2 what is the SSL configuration and use of 4 SSL SSL3 MySQL 5.7Performance test 5 summary preface

In the production environment, security can not always be ignored, and database security is the top priority, because all the data is stored in the database. It is true that MySQL did not fully consider security issues before version 5.7, resulting in major hidden dangers, such as the following problems, which some partners may know but some do not yet know:

The default installed user password for the 1.MySQL database is empty

two。 All users have access to the default installation of the test database for MySQL (even if no permission is granted)

Fortunately, Oracle officials have also realized the importance of security. After the installation of MySQL 5.7, the password of root users is no longer empty, but a password is randomly generated during installation, which leads to a larger difference between the installation of 5.7 and version 5.6. Second, the official test database has been deleted, and there is no test database after installation by default. More importantly, MySQL version 5.7 provides a simpler SSL security access configuration, and the default connection is encrypted with SSL.

What is SSL

First, take a look at how Wikipedia defines SSL:

Transport Layer Security (TLS) and its predecessor, Secure Sockets Layer (SSL), both of which are frequently referred to as' SSL', are cryptographic protocols designed to provide communications security over a computer network.

From the above definition, SSL refers to SSL/TLS, which is an encryption protocol for secure communication in computer networks. Assuming that the user's transmission is not through SSL, then it is transmitted in clear text in the network, which brings opportunities for people with ulterior motives. Therefore, many websites have turned on SSL functions by default, such as Facebook, Twtter, YouTube, Taobao and so on.

In the database field, when Inside went to a company for a technical exchange, the company introduced that its operation and maintenance platform could deal with some sensitive fields, such as the password or fund data extracted was represented by *, so DBA could not see this part of the private data. This is a good security approach in itself, but if DBA installs a tool similar to tcpdump locally, you can still get the data you want by getting the package you get. Therefore, in addition to the security processing shown on the program side, it is also necessary to turn on the secure encrypted communication function on the MySQL server side, and it is time for SSL to function.

Configuration and use of SSL in MySQL 5.7s

If you read the installation document INSTALL-BINARY of MySQL 5.7carefully, you will find that the installation document of 5.7has done an additional operation after initializing the data directory, which is not available in previous versions, and this step is the installation and configuration of SSL:

1 2 3 4. Shell > bin/mysqld-- initialize-- user=mysql shell > bin/mysql_ssl_rsa_setup.

After running the command mysql_ssl_rsa_setup, you will find that there are some files ending in pem in the data directory, and these files are the files needed to open the SSL connection:

12 3 4 5 6 7 8 9root@test-1:/usr/local/mysql/data# ls-lh *. Pem-rw- 1 mysql mysql 1.7K Nov 25 14:12 ca-key.pem-rw-r--r-- 1 mysql mysql 1.1K Nov 25 14:12 ca.pem-rw-r--r-- 1 mysql mysql 1.1K Nov 25 14:12 client-cert.pem-rw- 1 mysql mysql 1.7K Nov 25 14 12 client-key.pem-rw- 1 mysql mysql 1.7K Nov 25 14:12 private_key.pem-rw-r--r-- 1 mysql mysql 451Nov 25 14:12 public_key.pem-rw-r--r-- 1 mysql mysql 1.1K Nov 25 14:12 server-cert.pem-rw- 1 mysql mysql 1.7K Nov 25 14:12 server-key.pem

If you start the MySQL database at this time and start it, you should find the following status:

1 2 3 456 7mysql > SHOW VARIABLES LIKE 'have_ssl'; +-+ | Variable_name | Value | +-+-+ | have_ssl | YES | +-+-+ 1 row in set (0.00 sec)

This parameter indicates that the SSL feature is enabled on the MySQL server, and SSL is used to connect by default in MySQL version 5.7, such as:

1 2 3 4 56 7 8 9root@test-1:~# mysql-h20.166.224.32-udavid mysql >\ s-mysql Ver 14.14 Distrib 5.7.9, for linux-glibc2.5 (x86 / 64) using EditLine wrapper Connection id: 6119 Current database: Current user: david@10.166.224.32 SSL: Cipher in use is DHE-RSA-AES256-SHA.

You can tell whether the connected user is using SSL by the SSL column of STATUS. For example, Cipher in use is DHE-RSA-AES256-SHA in the above example indicates that the current david user connects through SSL. If you want to create a user through SSL every time, you need to set it through REQUIRE SSL before creating the user. For the above david users, you can modify it in the following ways to ensure that each connection is made through SSL. If you do not use SSL to connect, an error is reported:

1.23 4mysql > ALTER USER david@'%' REQUIRE SSL;. Root@test-1:~# mysql-h20.166.224.32-udavid-- ssl=0 ERROR 1045 (28000): Access denied for user 'david'@'10.166.224.32' (using password: YES)

MySQL 5.6 also supports SSL connection, but the operation is more complicated than 5.7. users need to create various public keys through the openssl command. For more information, please see the relevant official documents.

SSL performance test

I believe that many partners are concerned about the performance of SSL encrypted connections. It is undeniable that after enabling SSL encrypted connections, the performance will inevitably decline. The test here uses a full-memory SELECT primary key, so it can be considered the worst-case SSL performance overhead:

In the above test, in Inside's CVM environment, the CVM is configured with only 4 cores of CPU, so the overall QPS value is not high, but you should be able to find that the performance overhead after enabling SSL is about 25%.

In addition, because the expensive part of SSL is establishing connections, the cost of short links may be higher, so it is recommended to use long connections or connection pooling to reduce the extra overhead caused by SSL. Fortunately, most of MySQL's application habits are long connections.

Summary: MySQL 5.7.Configuring SSL is simpler than 5.6. multi-MySQL 5.7 clients enable SSL encrypted connections by default. Generally speaking, when SSL encrypted connections are opened, the maximum performance overhead is about 25%.

Reproduced from:

Http://www.innomysql.net/article/24297.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.