参数have_ssl为 YES
| have_openssl | YES || have_ssl | YES || ssl_ca | ca.pem || ssl_capath | || ssl_cert | server-cert.pem || ssl_cipher | || ssl_crl | || ssl_crlpath | || ssl_key | server-key.pem |
参数解释：
参数
解释
have_openssl
和have_ssl一样
have_ssl
YES: 启用SSL
DISABLED: 已经编译了SSL功能，但是没有开启
ssl_ca
CA文件，默认是ca.pem
ssl_capath
CA文件路径，默认为空。mysqld会从datadir查找ca.pem
ssl_cert
做事端证书，默认server-cert.pem.
ssl_cipher
SSL加密办法，默认为空
ssl_crl
certificate revocation lists文件，默认为空
ssl_crlpath
certificate revocation lists文件路径，默认为空
ssl_key
做事端证书私钥，默认server-key.pem.
server端启用SSL，须要ca.pem, server-cert.pem, server-key.pem 3个文件。客户端须要有ca.pem, client-cert.pem, client-key.pem 3个文件。
2 客户端SSL利用mysql客户端连接到做事端时，默认会利用加密通信，上岸后输入 \s, 或者show status like ‘ssl_cipher’，如果看Cipher信息，解释连接已经开启加密通信
[root@box1 mysql]# mysql -uroot -h127.0.0.1 -P3306 -phelloServer version: 5.7.32 MySQL Community Server (GPL)...Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \s--------------mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapperConnection id:23Current database:Current user:root@localhostSSL:Cipher in use is ECDHE-RSA-AES128-GCM-SHA256Current pager:stdoutUsing outfile:''Using delimiter:;Server version:5.7.32 MySQL Community Server (GPL)Protocol version:10Connection:127.0.0.1 via TCP/IPServer characterset:latin1mysql> show status like 'ssl_cipher';+---------------+-----------------------------+| Variable_name | Value |+---------------+-----------------------------+| Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 |
2.1 客户端禁用ssl通信
mysql客户端上岸时加上 --ssl-mode=disabled禁用TLS通信
mysql -uroot -h127.0.0.1 -P3306 -phello --ssl-mode=disabledmysql> \s--------------mysql Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using EditLine wrapperConnection id:25Current database:Current user:root@localhostSSL:Not in useCurrent pager:stdoutmysql> show status like 'ssl_cipher';+---------------+-------+| Variable_name | Value |+---------------+-------+| Ssl_cipher | |+---------------+-------+
2.2客户端哀求验证做事端证书
客户端可以哀求验证做事真个证书。
mysql客户端将ssl-mode参数设置为verify_ca或verify_identity, 同时须要供应用来署名的ca证书。
verify_ca的浸染，是为了让做事端证明，他的证书是客户端参数中的指定的ca署名的。其他做事器无法伪装。
mysql -uroot -h127.0.0.1 -P3306 -phello --ssl-mode=verify_ca --ssl-ca=ca.pem show status like 'ssl_cipher';+---------------+-----------------------------+| Variable_name | Value |+---------------+-----------------------------+| Ssl_cipher | ECDHE-RSA-AES128-GCM-SHA256 |
如果证书验证不通过，客户端连接会报错。做事端日志中也能看到干系报错信息。
### clientroot@box1 mysql]# mysql -uroot -h127.0.0.1 -P3306 -phello --ssl-mode=verify_ca --ssl-ca=/data/mysql01/data/ca.pemmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)### server[root@box1 mysql]# tail -1 /var/log/mysqld.log2021-03-31T23:34:20.461350Z 28 [Note] Bad handshake3 做事端哀求验证客户端利用SSL登录
虽然做事端开启了SSL，但是默认情形下用户可以选择启用或不启用加密通信。做事端可以逼迫哀求客户端利用加密通信，也可以哀求客户端证明自己的身份。这可以在创建用户的时候指定。
CREATE USER 'user_1'@'%' identified by 'hello' require ssl;CREATE USER 'user_2'@'%' identified by 'hello' require x509;CREATE USER 'user_3'@'%' identified by 'hello' require cipher 'ECDHE-RSA-AES256-GCM-SHA384';CREATE USER 'user_4'@'%' identified by 'hello' require cipher 'ECDHE-ECDSA-AES256-GCM-SHA384';CREATE USER 'user_5'@'%' identified by 'hello' require issuer 'www.dtstack.com' ;CREATE USER 'user_6'@'%' identified by 'hello' require subject '/CN=client.dtstack.com';
上面创建的用户中，
user_1必须开启SSL加密才能连接到做事器
user_2须要利用x509证书格式
user_3须要利用cipher ECDHE-RSA-AES256-GCM-SHA384
user_4须要利用cipher ECDHE-ECDSA-AES256-GCM-SHA384
user_5须要利用www.dtstack.com签发的证书
user_6须要利用subject为client.dtstack.com的证书
4测试用户信息select user,host, ssl_type, ssl_cipher, x509_issuer, x509_subject from mysql.user where user like 'user%';+--------+------+-----------+-------------------------------+-----------------+------------------------+| user | host | ssl_type | ssl_cipher | x509_issuer | x509_subject |+--------+------+-----------+-------------------------------+-----------------+------------------------+| user_1 | % | ANY | | | || user_2 | % | X509 | | | || user_3 | % | SPECIFIED | ECDHE-RSA-AES256-GCM-SHA384 | | || user_4 | % | SPECIFIED | ECDHE-ECDSA-AES256-GCM-SHA384 | | || user_5 | % | SPECIFIED | | www.dtstack.com | client.dtstack.com || user_6 | % | SPECIFIED | | | /CN=client.dtstack.com |+--------+------+-----------+-------------------------------+-----------------+------------------------+
4.1逼迫SSL
user_1上岸，如果不开启ssl-mode，上岸报错。利用SSL才能上岸
[root@box1 pki]# mysql -uuser_1 -phello -h127.0.0.1 --ssl-mode=disabledmysql: [Warning] Using a password on the command line interface can be insecure.ERROR 1045 (28000): Access denied for user 'user_1'@'localhost' (using password: YES)[root@box1 pki]# mysql -uuser_1 -phello -h127.0.0.1...Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> \s...Current user:user_1@localhostSSL:Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
4.2逼迫利用客户端证书
user_2须要x509证书才能上岸
[root@box1 pki]# mysql -uuser_2 -phello -h127.0.0.1ERROR 1045 (28000): Access denied for user 'user_2'@'localhost' (using password: YES)[root@box1 pki]# mysql -uuser_2 -phello -h127.0.0.1 --ssl-mode=requiredERROR 1045 (28000): Access denied for user 'user_2'@'localhost' (using password: YES)[root@box1 pki]# mysql -uuser_2 -phello -h127.0.0.1 --ssl-cert=client-cert.pem --ssl-key=client-key.pem...Welcome to the MySQL monitor. Commands end with ; or \g.mysql> \s...Current user:user_2@localhostSSL:Cipher in use is ECDHE-RSA-AES128-GCM-SHA256
4.3 逼迫指定客户端cipher
user_3指定了cipher, 无法上岸。 可以从做事端alert.log查看上岸失落败的缘故原由。
[root@box1 pki]# mysql -uuser_3 -pxhello -h127.0.0.1 --ssl-mode=required --ssl-cert=client-cert.pem --ssl-key=client-key.pemERROR 1045 (28000): Access denied for user 'user_3'@'localhost' (using password: YES)### 做事端日志[root@box1 mysql]# tail -1 /var/log/mysqld.log2021-04-01T00:07:44.216859Z 51 [Note] X509 ciphers mismatch: should be 'ECDHE-RSA-AES256-GCM-SHA384' but is 'ECDHE-RSA-AES128-GCM-SHA256'2021-04-01T00:07:44.216915Z 51 [Note] Access denied for user 'user_3'@'localhost' (using password: YES)
解释了cipher不知足哀求。my.cnf增加ssl_ciphper参数，重启做事
[root@box1 mysql]# tail -1 /etc/my.cnfssl_cipher=ECDHE-RSA-AES256-GCM-SHA384[root@box1 mysql]# service mysqld restartRedirecting to /bin/systemctl restart mysqld.service
-- 再次考试测验上岸，可以看到SSL 利用的ciper变成了 ECDHE-RSA-AES256-GCM-SHA384[root@box1 pki]# mysql -uuser_3 -phello -h127.0.0.1 --ssl-mode=required --ssl-cert=client-cert.pem --ssl-key=client-key.pem...mysql> \sCurrent user:user_3@localhostSSL:Cipher in use is ECDHE-RSA-AES256-GCM-SHA384
4.4验证客户端证书的subject 和 issuer
### client[root@box1 pki]# mysql -uuser_5 -phello -h127.0.0.1 --ssl-mode=required --ssl-cert=client-cert.pem --ssl-key=client-key.pemERROR 1045 (28000): Access denied for user 'user_5'@'localhost' (using password: YES)### server[root@box1 mysql]# tail -2 /var/log/mysqld.log2021-04-01T01:17:21.692560Z 6 [Note] X509 issuer mismatch: should be 'www.dtstack.com' but is '/C=CN/ST=HZ/L=ZJ/O=lazybug CO/OU=freecity/CN=www.dtstack.com/emailAddress=junda@dtstack.com'2021-04-01T01:17:21.692607Z 6 [Note] Access denied for user 'user_5'@'localhost' (using password: YES)### client[root@box1 pki]# mysql -uuser_6 -phello -h127.0.0.1 --ssl-mode=required --ssl-cert=client-cert.pem --ssl-key=client-key.pemERROR 1045 (28000): Access denied for user 'user_6'@'localhost' (using password: YES)### server[root@box1 mysql]# tail -2 /var/log/mysqld.log2021-04-01T01:20:41.297093Z 8 [Note] X509 subject mismatch: should be '/CN=client.dtstack.com' but is '/C=XX/L=Default City/O=Default Company Ltd'2021-04-01T01:20:41.297144Z 8 [Note] Access denied for user 'user_6'@'localhost' (using password: YES)
重新天生client证书，指定subject
[root@box1 pki]# openssl req -subj /CN=client.dtstack.com -newkey rsa:2048 -days 3600 \ -nodes -keyout client-key.pem -out client-req.pem writing new private key to 'client-key.pem'[root@box1 pki]# openssl rsa -in client-key.pem -out client-key.pemwriting RSA key[root@box1 pki]# openssl x509 -sha384 -req -in client-req.pem -days 3600 \> -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pemSignature oksubject=/CN=client.dtstack.comGetting CA Private Key
利用新的证书，就可以登录的数据库了：
[root@box1 pki]# mysql -uuser_6 -phello -h127.0.0.1 --ssl-mode=required --ssl-cert=client-cert.pem --ssl-key=client-key.pem...mysql> \sCurrent user:user_6@localhostSSL:Cipher in use is ECDHE-RSA-AES256-GCM-SHA384附录：SSL证书干系命令
天生证书的命令
# Create clean environmentrm -rf newcertsmkdir newcerts && cd newcerts# Create CA certificateopenssl genrsa 2048 > ca-key.pemopenssl req -new -x509 -nodes -days 3600 \ -key ca-key.pem -out ca.pem# Create server certificate, remove passphrase, and sign it# server-cert.pem = public key, server-key.pem = private keyopenssl req -newkey rsa:2048 -days 3600 \ -nodes -keyout server-key.pem -out server-req.pemopenssl rsa -in server-key.pem -out server-key.pemopenssl x509 -sha384 -req -in server-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem# Create client certificate, remove passphrase, and sign it# client-cert.pem = public key, client-key.pem = private keyopenssl req -subj /client.dtstack.com -newkey rsa:2048 -days 3600 \ -nodes -keyout client-key.pem -out client-req.pemopenssl rsa -in client-key.pem -out client-key.pemopenssl x509 -sha384 -req -in client-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem openssl x509 -req -in client-req.pem -days 3600 \ -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
查看证书信息
[root@box1 mysql]# openssl x509 -text -in server-cert.pem -nooutCertificate: Data: Version: 3 (0x2) Serial Number: 2 (0x2) Signature Algorithm: sha256WithRSAEncryption Issuer: CN=MySQL_Server_5.7.32_Auto_Generated_CA_Certificate Validity Not Before: Mar 31 12:14:46 2021 GMT Not After : Mar 29 12:14:46 2031 GMT Subject: CN=MySQL_Server_5.7.32_Auto_Generated_Server_Certificate Subject Public Key Info: Public Key Algorithm: rsaEncryption Public-Key: (2048 bit) Modulus:[root@box1 pki]# openssl x509 -text -in client-cert.pem -nooutCertificate: Data: Version: 1 (0x0) Serial Number: 1 (0x1) Signature Algorithm: sha384WithRSAEncryption Issuer: C=CN, ST=HZ, L=ZJ, O=lazybug CO, OU=freecity, CN=www.dtstack.com/emailAddress=junda@dtstack.com Validity Not Before: Apr 1 01:34:53 2021 GMT Not After : Feb 8 01:34:53 2031 GMT Subject: CN=client.dtstack.com Subject Public Key Info: Public Key Algorithm: rsaEncryption Public-Key: (2048 b验证证书
利用openssl verify命令验证证书有效性。CAfile是用于创建证书的ca文件。
[root@box1 pki]# openssl verify -CAfile ca.pem server-cert.pem client-cert.pemserver-cert.pem: OKclient-cert.pem: OK
如果ca文件和证书文件不匹配，验证会报错
[root@box1 pki]# openssl verify -CAfile /var/lib/backupca/ca.pem server-cert.pem client-cert.pemserver-cert.pem: C = CN, ST = ZJ, L = HZ, O = bugfree server, OU = land, CN = www.dtstack.com, emailAddress = server@dtstack.comerror 20 at 0 depth lookup:unable to get local issuer certificateclient-cert.pem: CN = client.dtstack.comerror 20 at 0 depth lookup:unable to get local issuer certificate
更多技能信息请查看云掣官网云掣YunChe - 可不雅观测运维专家 | 大数据运维托管 | 云MSP做事
