MySQL Encrypted connection SSL

1.SSL meaning

SSL (secure Sockets layer Secure socket), and its successor Transport Layer Security (Transport layer Security,tls) is a security protocol that provides security and data integrity for network traffic. TLS encrypts the network connection with SSL at the transport layer.

2.MYSQL5.7SSL Configuration and use

Note: This method only uses 5.7,mysql5.6 to support SSL-encrypted connections, but the configuration process is complex and requires the use of the OpenSSL command to create various types of common secret keys.

My test environment does not have SSL enabled by default and has a status of disabled

Mysql> Show variables like '%ssl% ';

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

| variable_name | Value |

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

| Have_openssl | DISABLED |

| Have_ssl | DISABLED |

|          Ssl_ca | |

|          Ssl_capath | |

|          Ssl_cert | |

|          Ssl_cipher | |

|          SSL_CRL | |

|          Ssl_crlpath | |

|          Ssl_key | |

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

You can create a PEM file by executing command mysql_ssl_rsa_setup:

[Email protected] ~]# cd/usr/local/mysql/bin/

[Email protected] bin]#./mysql_ssl_rsa_setup

Generating 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 '

-----

After running the command mysql_ssl_rsa_setup you will find some files in the data directory that end in PEM, which are the files needed to open the SSL connection:

[email protected] data]# ll *.PEM

-RW-------1 root root 1679 Nov 05:56 Ca-key.pem

-rw-r--r--1 root root 1074 Nov 05:56 Ca.pem

-rw-r--r--1 root root 1078 Nov 05:56 Client-cert.pem

-RW-------1 root root 1679 Nov 05:56 Client-key.pem

-RW-------1 root root 1679 Nov 05:56 Private_key.pem

-rw-r--r--1 root root 451 Nov 05:56 Public_key.pem

-rw-r--r--1 root root 1078 Nov 05:56 Server-cert.pem

-RW-------1 root root 1675 Nov 05:56 Server-key.pem

The newly created file belongs to the root user, needs to change the owner and owning group, and then restarts the service:

[Email protected] data]# chown-r mysql:mysql data/

[Email protected] ~]#/etc/init.d/mysqld restart

Mysql> Show variables like '%ssl% ';

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

| variable_name | Value |

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

| Have_openssl | YES |

| Have_ssl | YES |

| Ssl_ca | Ca.pem |

|                 Ssl_capath | |

| Ssl_cert | Server-cert.pem |

|                 Ssl_cipher | |

|                 SSL_CRL | |

|                 Ssl_crlpath | |

| Ssl_key | Server-key.pem |

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

Specify IP, login test via network:

[Email protected] ~]# mysql-uroot-p147258-h192.168.91.5

Mysql>\s

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

MySQL Ver 14.14 distrib 5.7.14, for linux-glibc2.5 (x86_64) using Editline Wrapper

Connection id:10

Current database:

Current User: [email protected]

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

......

Local client login, no IP specified, SSL encryption is not required by default:

[Email protected] ~]# Mysql-uroot-p147258-hlocalhost

Mysql>\s

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

MySQL Ver 14.14 distrib 5.7.14, for linux-glibc2.5 (x86_64) using Editline Wrapper

Connection Id:12

Current database:

Current User: [email protected]

Ssl:not in use

......

mysql5.7 user connections are encrypted by default using SSL, or you can use--ssl=0 (mysql5.7 can also use--ssl-mode=dibaled) to force users to not use SSL encryption:

[Email protected] ~]# mysql-ucdhu4-p147258-h192.168.91.5--ssl=0

Or:

[Email protected] ~]# mysql-ucdhu4-p147258-h192.168.91.5--ssl-mode=disabled

Mysql>\s

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

MySQL Ver 14.14 distrib 5.7.14, for linux-glibc2.5 (x86_64) using Editline Wrapper

Connection id:18

Current database:

Current User: [email protected]

Ssl:not in use

......

If you want the user to be SSL-only every time you create a user, you need to set it up by require SSL:

Mysql>alter user [email protected] '% ' require SSL;

Specifying Ssl=0 (or ssl_mode=disabled) at this point will cause an error of 1045:

[Email protected] ~]# mysql-ucdhu5-p147258-h192.168.91.5--ssl=0

ERROR 1045 (28000): Access denied for user ' cdhu5 ' @ ' Darren1 ' (using Password:yes)

Performance impact of the 3.SSL cryptographic connection

Turn on SSL encryption connection is the performance will inevitably decline, the performance cost is around 25%, in addition, because of the high SSL overhead link in the connection, so the cost of short links may be greater, so it is recommended to use a long connection or connection pooling to reduce the additional cost of SSL, Fortunately, however, MySQL's application habits are mostly a long-connected way.

This article is from the "10979687" blog, please be sure to keep this source http://10989687.blog.51cto.com/10979687/1878716

MySQL Encrypted connection SSL