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

1. Background

* in a production environment, security is always an issue that cannot be ignored, and database security is a top priority because all data is stored in the database.

* when connecting to the MySQL database in an unencrypted way, all information transmitted in the network is in clear text and can be intercepted by everyone in the network, and sensitive information may be disclosed. When transmitting sensitive information, such as passwords, you can use a SSL connection.

* if the version is less than 5.7.6, it will be done as configured by MySQL 5.6 SSL.

2. MySQL connection method

* socket connection

* TCP non-SSL connection

* SSL secure connection

* SSL + password connection [version > MySQL 5.7.5]

* SSL + password + key connection

3. Introduction to SSL

* SSL refers to SSL/TLS, which is an encryption protocol for secure communication over 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.

4. Environment [turn off SeLinux]

* system environment

[root@MySQL ~] # cat / etc/redhat-release CentOS release 6.9 (Final) [root@MySQL ~] # uname-r2.6.32-696.3.2.el6.x86_64 [root@MySQL ~] # getenforce Disabled

* MySQL environment [MySQL 5.7 installation has been described in detail in the previous chapter]

Both have_openssl and have_ssl values are DISABLED, which means that ssl is not enabled.

[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 6Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 (sec) mysql > show variables like 'datadir' +-+-+ | Variable_name | Value | +-+-+ | datadir | / data/mysql_data/ | +-- -+ 1 row in set (0.01 sec)

5. SSL configuration

* use self-contained tools to generate SSL-related files

[root@MySQL] # / usr/local/mysql/bin/mysql_ssl_rsa_setup-- datadir=/data/mysql_dataGenerating 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'-

* View the generated SSL file

[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

* modify the users and permissions of the SSL files generated under the data directory

[root@MySQL ~] # chown-v mysql.mysql / data/mysql_data/*.pemchanged ownership of `/ data/mysql_data/ca-key.pem' to mysql:mysqlchanged ownership of` / data/mysql_data/ca.pem' to mysql:mysqlchanged ownership of `/ data/mysql_data/client-cert.pem' to mysql:mysqlchanged ownership of` / data/mysql_data/client-key.pem' to mysql:mysqlchanged ownership of `/ data/mysql_data/private_key.pem' to mysql: Mysqlchanged ownership of `/ data/mysql_data/public_key.pem' to mysql:mysqlchanged ownership of` / data/mysql_data/server-cert.pem' to mysql:mysqlchanged ownership of `/ data/mysql_data/server-key.pem' to mysql:mysql

* View the generated SSL file

[root@MySQL] # ls-l / data/mysql_data/*.pem-rw- 1 mysql mysql 1679 Jun 24 20:54 / data/mysql_data/ca-key.pem-rw-r--r-- 1 mysql mysql 1074 Jun 24 20:54 / data/mysql_data/ca.pem-rw-r--r-- 1 mysql mysql 1078 Jun 24 20:54 / data/mysql_data/client-cert.pem-rw- 1 mysql mysql 1675 Jun 24 20:54 / data/mysql_data/client-key.pem-rw- 1 mysql mysql 1675 Jun 24 20:54 / data/mysql_data/private_key.pem-rw-r--r-- 1 mysql mysql 451 Jun 24 20:54 / data/mysql_data/public_key.pem-rw-r--r-- 1 mysql mysql 1078 Jun 24 20:54 / data/mysql_data/server-cert.pem-rw- 1 mysql mysql 1675 Jun 24 20:54 / data/mysql_data/server-key.pem

* restart the MySQL service

[root@MySQL ~] # / etc/init.d/mysqld restartShutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS!

* Connect to MySQL to check the enabled status of SSL

Both have_openssl and have_ssl values are YES, which means that ssl is enabled successfully.

Mysql > show variables like 'have%ssl%';+-+-+ | Variable_name | Value | +-+-+ | have_openssl | YES | | have_ssl | YES | +-+-+ 2 rows in set (0.03 sec)

6. SSL + password connection test

* create a user and specify a SSL connection [it is recommended to use create user to create a user after MySQL 5.7]

Mysql > create user 'ssl_test'@'%' identified by' 123' require SSL;Query OK, 0 rows affected (0.00 sec)

* pass the password connection test [SSL connection is used by default, and you need to specify not to use SSL connection]

[root@MySQL] # mysql-h 192.168.60.129-ussl_test-pendant 123'-- ssl=0mysql: [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)

* through SSL + password connection test

SSL: Cipher in use is DHE-RSA-AES256-SHA means to connect through SSL

[root@MySQL] # mysql-h 192.168.60.129-ussl_test-pendant 123'-- sslmysql: [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 12Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 wrapperConnection id: 12Current database: Current user: ssl_test@192.168.60.129SSL: Cipher in use is DHE-RSA-AES256-SHACurrent pager: stdoutUsing outfile:''Using delimiter: Server version: 5.7.18 MySQL Community Server (GPL) Protocol version: 10Connection: 192.168.60.129 via TCP/IPServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. Characterset: utf8TCP port: 3306Uptime: 7 min 34 secThreads: 1 Questions: 29 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 105 Queries per second avg: 0.063-

7. SSL + password + key connection

* create a user and specify X509 [SSL+ key] connection [it is recommended to use create user to create a user after MySQL 5.7]

Mysql > create user 'X509 testing samples%' identified by '123' require X509 * * query OK, 0 rows affected (0.00 sec)

* Test through password connection

[root@MySQL ~] # mysql-h 192.168.60.129-uX509_test-pendant 123'-- ssl=0mysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'X509 thanks to testlings 192.168.60.129' (using password: YES)

* through SSL + password connection test

[root@MySQL ~] # mysql-h 192.168.60.129-uX509_test-pendant 123'-- sslmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'X509 thanks to testlings 192.168.60.129' (using password: YES)

* through SSL + password + key connection test

SSL: Cipher in use is DHE-RSA-AES256-SHA means to connect through SSL

[root@MySQL] # mysql-h 192.168.60.129-uX509_test-pendant 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 21Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners.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 wrapperConnection id: 21Current database: Current user: X509_test@192.168.60.129SSL: Cipher in use is DHE-RSA-AES256-SHACurrent pager: stdoutUsing outfile:''Using delimiter: Server version: 5.7.18 MySQL Community Server (GPL) Protocol version: 10Connection: 192.168.60.129 via TCP/IPServer characterset: latin1Db characterset: latin1Client characterset: utf8Conn. Characterset: utf8TCP port: 3306Uptime: 18 min 27 secThreads: 1 Questions: 40 Slow queries: 0 Opens: 118 Flush tables: 1 Open tables: 111 Queries per second avg: 0.036-

8. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

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.