MySQL5.6如何部署TLS方法

本文主要给大家介绍 MySQL5.6 如何部署 TLS方法，其所涉及的东西，从理论知识来获悉，有很多书籍、文献可供大家参考，从现实意义角度出发，创新互联累计多年的实践经验可分享给大家。

创新互联公司长期为近千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为鱼台企业提供专业的网站设计制作、网站建设，鱼台网站改版等技术服务。拥有十多年丰富建站经验和众多成功案例,为您定制开发。

注：省略MySQL5.6的安装过程

[root@localhost ~]# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show variables like 'version%'; 
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| version                 | 5.6.40              |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | Linux               |
+-------------------------+---------------------+
4 rows in set (0.01 sec)
# 创建新用户
mysql> create user tlstest@'%' identified by '123456';   
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,ssl_type,password from user;      
+-----------+--------+----------+-------------------------------------------+
| host      | user   | ssl_type | password                                  |
+-----------+--------+----------+-------------------------------------------+
| localhost | root   |          | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| %         | tlstest |         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-----------+--------+----------+-------------------------------------------+
2 rows in set (0.00 sec)

mysql> create database tlsdb;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| tlsdb              |
+--------------------+
5 rows in set (0.01 sec)
# 授权某个用户访问某个数据库
mysql> grant all privileges on tlsdb.* to tlstest@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for tlstest@'%';
+--------------------------------------------------------------------------------------------------------------------+
| Grants for tlstest@%                                                                                               |
+--------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tlstest'@'%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE SSL |
| GRANT ALL PRIVILEGES ON `tlsdb`.* TO 'tlstest'@'%'                                                                 |
+--------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
# 测试未加密传输
[root@localhost ~]# tcpdump -l -i lo -w - src or dst port 3306 | strings
tcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 262144 bytes
4~ @
43x@
4~!@
[{4,
[{5,
5.6.40
U@"(AOHZ
m8i,=0v&WabJ
mysql_native_password
root
mysql_native_password
Linux
_client_name
libmysql
_pid
1788
_client_version
5.6.40  _platform
x86_64
program_name
mysql
select @@version_comment limit 1
@@version_comment
Source distribution
show databases
information_schema
SCHEMATA
SCHEMATA
Database
SCHEMA_NAME
information_schema
mysql
performance_schema
test
tlsdb

mysql> grant all privileges on tlsdb.* to tlstest@'%' require ssl;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,ssl_type from user;
+-----------+---------+----------+
| host      | user    | ssl_type |
+-----------+---------+----------+
| localhost | root    |          |
| %         | tlstest | ANY      |
+-----------+---------+----------+
2 rows in set (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.40, for Linux (x86_64) using  EditLine wrapper

Connection id:          6
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.40 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /project/mysql5.6/tmp/mysql.sock
Uptime:                 1 day 16 hours 2 min 4 sec

Threads: 1  Questions: 76  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.000
--------------
# 查看TLS配置和状态
mysql> show variables like '%ssl%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_crl       |          |
| ssl_crlpath   |          |
| ssl_key       |          |
+---------------+----------+
9 rows in set (0.00 sec)

# ssl_type 是描述TLS连接的模式（类型）
# ANY  是不需要客户端证书，需要验证服务器端证书（和使用浏览器访问https站点一样）。
# X509 是需要客户端证书。
# SPECIFIED  是指定特定的issuer,，subject ，ssl_cipher ，也可以是三者的组合。
# ‘’  是默认的空。

[root@localhost ~]# mkdir /project/mysql5.6/certs
[root@localhost ~]# cd /project/mysql5.6/certs/
[root@localhost mysql5.6]# chown -R mysql.mysql certs/
[root@localhost certs]# openssl genrsa -out mysql_ca_rsa.key  2048
Generating RSA private key, 2048 bit long modulus
..+++
....................................................................................................................................+++
e is 65537 (0x10001)
[root@localhost certs]# openssl req -new -x509 -key mysql_ca_rsa.key  -days 730 -sha256  -out mysql_ca.crt  -subj /C=CN/ST=BeiJing/L=BeiJing/O=mysqlDB/OU=mysql/CN=mysql_CA/emailAddress=mysqladmin@test.com
[root@localhost certs]# openssl genrsa -out mysql_rsa.key  2048
Generating RSA private key, 2048 bit long modulus
...........................................+++
....................................................................+++
e is 65537 (0x10001)
[root@localhost certs]# openssl req -new -key mysql_rsa.key -days 365   -out mysql_server.csr  -subj  /C=CN/ST=BeiJing/L=BeiJing/O=mysqlDB/OU=mysql/CN=mysql_server/emailAddress=mysqladmin@test.com
[root@localhost certs]# ll
总用量 16
-rw-r--r--. 1 mysql mysql 1415 12月 18 14:44 mysql_ca.crt
-rw-r--r--. 1 mysql mysql 1679 12月 18 14:43 mysql_ca_rsa.key
-rw-r--r--. 1 mysql mysql 1675 12月 18 14:45 mysql_rsa.key
-rw-r--r--. 1 mysql mysql 1058 12月 18 14:45 mysql_server.csr
[root@localhost certs]# openssl x509 -req -sha256 -days 365  -CA mysql_ca.crt -CAkey mysql_ca_rsa.key  -CAcreateserial  -in  mysql_server.csr  -out mysql_server.crt
Signature ok
subject=/C=CN/ST=BeiJing/L=BeiJing/O=mysqlDB/OU=mysql/CN=mysql_server/emailAddress=mysqladmin@test.com
Getting CA Private Key
[root@localhost certs]# vim ../my.cnf
[mysqld]
ssl_ca= /project/mysql5.6/certs/mysql_ca.crt
ssl_cert= /project/mysql5.6/certs/mysql_server.crt
ssl_key= /project/mysql5.6/certs/mysql_rsa.key
ssl_cipher= DHE-RSA-AES256-SHA
[root@localhost certs]# /etc/init.d/mysqld restart
Shutting down MySQL... SUCCESS! 
Starting MySQL... SUCCESS! 
[root@localhost ~]# mysql -u tlstest   --ssl-ca=/project/mysql5.6/certs/mysql_ca.crt  --ssl=1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.6.40, for Linux (x86_64) using  EditLine wrapper

Connection id:          8
Current database:
Current user:           tlstest@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.40 Source distribution
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /project/mysql5.6/tmp/mysql.sock
Uptime:                 19 min 26 sec

Threads: 2  Questions: 115  Slow queries: 0  Opens: 87  Flush tables: 1  Open tables: 80  Queries per second avg: 0.098
--------------
mysql> show variables like '%ssl%'; 
+---------------+------------------------------------------+
| Variable_name | Value                                    |
+---------------+------------------------------------------+
| have_openssl  | YES                                      |
| have_ssl      | YES                                      |
| ssl_ca        | /project/mysql5.6/certs/mysql_ca.crt     |
| ssl_capath    |                                          |
| ssl_cert      | /project/mysql5.6/certs/mysql_server.crt |
| ssl_cipher    | DHE-RSA-AES256-SHA                                         |
| ssl_crl       |                                          |
| ssl_crlpath   |                                          |
| ssl_key       | /project/mysql5.6/certs/mysql_rsa.key    |
+---------------+------------------------------------------+
9 rows in set (0.00 sec)

mysql> show variables like '%public%'; 
+---------------------------------+----------------+
| Variable_name                   | Value          |
+---------------------------------+----------------+
| sha256_password_public_key_path | public_key.pem |
+---------------------------------+----------------+
1 row in set (0.00 sec)

# 抓包测试
[root@localhost ~]# mysql -u tlstest -h 127.0.0.1 -P 3306  --ssl-ca=/project/mysql5.6/certs/mysql_ca.crt  --ssl=1 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
| tlsdb              |
+--------------------+
3 rows in set (2.80 sec)

[root@localhost ~]# tcpdump -l -i lo  -w - src or dst port 3306 | strings         
tcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 262144 bytes
5.6.40
\H1ZU{-h
FeL))2_hka$0
mysql_native_password
SJY8D
BeiJing1
BeiJing1
mysqlDB1
mysql1
mysql_CA1"0 
mysqladmin@test.com0
181218064627Z
191218064627Z0
BeiJing1
BeiJing1
mysqlDB1
mysql1
mysql_server1"0 
mysqladmin@test.com0
7RX$
zQ##
tgi9
b}v}
q`so
{.R     !3>
Y9N_
.7NfC
BeiJing1
BeiJing1
mysqlDB1
mysql1
mysql_CA1"0 
mysqladmin@test.com0
181218064406Z
201217064406Z0
BeiJing1
BeiJing1
mysqlDB1
mysql1
mysql_CA1"0 
mysqladmin@test.com0
CU/5
J)?J
6/J!
Cy |!
Lu!A{E
A_KB
TIP|i
P0N0
"7A-
"7A-
KU..
k-U9
5a6X
fvNa
7W\m?
WUBl
qzw:
.`Z9
SGnW
5X}?Y
g}d}
wlaD
ufIl
V0hC+,WR
2IE[
rjrI
)5{.t*
 G^E
N81(
.Hyz5
=?~n
Nr@l
< O_
eiq(
%K2R
#-8DE
:#?M
OZBI
)ua"
:n+S
1JZ
lFP
*Z*4

[root@localhost ~]# tshark -ni lo -R "tcp.dstport eq 3306"
tshark: -R without -2 is deprecated. For single-pass filtering use -Y.
Running as user "root" and group "root". This could be dangerous.
Capturing on 'Loopback'
  1 0.000000000    127.0.0.1 -> 127.0.0.1    TCP 74 43154 > 3306 [SYN] Seq=0 Win=43690 Len=0 MSS=65495 SACK_PERM=1 TSval=8184814 TSecr=0 WS=128
  3 0.000092859    127.0.0.1 -> 127.0.0.1    TCP 66 43154 > 3306 [ACK] Seq=1 Ack=1 Win=43776 Len=0 TSval=8184814 TSecr=8184814
  5 0.000434952    127.0.0.1 -> 127.0.0.1    TCP 66 43154 > 3306 [ACK] Seq=1 Ack=79 Win=43776 Len=0 TSval=8184814 TSecr=8184814
  6 0.000604778    127.0.0.1 -> 127.0.0.1    MySQL 102 Login Request user=
  8 0.003121269    127.0.0.1 -> 127.0.0.1    TCP 247 [TCP segment of a reassembled PDU]
 11 0.017109037    127.0.0.1 -> 127.0.0.1    TCP 66 43154 > 3306 [ACK] Seq=218 Ack=2894 Win=174720 Len=0 TSval=8184831 TSecr=8184820
 12 0.025592782    127.0.0.1 -> 127.0.0.1    TCP 404 [TCP segment of a reassembled PDU]
 14 0.029730886    127.0.0.1 -> 127.0.0.1    TCP 332 [TCP segment of a reassembled PDU]
 16 0.030049352    127.0.0.1 -> 127.0.0.1    TCP 172 [TCP segment of a reassembled PDU]
 18 0.071404170    127.0.0.1 -> 127.0.0.1    TCP 66 43154 > 3306 [ACK] Seq=928 Ack=3356 Win=185984 Len=0 TSval=8184885 TSecr=8184844
 19 11.507220009    127.0.0.1 -> 127.0.0.1    TCP 156 [TCP segment of a reassembled PDU]
 21 11.507794338    127.0.0.1 -> 127.0.0.1    TCP 66 43154 > 3306 [ACK] Seq=1018 Ack=3574 Win=191616 Len=0 TSval=8196321 TSecr=8196321

MySQL5.6 只能支持TLSv1 ，不能支持更高版本的TLS协议；

[root@localhost certs]# openssl genrsa -out client01.key 2048
Generating RSA private key, 2048 bit long modulus
............+++
................+++
e is 65537 (0x10001)
[root@localhost certs]# openssl req -new -key client01.key -out client01.csr -subj /C=CN/ST=BeiJing/L=BeiJing/O=mysqlDB/OU=mysql/CN=mysql_cli01/emailAddress=mysqladmin@test.com
[root@localhost certs]# openssl x509 -req -sha256 -days 365  -CA mysql_ca.crt -CAkey mysql_ca_rsa.key  -CAcreateserial  -in client01.csr  -out client01.crt
Signature ok
subject=/C=CN/ST=BeiJing/L=BeiJing/O=mysqlDB/OU=mysql/CN=mysql_cli01/emailAddress=mysqladmin@test.com
Getting CA Private Key
[root@localhost certs]# ll
总用量 36
-rw-r--r--. 1 mysql mysql 1302 12月 18 15:55 client01.crt
-rw-r--r--. 1 mysql mysql 1058 12月 18 15:54 client01.csr
-rw-r--r--. 1 mysql mysql 1679 12月 18 15:54 client01.key
-rw-r--r--. 1 mysql mysql 1415 12月 18 14:44 mysql_ca.crt
-rw-r--r--. 1 mysql mysql 1679 12月 18 14:43 mysql_ca_rsa.key
-rw-r--r--. 1 mysql mysql   17 12月 18 15:55 mysql_ca.srl
-rw-r--r--. 1 mysql mysql 1675 12月 18 14:45 mysql_rsa.key
-rw-r--r--. 1 mysql mysql 1306 12月 18 14:46 mysql_server.crt
-rw-r--r--. 1 mysql mysql 1058 12月 18 14:45 mysql_server.csr
[root@localhost ~]# mysql -u tlstest   --ssl-ca=/project/mysql5.6/certs/mysql_ca.crt  --ssl=1 --ssl-cert=/project/mysql5.6/certs/client01.crt --ssl-key=/project/mysql5.6/certs/client01.key  -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 28
Server version: 5.6.40 Source distribution

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 
看了以上 MySQL5.6 如何部署 TLS方法介绍，希望能给大家在实际运用中带来一定的帮助。本文由于篇幅有限，难免会有不足和需要补充的地方，大家可以继续关注创新互联行业资讯板块，会定期给大家更新行业新闻和知识，如有需要更加专业的解答，可在官网联系我们的24小时售前售后，随时帮您解答问题的。