Shulou(Shulou.com)05/31 Report--

This article mainly explains "how mysql5.7 encrypts the connection to ssl". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "how mysql5.7 encrypts the connection to ssl".

This operation is not applicable in mysql5.7 and earlier versions. Confirm the database version number and port number mysql > select version (); +-+ | version () | +-+ | 5.7.19 | +-+ 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 | +-- -+ 1 row in set (0.01 sec)

1. SSL configuration

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

Root@MySQL] # / usr/local/mysql/bin/mysql_ssl_rsa_setup-- datadir=/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/ca-key.pem-rw-r--r-- 1 root root 1074 Jun 24 20:54 / data/ca.pem-rw-r--r-- 1 root root 1078 Jun 24 20:54 / data/client-cert.pem-rw- 1 root root 1675 Jun 24 20:54 / data/client-key .pem-rw- 1 root root 1675 Jun 24 20:54 / data/private_key.pem-rw-r--r-- 1 root root 451 Jun 24 20:54 / data/public_key.pem-rw-r--r-- 1 root root 1078 Jun 24 20:54 / data/server-cert.pem-rw- 1 root root 1675 Jun 24 20:54 / data/server-key.pem Note: change the above file permissions to belong to mysql

* 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)

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 wrapper Connection 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 sec Threads: 1 Questions: 29 Slow queries: 0 Opens: 112 Flush tables: 1 Open tables: 105 Queries per second avg: 0.063-SSL + password + key connection

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

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

Test by password connection

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

* through SSL + password connection test

[root@MySQL] # mysql-h 192.168.60.129-uwang-p'123'-- sslmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'wang'@'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-uwang-pendant 123'-- ssl-cert=/data/client-cert.pem-- ssl-key=/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 wrapper Connection id: 21Current database: Current user: wang@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 sec Threads: 1 Questions: 40 Slow queries: 0 Opens: 118Flush tables: 1 Open tables: 111Queries per second avg: 0.036 Thank you for reading, the above is the content of "how mysql5.7 encrypts ssl connections". After the study of this article, I believe you have a deeper understanding of how mysql5.7 encrypts ssl connections, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.