MySQL5.6 该如何部署 TLS方法

发布时间：2023-08-21 14:55:46 所属栏目：MySql教程来源：

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

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

[r

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

注：省略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.MySQL5.6 如何部署 TLS方法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>

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!