使用客户端证书登录MySQL登录MySQL具有安全性高、不用输入密码的优点，这里说明生成证书和登录的过程。
实验环境是Linux上的的MySQL 8.0.31社区版。
生成证书
使用openssl req创建X.509证书，下面的命令创建有效期10年的私钥，使用man req可以查询这个命令的帮助。
oracle@yao mysql$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout yao1-key.pem -out yao1-req.pem Generating a 2048 bit RSA private key ............................................................................................+++ ......+++ writing new private key to 'yao1-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) []: Locality Name (eg, city) [Default City]: Organization Name (eg, company) [Default Company Ltd]: Organizational Unit Name (eg, section) []: Common Name (eg, your name or your server's hostname) []: Email Address []: Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
检查刚刚生成的证书：
oracle@yao mysql$ ll *.pem
-rw-r--r--. 1 oracle oinstall 1704 12月 8 18:57 yao1-key.pem
-rw-r--r--. 1 oracle oinstall 952 12月 8 18:57 yao1-req.pem
这两个文件是私钥和请求证书。
下面的命令生成文件名是yao1-cert.pem的公钥：
oracle@yao mysql$ sudo openssl x509 -req -in yao1-req.pem -days 3650 -CA /u01/mysql/ca.pem -CAkey /u01/mysql/ca-key.pem -set_serial 01 -out yao1-cert.pem Signature ok subject=/C=CN/L=Default City/O=Default Company Ltd Getting CA Private Key
校验公钥的有效性：
oracle@yao mysql$ openssl verify -CAfile /u01/mysql/ca.pem /u01/mysql/server-cert.pem yao1-cert.pem /u01/mysql/server-cert.pem: OK yao1-cert.pem: OK
创建只有证书没有密码的账号
相应命令如下：
mysql> CREATE USER yao1 REQUIRE SUBJECT '/C=CN/L=Default City/O=Default Company Ltd'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> select * from component; +--------------+--------------------+-------------------------------------+ | component_id | component_group_id | component_urn | +--------------+--------------------+-------------------------------------+ | 1 | 1 | file://component_validate_password | | 2 | 2 | file://component_log_filter_dragnet | +--------------+--------------------+-------------------------------------+ 2 rows in set (0.00 sec) mysql> uninstall component 'file://component_validate_password'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER yao1 REQUIRE SUBJECT '/C=CN/L=Default City/O=Default Company Ltd'; Query OK, 0 rows affected (0.00 sec)
需要卸载component_validate_password组件后才能创建无密码的账号。
使用证书登录mysql和mysqlsh
登录mysql，在输入密码处直接回车即可登录
oracle@yao mysql$ mysql -uyao1 --ssl-cert yao1-cert.pem --ssl-key yao1-key.pem -p --ssl_ca=/u01/mysql/ca.pem Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3685 Server version: 8.0.31 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 connection via unix socket. Please consider switching ssl off as it does not make connection via unix socket any more secure. mysql> \s -------------- mysql Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 3685 Current database: Current user: yao1@localhost SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.31 MySQL Community Server - GPL Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /u01/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 3 days 5 hours 6 min 47 sec Threads: 5 Questions: 11001 Slow queries: 0 Opens: 4565 Flush tables: 3 Open tables: 319 Queries per second avg: 0.039 -------------- mysql>
登录到MySQL Shell，到密码处直接回车即可：
oracle@yao mysql$ mysqlsh mysql://yao1@127.0.0.1 --ssl-cert yao1-cert.pem --ssl-key yao1-key.pem Please provide the password for 'yao1@127.0.0.1': Save password for 'yao1@127.0.0.1'? [Y]es/[N]o/Ne[v]er (default No): MySQL Shell 8.0.31 Copyright (c) 2016, 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 '\?' for help; '\quit' to exit. Creating a Classic session to 'yao1@127.0.0.1?ssl-cert=yao1-cert.pem&ssl-key=yao1-key.pem' Fetching schema names for auto-completion... Press ^C to stop. Your MySQL connection id is 3686 Server version: 8.0.31 MySQL Community Server - GPL No default schema selected; type \use <schema> to set one. MySQL 127.0.0.1:3306 ssl JS >
