MySQL Use SSL Connection configuration detailed _mysql

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:

>>> OpenSSL req-sha1-new-x509-nodes-days 3650-key ca-key.pem > Ca-cert.pem you are about to be

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 can leave some blank for some fields There would be
a default value,
If en Ter '. ', the field would be left blank.
-----
Country Name (2 letter code) [AU]:CN state
or province name (full name) [Some-state]:beijing
locality N Ame (eg, city) []:beijing
organization Name (eg.) [Internet widgits Pty ltd]:xys organizational unit
NAM E (eg, section) []:xys
Common name (e.g. server FQDN or YOUR name) []:xys
e-mail address []:yongshun1228@gmail.co M

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 '-----Your are about to is asked to enter information that would be incorpor
Ated 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 can leave some blank for some fields There would be a default value, If you enter '. '
, the field is 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 ' ex Tra ' attributes to being 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 01 > server-cert.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 01 > client-cert.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:

Copy Code code 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 variables like '%ssl% ';
+---------------+-----------------+
| variable_name | Value      |
+---------------+-----------------+
| have_openssl | YES       |
| Have_ssl   | YES | | ssl_ca | ca.pem | |
ssl_capath   | | | Server-cert.pem |
| Ssl_cipher | | | | ssl_crl | | | |
ssl_key    | Server-key.pem |
+---------------+-----------------+
9 rows in Set (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, for Linux (x86_64) using Editline wrapper

Connect Ion ID:    Current
database: Current
User:    ssl_test@172.17.0.4
SSL:      Cipher in Dhe-rsa-aes256-sha Current
Pager:    stdout
using outfile:    '
using delimiter:  ;
Server version:    5.7.17 MySQL Community Server (GPL)
Protocol version:
Connection:    test_ DB via TCP/IP
Server characterset:  latin1
db   characterset:  latin1
Client CharacterSet:  latin1
Conn. CharacterSet:  latin1
TCP port: 3306 Uptime
:      1 hour 2 Min 9 sec

threads:1 questions:23 slow queries:0 opens:126 Flush tables:3 Open tables:0 queries per second avg: 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:

Copy Code code 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_key |    /etc/ Mysql/cert/server-key.pem |
+---------------+---------------------------------+
9 rows in Set (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:

Copy Code code 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, for Linux (x86_64) using Editline wrapper

Connect Ion ID:    5 current
database: Current
User:    ssl_test@172.17.0.5
SSL:      Cipher in Dhe-rsa-aes256-sha Current
Pager:    stdout
using outfile:    '
using delimiter:  ;
Server version:    5.7.17 MySQL Community Server (GPL)
Protocol version:
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.02 7
--------------

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