How MySQL uses SSL connections to configure MySQL using SSL connection Configuration method

See if SSL is supported

First, execute the following command on MySQL to query whether MySQL supports SSL:

Mysql> show VARIABLES like ' Have_ssl '; +---------------+-------+ | variable_name | Value | +---------------+-------+ | Have_ssl | YES | +---------------+-------+ 1 row in Set (0.02 sec)

When Have_ssl is YES, it means that the MySQL service already supports SSL. If it is desable, you will need to enable the SSL feature when you start the MySQL service.

using OpenSSL to create an SSL certificate and private key

First we need to use OpenSSL to create the server-side certificate and private key. The OpenSSL version I use is:

>>>/usr/local/cellar/openssl/1.0.2j/bin/openssl Version OpenSSL 1.0.2j SEP 2016

Creates a new ~/temp/cert directory that holds the generated certificate and private key

mkdir ~/temp/cert CD ~/temp/cert

To create the CA private key and CA certificate

Then, we first build a CA private key:

OpenSSL genrsa 2048 > Ca-key.pem

When a CA private key is available, we can then use this private key to generate a new digital certificate:

OpenSSL req-sha1-new-x509-nodes-days 3650-key ca-key.pem > Ca-cert.pem

When executing this command, you will need to fill out some questions and fill it out casually. For example:

>>> opens SL req-sha1-new-x509-nodes-days 3650-keyca-key.pem > Ca-cert.pem  you are abouttobe askedtoenter in Formation that would be incorporated Intoyour certificate request. What you are abouttoenteriswhatiscalled a Distinguishednameora DN. There are quite a few fields but you can leavesomeblank Forsomefields there would be adefaultvalue, If you enter '. ', the field would beleftblank. ----- CountryName (2 letter code) [AU]:CN Stateorprovincename (fullname) [some-state]:beijing Localityname (eg, city) []:beijing OrganizationName (eg, company) [Internet widgits Pty Ltd]:xys Organizational Unitname (eg,section) []:xys CommonName (e.g. server fqdnoryourname) []:xys Email Address []:yongshun1228@gmail.com

After executing the above command, we have a CA private key and a CA certificate.

Create a server-side RSA private key and digital certificate

Next, we need to create the server-side private key and a certificate request file, which commands the following:

OpenSSL req-sha1-newkey rsa:2048-days 3650-nodes-keyout server-key.pem > Server-req.pem

The above command generates a new private key (SERVER-KEY.PEM) and uses the new private key to generate a certificate request file (SERVER-REQ.PEM).

The above command also needs to answer a few questions, casually fill out. However, it is important to note that a challenge password is required to be empty.

That

>>> OpenSSL req-sha1-newkey rsa:2048-days 3650-nodes-keyout server-key.pem > Server-req.pem Generating a 2048 bit RSA private key .................+++ .. +++ Writing new private key to ' Server-key.pem ' ----- are about to is asked to enter information that would be incorporated into your certificate request. What you are about to enter the What is called a distinguished Name or a DN. There are quite a few fields but you can leave some For some fields there would be a default value, If you enter '. ', the field would be left blank. ----- Country Name (2 letter code) [AU]:CN State or province Name (full name) [some-state]:beijing Locality Name (eg, city) []:beijing Organization Name (eg, company) [Internet widgits Pty Ltd]:xys Organizational unit Name (eg, section) []:xys Common name (e.g. server FQDN or YOUR name) []:xys Email Address []:yongshun1228@gmail.com Please enter the following ' extra ' attributes To is sent with your certificate request A Challenge Password []: An optional company name []:

Next, we need to convert the generated private key to the RSA private key file format:

OpenSSL rsa-in server-key.pem-out Server-key.pem

In the final step, we need to generate a server-side digital certificate using the previously generated CA certificate:

OpenSSL x509-sha1-req-in server-req.pem-days 3650-ca ca-cert.pem-cakey ca-key.pem-set_serial > Server-cer T.pem

The above command creates a server-side digital certificate file.

to create a client's RSA private key and digital certificate

Similar to the commands executed on the server side, we also need to generate a private key and certificate request file for the client, as follows:

OpenSSL req-sha1-newkey rsa:2048-days 3650-nodes-keyout client-key.pem > Client-req.pem

Similarly, we need to convert the generated private key to the RSA private key file format:

OpenSSL rsa-in client-key.pem-out Client-key.pem

Finally, we also need to create a digital certificate for the client:

OpenSSL x509-sha1-req-in client-req.pem-days 3650-ca ca-cert.pem-cakey ca-key.pem-set_serial > Client-cer T.pem

using tools to create certificates and private keys

We've described how to use OpenSSL to create the private key and certificate files for SSL connections, so let's take a look at a simpler approach.

In MySQL 5.7, there is a tool named Mysql_ssl_rsa_setup, through which we can easily create the various files required for SSL connections:

Mkdir~/temp/cert Cd~/temp/cert Mysql_ssl_rsa_setup--datadir./

In the above command,--datadir represents the directory of the generated files.

When the above command is executed, eight files are also generated:

Ca-key.pem Ca.pem Client-cert.pem Client-key.pem Private_key.pem Public_key.pem Server-cert.pem Server-key.pem

These files are the same as the eight files we created using OpenSSL, and this is no longer detailed here.

SSL Configuration

In the previous steps, we have generated 8 files, respectively:

CA-CERT.PEM:CA certificate for generating digital certificates for server-side/client.

CA-KEY.PEM:CA private key, used to generate digital certificates for server-side/client.

SERVER-KEY.PEM: Server-side RSA private key

SERVER-REQ.PEM: A server-side certificate request file that is used to generate a digital certificate on the server side.

SERVER-CERT.PEM: Server-side digital certificate.

CLIENT-KEY.PEM: Client's RSA private key

CLIENT-REQ.PEM: The client's certificate request file, which is used to generate a digital certificate for the client.

CLIENT-CERT.PEM: A digital certificate for the client.

Next we need to configure the server side and the client separately.

server-side configuration

Server-side needs to use three files, namely: CA certificate, server-side RSA private key, server-side digital certificate, we need to add the following in the [mysqld] configuration domain:

[Mysqld] Ssl-ca=/etc/mysql/ca-cert.pem Ssl-cert=/etc/mysql/server-cert.pem Ssl-key=/etc/mysql/server-key.pem

We can then change the bind-address so that the MySQL service can receive clients from all IP addresses, namely:

bind-address = *

When configured, we need to restart the MySQL service to enable configuration.

In the final step, we add an account that requires SSL to be logged in to verify that the SSL we have configured is in effect:

The code is as follows:

GRANT all privileges on *.* to ' ssl_test ' @ '% ' identified by ' ssl_test ' REQUIRE SSL; FLUSH privileges;

When configured, use root to login to MySQL, execute show variables like '%ssl% ' statement will have the following output:

Mysql> show Variableslike '%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 | +---------------+-----------------+ 9rowsinset (0.01 sec)

Client Configuration

The client configuration is relatively simple. First we need to copy Ca-cert.pem, Client-cert.pem and Client-key.pem three files to the client host, and then we can execute the following command to connect the MySQL service using SSL:

MySQL--ssl-ca=/path/to/ca-cert.pem--ssl-cert=/path/to/client-cert.pem--ssl-key=/path/to/client-key.pem-h Host_name-u ssl_test-p

In addition to the above command-line configuration of SSL, we can also use the configuration file in the same way. That is, add the following in the ~/.my.cnf file:

[Client] Ssl-ca=/path/to/ca-cert.pem Ssl-cert=/path/to/client-cert.pem Ssl-key=/path/to/client-key.pem

When the connection succeeds, we execute the following instructions

Mysql> \s -------------- MySQL Ver 14.14 distrib 5.7.17,forlinux (x86_64) using Editline Wrapper Connectionid:14 CurrentDatabase: currentuser:ssl_test@172.17.0.4 Ssl:cipherinuseisdhe-rsa-aes256-sha Currentpager:stdout Using outfile: ' Using delimiter:; Server version:5.7.17 MySQL Community Server (GPL) Protocol version:10 connection:test_db via TCP/IP Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 TCP port:3306 Uptime:1hour2min9 sec Threads:1 questions:23 Slow queries:0 opens:126 Flush tables:3opentables:0 queries persecondavg:0.006 --------------

If the output contains information such as the Ssl:cipher in using is Dhe-rsa-aes256-sha, it means that SSL has been used to connect.

enable MySQL SSL connection in Docker

Above we briefly introduced if we can MySQL SSL connection, so now we use Docker to specific combat a bar!

First pull the latest MySQL image:

Docker pull MySQL

Then you need to prepare the directory structure that is mounted to the Docker container:

>>>cd~/temp >>> Tree . ├──cert │├──ca-key.pem │├──ca.pem │├──client-cert.pem │├──client-key.pem │├──private_key.pem │├──public_key.pem │├──server-cert.pem │└──server-key.pem ├──config │└──my.cnf └──db 3 Directories, 9 files

There are three subdirectories under the temp directory:

The Cert directory is used to store our previously generated certificate and private key information;

Config directory to store the MySQL service configuration file

The DB directory is the data that is used to store MySQL.

Next we need to start the MySQL container with the following command:

The code is as follows:

Docker run--rm--name test_db-p 10000:3306-e mysql_root_password=root-v/users/xiongyongshun/temp/db:/var/lib/ Mysql-v/users/xiongyongshun/temp/config:/etc/mysql/conf.d-v/users/xiongyongshun/temp/cert:/etc/mysql/cert MySQL : Latest

In our command above, we mounted the directories on the three host hosts, cert, config, DB, to the MySQL container respectively.

After you start the MySQL service, you can log in to MySQL using the root account to check whether the MySQL service has SSL enabled at this time:

Docker run-it--link test_db:test_db--rm mysql sh-c ' exec mysql-u root-p-h test_db '

After the login is successful, we execute the following instructions in MySQL:

Mysql> Show variables like '%ssl% '; +---------------+---------------------------------+ | variable_name | Value | +---------------+---------------------------------+ | Have_openssl | YES | | Have_ssl | YES | | Ssl_ca |/etc/mysql/cert/ca-cert.pem | |                 Ssl_capath | | | Ssl_cert |/etc/mysql/cert/server-cert.pem | |                 Ssl_cipher | | |                 SSL_CRL | | |                 Ssl_crlpath | | | Ssl_key |/etc/mysql/cert/server-key.pem | +---------------+---------------------------------+ 9 Rowsinset (0.01 sec)

Having the above output indicates that the MySQL service has already used SSL functionality at this time.

Next, we will, as mentioned earlier, create an account that can only log on using SSL to verify that our configuration is valid:

The code is as follows:

GRANT all privileges on *.* to ' ssl_test ' @ '% ' identified by ' ssl_test ' REQUIRE SSL; FLUSH privileges; [Code]

The above command creates an account number named Ssl_test, the password is ssl_test, and does not restrict the login host IP account.

After all these are configured successfully, we then start a MySQL client container:

[Code]docker run-it--link test_db:test_db--rm-v/users/xiongyongshun/temp/cert:/etc/mysql/cert mysql sh-c ' exec mysql --ssl-ca=/etc/mysql/cert/ca-cert.pem--ssl-cert=/etc/mysql/cert/client-cert.pem--ssl-key=/etc/mysql/cert/ Client-key.pem-h test_db-u ssl_test-p '

From the above command we can see that when we start the MySQL client container, we mount the CERT directory of the host to the/etc/mysql/cert directory within the container, so that the SSL private key and certificate file can be accessed in the container. We then use--SSL-CA,--ssl-cert, and--ssl-key in the MySQL client command line to specify the CA certificate, RSA private key, and client certificate required for the SSL connection, using the three parameters.

After the login is successful, we execute the S command:

Mysql> \s -------------- MySQL Ver 14.14 distrib 5.7.17,forlinux (x86_64) using Editline Wrapper Connectionid:5 Current database: Current user:ssl_test@172.17.0.5 Ssl:cipherinuse is Dhe-rsa-aes256-sha Current Pager:stdout Using outfile: ' Using delimiter:; Server version:5.7.17 MySQL Community Server (GPL) Protocol version:10 connection:test_db via TCP/IP Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 TCP port:3306 Uptime:6 min 8 sec Threads:2 questions:10 Slow queries:0 opens:113 Flush tables:1 Open tables:106 queries per second avg:0.027 --------------

The output has ssl:cipher in using is Dhe-rsa-aes256-sha information, which means that we do use SSL-connected MySQL server.