MySQL使用SSL连接

发表于 | 分类于 | | 阅读次数:
MySQL5.7使用SSL连接的配置过程

测试版本

1
2
3
4
5
6
7
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.22-log |
+------------+
1 row in set (0.01 sec)

5.7默认会开启SSL，需要关闭可以在配置文件增加skip_ssl

检测当前实例是否开启SSL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show global 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.01 sec)

SSL是关闭的

使用mysql_ssl_rsa_setup创建证书与私钥

MySQL5.7提供了mysql_ssl_rsa_setup工具，可以很方便的创建配置SSL所需要的各个文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql_ssl_rsa_setup --help
mysql_ssl_rsa_setup Version : 1.0.0 Distribution : 5.7.22 For : osx10.13 On : x86_64
Copyright (c) 2015, 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.

MySQL SSL Certificate and RSA Key Generation Utility
Usage : mysql_ssl_rsa_setup [OPTIONS]
  -?, --help          Display this help and exit.
  -v, --verbose       Be more verbose when running program
  -V, --version       Print program version and exit
  -d, --datadir=name  Directory to store generated files.
  -s, --suffix=name   Suffix to be added in certificate subject line
  --uid=name          The effective user id to be used for file permission

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- ----------------------------------------
verbose                           FALSE
datadir                           /usr/local/var/mysql
suffix                            5.7.20
uid                               (No default value)

创建相关文件

-d可以选择文件保存到哪个文件夹，这里我将文件保存在当前目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
➜  ~ mysql_ssl_rsa_setup -d ./ 
Generating a 2048 bit RSA private key
......+++
...........................................+++
writing new private key to 'ca-key.pem'
-----
Generating a 2048 bit RSA private key
................................................................+++
................................................................................................................................................................................................................+++
writing new private key to 'server-key.pem'
-----
Generating a 2048 bit RSA private key
...+++
..............................+++
writing new private key to 'client-key.pem'
-----

查看生成的文件

1
2
3
4
5
6
7
8
9
➜  ~ ll *.pem 
ca-key.pem          #CA 证书, 用于生成服务器端/客户端的数字证书
ca.pem              #CA 私钥, 用于生成服务器端/客户端的数字证书
client-cert.pem     #服务器端的 RSA 私钥
client-key.pem      #服务器端的证书请求文件, 用于生成服务器端的数字证书
private_key.pem     #服务器端的数字证书
public_key.pem      #客户端的 RSA 私钥
server-cert.pem     #客户端的证书请求文件, 用于生成客户端的数字证书
server-key.pem      #客户端的数字证书

SSL配置

服务端配置

配置证书位置

服务端需要ca.pem、server-cert.pem 、server-key.pem 三个文件，在配置文件添加

1
2
3
4
[mysqld]
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

重启服务，查看SSL已经开启

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql> show global variables like '%ssl%';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /var/lib/mysql/ca.pem          |
| ssl_capath    |                                |
| ssl_cert      | /var/lib/mysql/server-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /var/lib/mysql/server-key.pem  |
+---------------+--------------------------------+
9 rows in set (0.00 sec)

创建REQUIRE SSL账号

1
2
mysql> grant all on *.* to ssl_test identified by '123456' require ssl;
Query OK, 0 rows affected, 1 warning (0.02 sec)

客户端配置

拷贝ca.pem、client-cert.pem、client-key.pem文件到客户端机器

指定证书位置连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
➜  data mysql --ssl-ca=./ca.pem --ssl-cert=./client-cert.pem --ssl-key=./client-key.pem -h 127.0.0.1 -u ssl_test -P33061 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.22-log MySQL Community Server (GPL)

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.7.22, for osx10.13 (x86_64) using  EditLine wrapper

Connection id:          7
Current database:
Current user:           ssl_test@172.21.0.1
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          less
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.22-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               33061
Uptime:                 7 min 47 sec

Threads: 3  Questions: 19  Slow queries: 0  Opens: 111  Flush tables: 1  Open tables: 104  Queries per second avg: 0.040
--------------

mysql>

SSL: Cipher in use is DHE-RSA-AES256-SHA表示已经使用 SSL 来连接了

DISABLED SSL连接会提示密码错误

1
2
3
➜  data mysql --ssl-mode=DISABLED -h 127.0.0.1 -u ssl_test -P33061 -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'ssl_test'@'172.21.0.1' (using password: YES)

使用配置文件连接

在配置文件添加如下信息

1
2
3
4
[client]
ssl-ca=/path/ca.pem
ssl-cert=/path/client-cert.pem
ssl-key=/path/client-key.pem

则在命令行连接时会默认使用配置文件的证书信息

Replication使用SSL

配置

将Master对应的客户端证书文件ca.pem、client-cert.pem、client-key.pem拷贝到SLAVE机器，然后在CHANGE MASTER时指定证书位置，并且开启MASTER_SSL配置

1
2
3
4
5
6
7
8
9
10
mysql> CHANGE MASTER TO 
    -> MASTER_HOST='m1',
    -> MASTER_USER='ssl_test',
    -> MASTER_PASSWORD='123456',
    -> MASTER_AUTO_POSITION=1,
    -> MASTER_SSl=1,
    -> MASTER_SSl_CA='/var/lib/mysql/ca.pem',
    -> MASTER_SSl_CERT='/var/lib/mysql/client-cert.pem',
    -> MASTER_SSl_KEY='/var/lib/mysql/client-key.pem';
Query OK, 0 rows affected, 2 warnings (0.04 sec)

查看SLAVE信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: m1
                  Master_User: ssl_test
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 482
               Relay_Log_File: relay-bin.000002
                Relay_Log_Pos: 414
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 482
              Relay_Log_Space: 671
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: Yes
           Master_SSL_CA_File: /var/lib/mysql/ca.pem
           Master_SSL_CA_Path: 
              Master_SSL_Cert: /var/lib/mysql/client-cert.pem
            Master_SSL_Cipher: 
               Master_SSL_Key: /var/lib/mysql/client-key.pem
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 82ba33f9-ee1f-11e8-b4e5-0242ac150002
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 82b862a0-ee1f-11e8-b241-0242ac150003:1-13,
82ba33f9-ee1f-11e8-b4e5-0242ac150002:1-83
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

错误解决

Last_IO_Errno: 2026

1
2
3
Master_SSL_Allowed: Yes
Last_IO_Errno: 2026
Last_IO_Error: error connecting to master 'ssl_test@m1:3306' - retry-time: 60  retries: 1

SSL证书验证不通过，确认Master和Slave的证书信息是否匹配

Last_IO_Errno: 1045

有时候在确认账号密码正确的情况下，查看slave信息依然提示Last_IO_Errno: 1045，这实际上是因为在CHANGE_MASTER时Master_SSL没有开启

1
2
3
Master_SSL_Allowed: No
Last_IO_Errno: 1045
Last_IO_Error: error connecting to master 'ssl_test@m1:3306' - retry-time: 60  retries: 1

开启Master_SSL即可

1
2
3
4
5
6
7
8
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_ssl=1;
Query OK, 0 rows affected (0.03 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

ERROR 2026 (HY000): SSL connection error: error:14082174:SSL routines:ssl3_check_cert_and_algorithm:dh key too small

openssl 1.0.1e-30.el6_6.9
系bug：
https://bugzilla.redhat.com/show_bug.cgi?id=1228755
https://access.redhat.com/errata/RHBA-2015:1129

mysql也做了修复：
Fixed also in MySQL development version 5.7 in 5.7.6:
https://github.com/mysql/mysql-server/commit/866b988a76e8e7e217017a7883a52a12ec5024b9
http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-6.html

This fix updates DH params to 2048bits rather than 1024bits.

参考
  1. MySQL 使用 SSL 连接(附 Docker 例子)