mysql5.7 启用SSL
服务器端启动配置
查看默认数据目录
# ls -l data/total 122944
-rw-r----- 1 mysql mysql 56 Apr 4 17:21 auto.cnf
-rw------- 1 mysql mysql 1676 Apr 4 17:21 ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Apr 4 17:21 ca.pem
-rw-r--r-- 1 mysql mysql 1112 Apr 4 17:21 client-cert.pem
-rw------- 1 mysql mysql 1680 Apr 4 17:21 client-key.pem
-rw-r----- 1 mysql mysql 436 Apr 4 17:21 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Apr 4 17:21 ibdata1
-rw-r----- 1 mysql mysql 50331648 Apr 4 17:21 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Apr 4 17:21 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Apr 4 17:23 ibtmp1
drwxr-x--- 2 mysql mysql 4096 Apr 4 17:21 mysql
-rw-r----- 1 mysql mysql 6 Apr 4 17:21 mysqld.pid
drwxr-x--- 2 mysql mysql 4096 Apr 4 17:21 performance_schema
-rw------- 1 mysql mysql 1680 Apr 4 17:21 private_key.pem
-rw-r--r-- 1 mysql mysql 452 Apr 4 17:21 public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Apr 4 17:21 server-cert.pem
-rw------- 1 mysql mysql 1680 Apr 4 17:21 server-key.pem
drwxr-x--- 2 mysql mysql 12288 Apr 4 17:21 sys连接mysql
# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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.查看ssl变量
mysql> show variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| have_openssl | YES |
| have_ssl | YES | # 启动ssl
| performance_schema_show_processlist | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+-------------------------------------+-----------------+
10 rows in set (0.00 sec)查看tls变量
mysql> SHOW GLOBAL VARIABLES LIKE 'tls_version';
+---------------+-----------------------+
| Variable_name | Value |
+---------------+-----------------------+
| tls_version | TLSv1,TLSv1.1,TLSv1.2 |
+---------------+-----------------------+
1 row in set (0.01 sec)查看连接是否加密
mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Ssl_cipher | |
+---------------+-------+
1 row in set (0.00 sec)查看当前连接
mysql> \s
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 7
Current database:
Current user: root@localhost
SSL: Not in use # 连接未加密
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.40-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 35 min 9 sec
Threads: 1 Questions: 33 Slow queries: 0 Opens: 131 Flush tables: 1 Open tables: 0 Queries per second avg: 0.015
--------------客户端配置
连接mysql
# /usr/local/mysql/bin/mysql -uroot -p --ssl-ca /data/apps/mysql/data/ca.pem --ssl-cert /data/apps/mysql/data/client-cert.pem --ssl-key /data/apps/mysql/data/client-key.pem --tls-version TLSv1.2 --ssl-mode=VERIFY_CA
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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.
mysql> 查看连接是否加密
mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+-----------------------------+
| Variable_name | Value |
+---------------+-----------------------------+
| Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 | # 默认加密
+---------------+-----------------------------+
1 row in set (0.01 sec)查看当前连接
mysql> \s
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 9
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.40-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 42 min 41 sec
Threads: 1 Questions: 43 Slow queries: 0 Opens: 134 Flush tables: 1 Open tables: 0 Queries per second avg: 0.016
--------------强制加密连接配置
my.cnf
[mysqld]
require_secure_transport=ON本地连接测试
# /usr/local/mysql/bin/mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.40-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, 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.
mysql> \s
--------------
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 13
Current database:
Current user: root@localhost
SSL: Not in use # 没有加密
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.40-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 19 min 5 sec
Threads: 2 Questions: 45 Slow queries: 0 Opens: 149 Flush tables: 1 Open tables: 0 Queries per second avg: 0.039
--------------远程连接测试
# /usr/local/mysql/bin/mysql -uwgs -h172.16.18.31 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.40-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.
mysql> \s
--------------
/usr/local/mysql/bin/mysql Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 12
Current database:
Current user: wgs@172.16.3.213
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 # 连接加密
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.40-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 172.16.18.31 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 18 min 34 sec
Threads: 2 Questions: 37 Slow queries: 0 Opens: 149 Flush tables: 1 Open tables: 0 Queries per second avg: 0.033
--------------禁用加密连接
# mysql -uwgs01 -h172.16.18.31 -p --ssl-mode=DISABLED
Enter password:
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.强制用户加密连接
创建用户
mysql> grant all privileges on *.* to wgs01@'%' identified by 'xxxxx' require ssl;or
mysql> alter user wgs01@'%' require ssl;加密连接mysql
# mysql -uwgs01 -h172.16.18.31 -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.40-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.
mysql> \s
--------------
mysql Ver 8.0.25 for Linux on x86_64 (MySQL Community Server - GPL)
Connection id: 9
Current database:
Current user: wgs01@172.16.3.213
SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.40-log MySQL Community Server (GPL)
Protocol version: 10
Connection: 172.16.18.31 via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3306
Binary data as: Hexadecimal
Uptime: 4 min 41 sec
Threads: 2 Questions: 23 Slow queries: 0 Opens: 122 Flush tables: 1 Open tables: 0 Queries per second avg: 0.081
--------------非加密连接
# mysql -uwgs01 -h172.16.18.31 -p --ssl-mode=DISABLED
Enter password:
ERROR 1045 (28000): Access denied for user 'wgs01'@'172.16.3.213' (using password: YES)参考文档
https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html
