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

Source: Internet
Author: User
Tags bind flush mysql client openssl openssl rsa openssl version openssl x509 docker run

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.