MySQL 5.7配置SSL连接

如果想服务能够部署自动支持安全连接，使用mysql_ssl_rsa_setup工具来创建缺省SSL与RSA文件
[root@cs2 bin]# ./mysql_ssl_rsa_setup --datadir=/mysqldata/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'
-----

启动mysql
[root@cs2 ~]# service mysqld start
Starting MySQL.. SUCCESS!

测试远程登录
-bash-4.2$ mysql -h 10.11.13.19 -P 3306 -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, 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> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SELECT DISTINCT CONCAT('User: [', user, '''@''', host, '];') AS USER_HOST FROM user;
+------------------------------------+
| USER_HOST                          |
+------------------------------------+
| User: [root'@'%];                  |
| User: [mysql.session'@'localhost]; |
| User: [mysql.sys'@'localhost];     |
+------------------------------------+
3 rows in set (0.05 sec)

通过ssl登录mysql服务器
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u root -pabcd123 --ssl-cert=client-cert.pem --ssl-key=client-key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, 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.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          7
Current database:
Current user:           root@10.11.13.19
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             10.11.13.19 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 14 min 11 sec

Threads: 2  Questions: 13  Slow queries: 0  Opens: 108  Flush tables: 1  Open tables: 101  Queries per second avg: 0.015
--------------

mysql> show variables like 'have_ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_ssl      | DISABLED |
+---------------+----------+
1 row in set (0.01 sec)

mysql> show variables like 'require_secure_transport';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
+--------------------------+-------+
1 row in set (0.00 sec)

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  |
+---------------+-----------------+

从上面的查询结果可以看到SSL: Not in use，have_ssl和have_openssl为DISABLED，这说明实际上是没有启用ssl。

这是因为通过执行mysql_ssl_rsa_setup命令产生的pem文件的权限为root用户而不是mysql用户造成的，将这些pem文件的权限修改为mysql用户与组 权限不要太大，只用户权限为rw，组与其它用户需要r权限
[root@localhost mysql]# ls -lrt
30172
-rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile1
-rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile2
drwxr-x---. 2 mysql mysql        48 321 16:25 undo
-rw-r-----. 1 mysql mysql        56 321 16:25 auto.cnf
drwxr-x---. 2 mysql mysql      8192 321 16:25 performance_schema
drwxr-x---. 2 mysql mysql      4096 321 16:25 mysql
drwxr-x---. 2 mysql mysql      8192 321 16:25 sys
-rw-r-----. 1 mysql mysql       177 321 16:25 binlog.000001
-rw-r--r--. 1 mysql mysql      1679 321 16:26 ca-key.pem
-rw-r--r--. 1 mysql mysql      1107 321 16:26 ca.pem
-rw-r--r--. 1 root  root       1679 321 16:26 server-key.pem
-rw-r--r--. 1 root  root       1107 321 16:26 server-cert.pem
-rw-r--r--. 1 root  root      1675 321 16:26 client-key.pem
-rw-r--r--. 1 root  root      1107 321 16:26 client-cert.pem
-rw-r--r--. 1 root  root      1675 321 16:26 private_key.pem
-rw-r--r--. 1 root  root       451 321 16:26 public_key.pem
-rw-r-----. 1 mysql mysql    114688 524 17:00 ts2.ibd
-rw-r-----. 1 mysql mysql     98304 524 17:01 ts1.ibd
drwxr-x---. 2 mysql mysql        32 524 17:11 test
-rw-r-----. 1 mysql mysql      9352 621 10:46 binlog.000002
-rw-r-----. 1 mysql mysql       177 621 10:46 binlog.000003
-rw-r-----. 1 mysql mysql       154 9 9 19:55 binlog.000004
-rw-r-----. 1 mysql mysql       177 9 9 20:23 binlog.000005
-rw-r-----. 1 mysql mysql       177 9 9 20:38 binlog.000006
-rw-r-----. 1 mysql mysql       177 9 9 20:44 binlog.000007
-rw-r-----. 1 mysql mysql       122 9 9 20:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql       248 9 9 20:44 binlog.index
-rw-r-----. 1 mysql mysql       154 9 9 20:44 binlog.000008
-rw-r-----. 1 mysql mysql         6 9 9 20:44 mysqld.pid
-rw-r-----. 1 mysql mysql  12582912 9 9 20:44 ibtmp1
-rw-r-----. 1 mysql mysql  10485760 9 9 20:44 ibdata1
-rw-r-----. 1 mysql mysql 104857600 9 9 20:44 ib_logfile0
-rw-r-----. 1 mysql mysql    107011 9 9 20:54 mysql.err

[root@localhost mysql]# chown -R mysql:mysql *.pem
[root@localhost mysql]# ls -lrt
30172
-rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile1
-rw-r-----. 1 mysql mysql 104857600 321 16:25 ib_logfile2
drwxr-x---. 2 mysql mysql        48 321 16:25 undo
-rw-r-----. 1 mysql mysql        56 321 16:25 auto.cnf
drwxr-x---. 2 mysql mysql      8192 321 16:25 performance_schema
drwxr-x---. 2 mysql mysql      4096 321 16:25 mysql
drwxr-x---. 2 mysql mysql      8192 321 16:25 sys
-rw-r-----. 1 mysql mysql       177 321 16:25 binlog.000001
-rw-r--r--. 1 mysql mysql      1679 321 16:26 ca-key.pem
-rw-r--r--. 1 mysql mysql      1107 321 16:26 ca.pem
-rw-r--r--. 1 mysql mysql      1679 321 16:26 server-key.pem
-rw-r--r--. 1 mysql mysql      1107 321 16:26 server-cert.pem
-rw-r--r--. 1 mysql mysql      1675 321 16:26 client-key.pem
-rw-r--r--. 1 mysql mysql      1107 321 16:26 client-cert.pem
-rw-r--r--. 1 mysql mysql      1675 321 16:26 private_key.pem
-rw-r--r--. 1 mysql mysql       451 321 16:26 public_key.pem
-rw-r-----. 1 mysql mysql    114688 524 17:00 ts2.ibd
-rw-r-----. 1 mysql mysql     98304 524 17:01 ts1.ibd
drwxr-x---. 2 mysql mysql        32 524 17:11 test
-rw-r-----. 1 mysql mysql      9352 621 10:46 binlog.000002
-rw-r-----. 1 mysql mysql       177 621 10:46 binlog.000003
-rw-r-----. 1 mysql mysql       154 9 9 19:55 binlog.000004
-rw-r-----. 1 mysql mysql       177 9 9 20:23 binlog.000005
-rw-r-----. 1 mysql mysql       177 9 9 20:38 binlog.000006
-rw-r-----. 1 mysql mysql       177 9 9 20:44 binlog.000007
-rw-r-----. 1 mysql mysql       122 9 9 20:44 ib_buffer_pool
-rw-r-----. 1 mysql mysql       248 9 9 20:44 binlog.index
-rw-r-----. 1 mysql mysql       154 9 9 20:44 binlog.000008
-rw-r-----. 1 mysql mysql         6 9 9 20:44 mysqld.pid
-rw-r-----. 1 mysql mysql  12582912 9 9 20:44 ibtmp1
-rw-r-----. 1 mysql mysql  10485760 9 9 20:44 ibdata1
-rw-r-----. 1 mysql mysql 104857600 9 9 20:44 ib_logfile0
-rw-r-----. 1 mysql mysql    107011 9 9 20:54 mysql.err

pem文件权限太多可能会出现如下错误

[mysql@localhost mysql]$ mysql -h 10.11.13.19 -P 3306 -u root -pabcd123 --ssl-cert=/mysqldata/mysql/client-cert.pem --ssl-key=/mysqldata/mysql/client-key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed

[root@localhost mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.. SUCCESS!

[mysql@localhost mysql]$ mysql -h 10.11.13.19 -P 3306 -u root -pabcd123 --ssl-cert=/mysqldata/mysql/client-cert.pem --ssl-key=/mysqldata/mysql/client-key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, 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.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          4
Current database:
Current user:           root@10.11.13.19
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             10.11.13.19 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 59 sec

Threads: 2  Questions: 11  Slow queries: 0  Opens: 108  Flush tables: 1  Open tables: 101  Queries per second avg: 0.186
--------------

创建用户限制用户必须用ssl登录
mysql> create user 'jy'@'%' identified by "123";
Query OK, 0 rows affected (0.02 sec)

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

虽然要求用ssl但是还是可以使用密码登录，是因为mysql.user表中的ssl_type=ANY
[mysql@localhost ~]$  mysql -h 10.11.13.19 -P 3306 -u jy -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.26-log Source distribution

Copyright (c) 2000, 2019, 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.26, for Linux (x86_64) using  EditLine wrapper

Connection id:          7
Current database:
Current user:           jy@10.11.13.19
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.26-log Source distribution
Protocol version:       10
Connection:             10.11.13.19 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    gb2312
Conn.  characterset:    gb2312
TCP port:               3306
Uptime:                 50 min 18 sec

Threads: 2  Questions: 69  Slow queries: 0  Opens: 139  Flush tables: 1  Open tables: 132  Queries per second avg: 0.022
--------------

mysql> select * from user where user='jy'\G
ERROR 1046 (3D000): No database selected
mysql> select * from mysql.user where user='root'\G
*************************** 1. row ***************************
                  Host: %
                  User: jy
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: ANY
            ssl_cipher:
           x509_issuer:
          x509_subject:
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *1AA476D99C1600C9D984E248FBF2FDE3A0BB256E
      password_expired: N
 password_last_changed: 2022-03-21 16:27:48
     password_lifetime: NULL
        account_locked: N
1 row in set (0.00 sec)

修改用户jy的ssl类型为x509
mysql> alter user 'jy'@'%' require x509
    -> ;
Query OK, 0 rows affected (0.01 sec)

在不指定ssl密钥时就不能登录了
[mysql@localhost ~]$ mysql -h 10.11.13.19 -P 3306 -u jy -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'jy'@'10.11.13.19' (using password: YES)
或
[mysql@localhost ~]$  mysql -h 10.11.13.19 -P 3306 -u jy -p123 --ssl
mysql: [Warning] Using a password on the command line interface can be insecure.
WARNING: --ssl is deprecated and will be removed in a future version. Use --ssl-mode instead.
ERROR 1045 (28000): Access denied for user 'jy'@'10.11.13.19' (using password: YES)

指定ssl密钥进行登录
[mysql@localhost ~]$  mysql -h 10.11.13.19 -P 3306 -u jy -p123 --ssl-cert=/mysqldata/mysql/client-cert.pem --ssl-key=/mysqldata/mysql/client-key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.26-log Source distribution

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

如果指定ssl密钥进行登录时不指定密钥完整路径时会出现如下错误
[mysql@localhost ~]$  mysql -h 10.11.13.19 -P 3306 -u jy -p123 --ssl-cert=client-cert.pem --ssl-key=client-key.pem
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [ERROR] SSL error: Unable to get certificate from 'client-cert.pem'
ERROR 2026 (HY000): SSL connection error: Unable to get certificate