mysql以SSL加密的方式登录

一、默认加密设置

复制#1.MySQL服务器是否以--ssl选项启动，YES表示当前服务器支持SSL加密

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

#2.检查MySQL服务器require_secure_transport系统变量，如果为ON启用此变量后，服务器仅允许使用TLS/SSL加密的TCP/IP连接。

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

二、强制客户端使用SSL加密连接

复制#方法1:修改my.cnf并重启mysql
require_secure_transport=ON
#方法2.配置系统环境变量(推荐)
mysql> set global require_secure_transport=ON;
Query OK, 0 rows affected (0.00 sec)

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

#3.以ssl方式登录root用户

mysql -uroot -p --ssl-mode=require

#4.使用\s命令查看(SSL:Cipher in use is ECDHE-RSA-AES128-GCM-SHA256)

mysql> \s
--------------

mysql  Ver 14.14 Distrib 5.7.33, for el7 (x86_64) using  EditLine wrapper

Connection id:          206
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.33-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
UNIX socket:            /tmp/mysql.sock
Uptime:                 27 min 59 sec

Threads: 7  Questions: 544  Slow queries: 2  Opens: 134  Flush tables: 1  Open tables: 127  Queries per second avg: 0.324
--------------

#5.创建kht用户并测试
create user 'kht' identified by 'kht123' require SSL;(优先级高,即使全局关闭，也必须以加密的方式登录)
create user 'kht1' identified by 'kht123' require NONE;

mysql> create user 'kht' identified by 'kht123' require SSL;
Query OK, 0 rows affected (0.05 sec)

mysql> grant all on *.* to 'kht';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
#此时，仅使用 mysql -u kht -p无法登录
[root@kht130 ~]# mysql -u kht -p
Enter password:
ERROR 1045 (28000): Access denied for user 'kht'@'localhost' (using password: YES)
#使用以下命令登录成功
[root@kht130 ~]# mysql -u kht -p --ssl-mode=require
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 274
Server version: 5.7.33-log MySQL Community Server (GPL)
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
You are enforcing ssl conection via unix socket. Please consider
switching ssl off as it does not make connection via unix socket
any more secure.