MySQL5.6 5.7 SSL Config

5.7

1.mysql5.7上开启并配置ssl

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[root@mysqlmaster01 bin]# ./mysql_ssl_rsa_setup --datadir=/data/mysql_data1/ --user=mysql

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
10
11
12
13
14
15
mysql> show variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| 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)

(SSL还是没有启用)

解决办法：把数据目录下.pem的文件，属主和属组改成mysql

1
[root@mysqlmaster01 mysql_data1]# chown -R mysql.mysql *.pem

然后重启服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@mysqlmaster01 mysql_data1]# /etc/init.d/mysqld_multi stop 1

[root@mysqlmaster01 mysql_data1]# /etc/init.d/mysqld_multi start 1
[root@mysqlmaster01 mysql_data1]# /etc/init.d/mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running

[root@mysqlmaster01 mysql_data1]# mysql --login-path=mysql1 -e "show variables like 'have%ssl%';"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl | YES |
| have_ssl | YES |
+---------------+-------+

(说明ssl已经启用）
1
2
3
4
5
6
7
8
9
[root@mysqlmaster01 mysql_data1]# ll *.pem
-rw-------. 1 mysql mysql 1679 Nov 24 11:14 ca-key.pem
-rw-r--r--. 1 mysql mysql 1107 Nov 24 11:14 ca.pem
-rw-r--r--. 1 mysql mysql 1107 Nov 24 11:14 client-cert.pem
-rw-------. 1 mysql mysql 1679 Nov 24 11:14 client-key.pem
-rw-------. 1 mysql mysql 1679 Nov 24 11:14 private_key.pem
-rw-r--r--. 1 mysql mysql 451 Nov 24 11:14 public_key.pem
-rw-r--r--. 1 mysql mysql 1107 Nov 24 11:14 server-cert.pem
-rw-------. 1 mysql mysql 1675 Nov 24 11:14 server-key.pem

2.通过ssl进行连接：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
[root@mysqlmaster01 mysql_data2]# mysql -u ssl -p -h 10.2.11.226 --ssl-cert=/data/mysql_data2/client-cert.pem --ssl-key=/data/mysql_data2/client-key.pem -P 3307
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, 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> \q

(默认如果授权没有做任何限制，用户既可以通过秘钥登录,也可以通过用户名和密码登录)

3.用户授权规定只能通过ssl方式登录

1
2
3
4
5
mysql> create user 'tom'@'10.2.11.%' identified by 'Aa123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'tom'@'10.2.11.%' require ssl;
Query OK, 0 rows affected, 1 warning (0.00 sec)

4.测试

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
[root@mysqlmaster01 ~]# mysql -u tom -p -h 10.2.11.226 --ssl-mode 'REQUIRED' -P 3306 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.

mysql> \s
--------------
mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86_64) using EditLine wrapper

Connection id:	25
Current database:	
Current user:	tom@10.2.11.226
SSL:	Cipher in use is DHE-RSA-AES256-SHA
Current pager:	stdout
Using outfile:	''
Using delimiter:	;
Server version:	5.7.20-log MySQL Community Server (GPL)
Protocol version:	10
Connection:	10.2.11.226 via TCP/IP
Server characterset:	latin1
Db characterset:	latin1
Client characterset:	utf8
Conn. characterset:	utf8
TCP port:	3306
Uptime:	1 hour 34 min 11 sec

Threads: 2 Questions: 56 Slow queries: 0 Opens: 124 Flush tables: 1 Open tables: 117 Queries per second avg: 0.009
--------------

5.不仅需要ssl还需要秘钥

1
2
mysql> alter user 'tom'@'10.2.11.%' require x509;
Query OK, 0 rows affected (0.01 sec)

或者新建一个用户，要求ssl+秘钥登录

1
2
3
4
5
6
7
8
mysql> grant all on *.* to 'test'@'10.2.11.%' identified by 'Aa123456' require x509;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> grant all on *.* to 'test'@'10.2.18.%' identified by 'Aa123456' require x509;
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

测试登录：

1
2
3
4
5
6
7
[root@mysqlmaster01 mysql_data1]# mysql -u test -p -h 10.2.11.226 -P 3306 --ssl
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.
Enter password: 
ERROR 1045 (28000): Access denied for user 'test'@'10.2.11.226' (using password: YES)

[root@mysqlmaster01 mysql_data1]# mysql -u test -p -h 10.2.11.226 -P 3306 --ssl-cert=/data/dbdata/client-cert.pem --ssl-key=/data/dbdata/client-key.pem
mysql>

5.6

1.加密连接服务端配置

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

说明：

  • ss-ca：证书颁发机构(CA)证书文件的路径名
  • ssl-cert：服务器公钥证书文件的路径名。这可以发送到客户端，并通过CA证书进行身份验证。
  • ssl-key：服务器的私钥证书文件的路径名

2.客户端使用ssl

案例：

1
mysql  --ssl-ca=ca.pem  --ssl-cert=client-cert.pem  --ssl-key=client-key.pem

3.通过openssl 制作生成 SSL 证书

1
2
[root@mysqlmaster01 CA]# touch index.txt
[root@mysqlmaster01 CA]# echo 01>serial

4.创建CA证书

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
[root@server mysql56]# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
...............................................+++
......................................................................................................................+++
e is 65537 (0x10001)
[root@server mysql56]# openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.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) [XX]:CN
State or Province Name (full name) []:shanghai
Locality Name (eg, city) [Default City]:shanghai
Organization Name (eg, company) [Default Company Ltd]:als
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:ca.test.com
Email Address []:
[root@server mysql56]# ll *.pem
-rw-r--r--. 1 root root 1679 Nov 24 15:15 ca-key.pem
-rw-r--r--. 1 root root 1314 Nov 24 15:16 ca.pem

5.创建服务器证书

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
[root@server mysql56]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out 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) [XX]:CN
State or Province Name (full name) []:shanghai
Locality Name (eg, city) [Default City]:shanghai
Organization Name (eg, company) [Default Company Ltd]:als
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:server.test.com
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@server mysql56]# openssl rsa -in server-key.pem -out server-key.pem 
writing RSA key

[root@server mysql56]# openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
Signature ok
subject=/C=CN/ST=shanghai/L=shanghai/O=als/OU=ops/CN=server.test.com
Getting CA Private Key

6.创建客户端证书

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
[root@server mysql56]# openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem
Generating a 2048 bit RSA private key
.+++
...............................................+++
writing new private key to 'client-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) [XX]:CN
State or Province Name (full name) []:shanghai
Locality Name (eg, city) [Default City]:shanghai
Organization Name (eg, company) [Default Company Ltd]:als
Organizational Unit Name (eg, section) []:ops
Common Name (eg, your name or your server's hostname) []:client.test.com 
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@server mysql56]# openssl rsa -in client-key.pem -out client-key.pem 
writing RSA key
[root@server mysql56]# openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem
Signature ok
subject=/C=CN/ST=shanghai/L=shanghai/O=als/OU=ops/CN=client.test.com
Getting CA Private Key

7.检测

1
2
3
[root@mysqlmaster01 mysql56]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pem 
server-cert.pem: OK
client-cert.pem: OK

说明：

  • ca.pem: Use this as the argument to –ssl-ca on the server and client sides. (The CA certificate, if used, must be the same on both sides.)

  • server-cert.pem, server-key.pem: Use these as the arguments to –ssl-cert and –ssl-key on the server side.

  • client-cert.pem, client-key.pem: Use these as the arguments to –ssl-cert and –ssl-key on the client side.

    1
    [root@mysqlmaster01 mysql56]# chown -R mysql.mysql *.pem （更改属主和属组

    编写my.cnf文件，在【mysqld】下填写

    1
    2
    3
    ssl-ca=/data/mysql56/ca.pem 
    ssl-cert=/data/mysql56/server-cert.pem 
    ssl-key=/data/mysql56/server-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
    mysql> grant all on *.* to 'test'@'10.2.11.%' identified by 'Aa123456' require x509; （授权test用户通过ssl+秘钥登录）
    Query OK, 0 rows affected (0.00 sec)

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

    [root@mysqlmaster01 ~]# mysql -u test -h 10.2.11.226 -p -P 3308
    Enter password: 
    ERROR 1045 (28000): Access denied for user 'test'@'10.2.11.226' (using password: YES)
    (直接用密码登录错误)

    [root@mysqlmaster01 ~]# mysql -u test -h 10.2.11.226 -p -P 3308 -ssl-cert=client-cert.pem --ssl-key=client-key.pem --ssl-ca=ca.pem
    mysql: [ERROR] mysql: unknown option '-l'
    [root@mysqlmaster01 ~]# mysql -u test -h 10.2.11.226 -p -P 3308 --ssl-cert=client-cert.pem --ssl-key=client-key.pem --ssl-ca=ca.pem
    Enter password: 
    ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed
    [root@mysqlmaster01 ~]# mysql -u test -h 10.2.11.226 -p -P 3308 --ssl-cert=/data/mysql56/client-cert.pem --ssl-key=/data/mysql56/client-key.pem
    Enter password: 
    Welcome to the MySQL monitor. Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.6.38-log MySQL Community Server (GPL)

    Copyright (c) 2000, 2017, 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>

    (如果要在其他电脑上通过ssl登录该机器的数据库，必须要ca.pem,client-cert.pem,client-key.pem拷贝到其他电脑上，然后配置连接数据库的工具使用ssl)