Detailed explanation of MySQL using SSL connection configuration
- 黄舟Original
- 2017-01-18 11:39:341738browse
Mysql relational database management system
MySQL is an open source small relational database management system developed by the Swedish MySQL AB company. MySQL is widely used in small and medium-sized websites on the Internet. Due to its small size, fast speed, low total cost of ownership, and especially the characteristics of open source, many small and medium-sized websites choose MySQL as their website database in order to reduce the total cost of website ownership.
This article will share with you how to configure MySQL to support SSL connection methods and specific cases of configuring it in docker. Friends in need can refer to it
Check whether SSL is supported
First execute 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 means that MySQL is now The service already 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-side certificate and private key. The version of openssl I use is:
>>> /usr/local/Cellar/openssl/1.0.2j/bin/openssl version OpenSSL 1.0.2j 26 Sep 2016
Create a new ~/temp/cert directory to store the generated certificate and private key
mkdir ~/temp/cert cd ~/temp/cert
Create CA private key and CA certificate
Then, we first generate a CA private key:
openssl genrsa 2048 > ca-key.pem
When we have a CA private key, 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 in some questions, just fill them in 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 will be incorporated into 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 blank For some fields there will be a default value, If you enter '.', the field will 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
After executing the above command, we will With a CA private key and a CA certificate.
Create the server-side RSA private key and digital certificate
Next, we need to create the server-side private key 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 will Generate a new private key (server-key.pem), and use this new private key to generate a certificate request file (server-req.pem).
The above command also requires answering a few questions, fill them in casually That’s it. However, it should be noted that the A challenge password item needs to be empty.
That is:
>>> 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' ----- You are about to be asked to enter information that will be incorporated into 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 blank For some fields there will be a default value, If you enter '.', the field will 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 be sent with your certificate request A challenge password []: An optional company name []:
Next step, 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
The last step, we need to use the originally generated 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 above command will create a digital certificate file on the server side.
Create the client's RSA private key and digital certificate
It is similar to the command executed on the server side. We It is also necessary 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 introduced how to use OpenSSL earlier To create the private key and certificate file for the SSL connection, now we look at a simpler method.
In MySQL 5.7, a tool called mysql_ssl_rsa_setup is provided, through which we can easily create the SSL connection. Various files required:
mkdir ~/temp/cert cd ~/temp/cert mysql_ssl_rsa_setup --datadir ./
In the above command, --datadir indicates the directory of the generated file.
When the above command is executed, eight files will also be 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 have the same function as the eight files we created using OpenSSL, so they will not be described in detail here.
SSL configuration
In In the previous steps, we have generated 8 files, namely:
ca-cert.pem: CA certificate, used to generate server/client digital certificates.
ca-key.pem : CA private key, used to generate server/client digital certificates.
server-key.pem: Server-side RSA private key
server-req.pem: Server-side certificate request file, used to generate server-side Digital certificate.
server-cert.pem: Server-side digital certificate.
client-key.pem: Client's RSA private key
client-req.pem: Client's certificate request file, used to generate client The digital certificate of the client.
client-cert.pem: The digital certificate of the client.
Next we need to configure the server and client respectively.
Server-side configuration
服务器端需要用到三个文件, 分别是: CA 证书, 服务器端的 RSA 私钥, 服务器端的数字证书, 我们需要在 [mysqld] 配置域下添加如下内容:
[mysqld] ssl-ca=/etc/mysql/ca-cert.pem ssl-cert=/etc/mysql/server-cert.pem ssl-key=/etc/mysql/server-key.pem
接着我们还可以更改 bind-address, 使 MySQL 服务可以接收来自所有 ip 地址的客户端, 即:
bind-address = *
当配置好后, 我们需要重启 MySQL 服务, 使能配置.
最后一步, 我们添加一个需要使用 SSL 才可以登录的帐号, 来验证一下我们所配置的 SSL 是否生效:
GRANT ALL PRIVILEGES ON *.* TO 'ssl_test'@'%' IDENTIFIED BY 'ssl_test' REQUIRE SSL; FLUSH PRIVILEGES;
当配置好后, 使用 root 登录 MySQL, 执行 show variables like '%ssl%' 语句会有如下输出:
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)
客户端配置
客户端配置相对简单一些. 首先我们需要拷贝 ca-cert.pem, client-cert.pem 和 client-key.pem 这三个文件到客户端主机中, 然后我们可以执行如下命令来使用 SSL 连接 MySQL 服务:
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
除了上述的使用命令行方式配置 SSL 外, 我们也可以使用配置文件的方式. 即在 ~/.my.cnf 文件中添加如下内容即可:
[client] ssl-ca=/path/to/ca-cert.pem ssl-cert=/path/to/client-cert.pem ssl-key=/path/to/client-key.pem
当连接成功后, 我们执行如下指令
mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper Connection id: 14 Current database: Current user: ssl_test@172.17.0.4 SSL: Cipher in use 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: 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 --------------
如果输出中有 SSL: Cipher in use is DHE-RSA-AES256-SHA 之类的信息, 则表示已经使用 SSL 来连接了.
在 Docker 中使能 MySQL SSL 连接
上面我们简单介绍了一下如果使能 MySQL SSL 连接, 那么现在我们使用 Docker 来具体的实战一把吧!
首先拉取最新的 MySQL 镜像:
docker pull mysql
然后需要准备一下挂载到 Docker 容器的目录结构:
>>> 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
在 temp 目录下有三个子目录:
cert 目录用于存放我们先前生成的证书和私钥信息;
config 目录用于存放 MySQL 服务的配置文件
db 目录是用于存放 MySQL 的数据.
下一步我们需要使用如下命令启动 MySQL 容器:
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
我们在上面的命令中, 我们分别挂载了 cert, config, db 这三个宿主机上的目录到 MySQL 容器中.
启动了 MySQL 服务后, 可以先使用 root 帐号登录 MySQL, 来检查 MySQL 服务此时是否已经开启了 SSL 功能:
docker run -it --link test_db:test_db --rm mysql sh -c 'exec mysql -u root -p -h test_db'
登录成功后, 我们在 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)
有上面的输出后, 表明此时 MySQL 服务已经使用 SSL 功能了.
接着下一步, 我们按照前面所提到的, 创建一个仅仅可以使用 SSL 登录的帐号, 来检验我们的配置是否有效:
GRANT ALL PRIVILEGES ON *.* TO 'ssl_test'@'%' IDENTIFIED BY 'ssl_test' REQUIRE SSL; FLUSH PRIVILEGES;[code]
上面的命令创建了一个帐号名为 ssl_test, 密码为 ssl_test, 并且不限制登录主机 ip 的帐号.
这些都配置成功后, 我们再启动一个 MySQL 客户端容器:
[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'
从上面的这个命令中我们可以看到, 启动 MySQL 客户端容器时, 我们挂载了宿主机的 cert 目录到容器内的 /etc/mysql/cert 目录, 这样在容器中就可以访问到 SSL 私钥和证书文件了. 接着我们在 MySQL 客户端命令行中, 使用 --ssl-ca, --ssl-cert, --ssl-key 这三个参数来指定 SSL 连接所需要的 CA 证书, RSA 私钥和客户端证书.
登录成功后, 我们执行 s 命令:
mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper Connection id: 5 Current database: Current user: ssl_test@172.17.0.5 SSL: Cipher in use 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 --------------
输出中有 SSL: Cipher in use is DHE-RSA-AES256-SHA 信息则说明我们确实是使用了 SSL 连接的 MySQL 服务器.
以上就是MySQL 使用 SSL 连接配置详解的内容，更多相关内容请关注PHP中文网（www.php.cn）！