MySQL5.6 如何部署 TLS方法

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

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

[root@localhost ~]# mysql -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.6.40 Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 | stringstcpdump: listening on lo, link-type EN10MB (Ethernet), capture size 262144 bytes4~ @43x@4~!@[{4,[{5,5.6.40U@"(AOHZm8i,=0v&WabJmysql_native_passwordrootmysql_native_passwordLinux_client_namelibmysql_pid1788_client_version5.6.40  _platformx86_64program_namemysqlselect @@version_comment limit 1@@version_commentSource distributionshow databasesinformation_schemaSCHEMATASCHEMATADatabaseSCHEMA_NAMEinformation_schemamysqlperformance_schematesttlsdbmysql> 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 wrapperConnection id:          6Current database:Current user:           root@localhostSSL:                    Not in useCurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         5.6.40 Source distributionProtocol version:       10Connection:             Localhost via UNIX socketServer characterset:    utf8Db     characterset:    utf8Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /project/mysql5.6/tmp/mysql.sockUptime:                 1 day 16 hours 2 min 4 secThreads: 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  2048Generating 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  2048Generating 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.crtSignature oksubject=/C=CN/ST=BeiJing/L=BeiJing/O=mysqlDB/OU=mysql/CN=mysql_server/emailAddress=mysqladmin@test.comGetting CA Private Key[root@localhost certs]# vim ../my.cnf[mysqld]ssl_ca= /project/mysql5.6/certs/mysql_ca.crtssl_cert= /project/mysql5.6/certs/mysql_server.crtssl_key= /project/mysql5.6/certs/mysql_rsa.keyssl_cipher= DHE-RSA-AES256-SHA[root@localhost certs]# /etc/init.d/mysqld restartShutting down MySQL... SUCCESS! Starting MySQL... SUCCESS! [root@localhost ~]# mysql -u tlstest   --ssl-ca=/project/mysql5.6/certs/mysql_ca.crt  --ssl=1 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 8Server version: 5.6.40 Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 wrapperConnection id:          8Current database:Current user:           tlstest@localhostSSL:                    Cipher in use is DHE-RSA-AES256-SHACurrent pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         5.6.40 Source distributionProtocol version:       10Connection:             Localhost via UNIX socketServer characterset:    utf8Db     characterset:    utf8Client characterset:    utf8Conn.  characterset:    utf8UNIX socket:            /project/mysql5.6/tmp/mysql.sockUptime:                 19 min 26 secThreads: 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 -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 9Server version: 5.6.40 Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 bytes5.6.40\H1ZU{-hFeL))2_hka$0mysql_native_passwordSJY8DBeiJing1BeiJing1mysqlDB1mysql1mysql_CA1"0 mysqladmin@test.com0181218064627Z191218064627Z0BeiJing1BeiJing1mysqlDB1mysql1mysql_server1"0 mysqladmin@test.com07RX$zQ##tgi9b}v}q`so{.R     !3>Y9N_.7NfCBeiJing1BeiJing1mysqlDB1mysql1mysql_CA1"0 mysqladmin@test.com0181218064406Z201217064406Z0BeiJing1BeiJing1mysqlDB1mysql1mysql_CA1"0 mysqladmin@test.com0CU/5J)?J6/J!Cy |!Lu!A{EA_KBTIP|iP0N0"7A-"7A-KU..k-U95a6XfvNa7W\m?WUBlqzw:.`Z9SGnW5X}?Yg}d}wlaDufIlV0hC+,WR2IE[rjrI)5{.t* G^EN81(.Hyz5=?~nNr@l< O_eiq(%K2R#-8DE:#?MOZBI)ua":n+S1JZlFP*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 2048Generating 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.crtSignature oksubject=/C=CN/ST=BeiJing/L=BeiJing/O=mysqlDB/OU=mysql/CN=mysql_cli01/emailAddress=mysqladmin@test.comGetting 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  -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 28Server version: 5.6.40 Source distributionCopyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> 

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