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