MySQL uses SSL connection configuration details, mysqlssl

MySQL uses SSL connection configuration details, mysqlssl

Check whether SSL is supported

First, run the following command on MySQL to check 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 indicates that the MySQL service now supports SSL. If it is DESABLE, You need to enable the SSL function when starting the MySQL service.

Use OpenSSL to create an SSL Certificate and Private Key

First, we need to use openssl to create the server certificate and private key. The openssl version I use is:

>>> /usr/local/Cellar/openssl/1.0.2j/bin/openssl versionOpenSSL 1.0.2j 26 Sep 2016

Create a new ~ /Temp/cert Directory, used to store the generated certificate and Private Key

mkdir ~/temp/certcd ~/temp/cert

Create a CA private key and a CA certificate

Then, we first generate a CA private key:

openssl genrsa 2048 > ca-key.pem


When a CA private key is available, we can 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 you execute this command, you need to fill in some questions. You can simply fill in the questions. For example:

>>> openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pemYou are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or a DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [AU]:CNState or Province Name (full name) [Some-State]:BeijingLocality Name (eg, city) []:BeijingOrganization Name (eg, company) [Internet Widgits Pty Ltd]:xysOrganizational Unit Name (eg, section) []:xysCommon Name (e.g. server FQDN or YOUR name) []:xysEmail Address []:yongshun1228@gmail.com

After executing the preceding Command, we have a CA private key and a CA certificate.

Create the RSA private key and digital certificate on the server

Next, we need to create the private key of the server and a certificate request file. The command is as follows:

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 this new private key to generate a certificate request file (server-req.pem ).
The above command also needs to answer A few questions, just enter it. However, it should be noted that the challenge password item must be blank.
That is:

>>> openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pemGenerating a 2048 bit RSA private key.................+++..+++writing new private key to 'server-key.pem'-----You are about to be asked to enter information that will be incorporatedinto your certificate request.What you are about to enter is what is called a Distinguished Name or a DN.There are quite a few fields but you can leave some blankFor some fields there will be a default value,If you enter '.', the field will be left blank.-----Country Name (2 letter code) [AU]:CNState or Province Name (full name) [Some-State]:BeijingLocality Name (eg, city) []:BeijingOrganization Name (eg, company) [Internet Widgits Pty Ltd]:xysOrganizational Unit Name (eg, section) []:xysCommon Name (e.g. server FQDN or YOUR name) []:xysEmail Address []:yongshun1228@gmail.comPlease enter the following 'extra' attributesto be sent with your certificate requestA 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 last step, we need to use the original CA certificate to generate a server-side digital 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 preceding command creates a certificate file on the server.

Create the RSA private key and digital certificate of the Client

Similar to the Command executed by the server, we also need to generate a private key and certificate request file for the client. The command is 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


Use tools to create certificates and private keys

We have introduced how to use OpenSSL to create the private key and Certificate file for the SSL connection. Now let's look at a simpler method.
In MySQL 5.7, a tool named mysql_ssl_rsa_setup is provided, through which we can easily create various files required for SSL connections:

mkdir ~/temp/certcd ~/temp/certmysql_ssl_rsa_setup --datadir ./

In the preceding command, -- datadir indicates the directory of the generated file.

After the preceding command is executed, eight files are generated:

ca-key.pemca.pemclient-cert.pemclient-key.pemprivate_key.pempublic_key.pemserver-cert.pemserver-key.pem

These files serve the same purpose as the eight files we created using OpenSSL.

SSL Configuration

In the previous step, we have generated eight files:

Ca-cert.pem: CA certificate, used to generate a digital certificate for the server/client.
Ca-key.pem: CA private key used to generate a digital certificate for the server/client.
Server-key.pem: server-side RSA private key
Server-req.pem: server-side certificate request file, used to generate a server-side digital certificate.
Server-cert.pem: server-side digital certificate.
Client-key.pem: RSA private key of the Client
Client-req.pem: the client's certificate request file, used to generate the client's digital certificate.
Client-cert.pem: the client's digital certificate.

Next, we need to configure the server and client respectively.

Server Configuration

The server needs to use three files: CA certificate, server-side RSA private key, and server-side digital certificate. We need to add the following content under the [mysqld] configuration domain:

[mysqld]ssl-ca=/etc/mysql/ca-cert.pemssl-cert=/etc/mysql/server-cert.pemssl-key=/etc/mysql/server-key.pem

Then we can change bind-address so that the MySQL service can receive clients from all ip addresses, that is:

bind-address = *

After the configuration, We need to restart the MySQL service to enable the configuration.

In the last step, we add an account that requires SSL to log on to verify that the configured SSL has taken effect:

Copy codeThe Code is as follows: grant all privileges on *. * TO 'ssl _ test' @ '%' identified by 'ssl _ test' REQUIRE ssl;
Flush privileges;

After the configuration, log on to MySQL as the root user and run the show variables like '% ssl %' statement. The following output is displayed:

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 |+---------------+-----------------+9 rows in set (0.01 sec)

Client Configuration

Client configuration is relatively simple. First we need to copy the ca-cert.pem, client-cert.pem and client-key.pem these three files into the client host, then we can execute the following command to connect to 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, we can also use the configuration file ~ Add the following content to the/. my. cnf file:

[client]ssl-ca=/path/to/ca-cert.pemssl-cert=/path/to/client-cert.pemssl-key=/path/to/client-key.pem

After the connection is successful, run the following command:

mysql> \s--------------mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapperConnection id:    14Current database:Current user:    ssl_test@172.17.0.4SSL:      Cipher in use is DHE-RSA-AES256-SHACurrent pager:    stdoutUsing outfile:    ''Using delimiter:  ;Server version:    5.7.17 MySQL Community Server (GPL)Protocol version:  10Connection:    test_db via TCP/IPServer characterset:  latin1Db   characterset:  latin1Client characterset:  latin1Conn. characterset:  latin1TCP port:    3306Uptime:      1 hour 2 min 9 secThreads: 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 SSL: Cipher in use is DHE-RSA-AES256-SHA, it indicates that SSL is used for connection.

Enabling MySQL SSL connection in Docker

We have briefly introduced how to use Docker to implement MySQL SSL connection!

First, pull the latest MySQL image:

docker pull mysql


Then you need to prepare the directory structure 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└── db3 directories, 9 files

The temp directory contains three subdirectories:

The cert Directory stores the previously generated certificate and private key information;
The config directory stores the configuration files of the MySQL service.
The db directory is used to store MySQL Data.

Next, run the following command to start the MySQL container:

Copy codeThe Code is as follows: docker run -- rm -- name test_db-p :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 the preceding command, we mounted the directories on the cert, config, and db hosts to the MySQL container.

After starting the MySQL service, you can log on to MySQL using the root account to check whether the SSL function is enabled for the MySQL service:

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

After successful login, we execute the following command 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 rows in set (0.01 sec)

After the preceding output, it indicates that the MySQL service has used the SSL function.

Next, we will create an account that can only log on with SSL as mentioned above to check whether our configuration is valid:

Copy codeThe Code is as follows: grant all privileges on *. * TO 'ssl _ test' @ '%' identified by 'ssl _ test' REQUIRE ssl;
Flush privileges; [code]

The preceding command creates an account named ssl_test with the password ssl_test and does not limit the account used to log on to the Host ip address.

After the configuration is successful, we start another 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 preceding command, we can see that when the MySQL client container is started, the cert Directory of the host machine is mounted to the/etc/mysql/cert Directory in the container, in this way, you can access the SSL private key and Certificate file in the container. then, in the MySQL client command line, use the -- ssl-ca, -- ssl-cert, -- ssl-key parameters to specify the CA certificate required for the SSL connection, RSA private key and client certificate.

After the logon is successful, run the s command:

mysql> \s--------------mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapperConnection id:    5Current database:Current user:    ssl_test@172.17.0.5SSL:      Cipher in use is DHE-RSA-AES256-SHACurrent pager:    stdoutUsing outfile:    ''Using delimiter:  ;Server version:    5.7.17 MySQL Community Server (GPL)Protocol version:  10Connection:    test_db via TCP/IPServer characterset:  latin1Db   characterset:  latin1Client characterset:  latin1Conn. characterset:  latin1TCP port:    3306Uptime:      6 min 8 secThreads: 2 Questions: 10 Slow queries: 0 Opens: 113 Flush tables: 1 Open tables: 106 Queries per second avg: 0.027--------------

The output contains the SSL: Cipher in use is DHE-RSA-AES256-SHA information, which indicates that we are indeed using a MySQL server with an SSL connection.