1.安装证书
权限不足会失败
mysql_ssl_rsa_setup --datadir=/var/lib/mysql -vvv
chmod 0777 *.pem 生成如下
[root@10-6-8-197 mysql]# ll *.pem
-rwxrwxrwx 1 root root 1675 Dec 16 11:31 ca-key.pem
-rwxrwxrwx 1 root root 1107 Dec 16 11:31 ca.pem
-rwxrwxrwx 1 root root 1107 Dec 16 11:31 client-cert.pem
-rwxrwxrwx 1 root root 1675 Dec 16 11:31 client-key.pem
-rwxrwxrwx 1 root root 1675 Dec 16 11:31 private_key.pem
-rwxrwxrwx 1 root root 451 Dec 16 11:31 public_key.pem
-rwxrwxrwx 1 root root 1107 Dec 16 11:31 server-cert.pem
-rwxrwxrwx 1 root root 1679 Dec 16 11:31 server-key.pem
2.修改/etc/my.cnf 在 [mysqld] 下面添加
ssl-ca = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key = /var/lib/mysql/server-key.pem3.查看
systemctl restart mysqld
mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'AAbb1122!!';
mysql> show global variables like '%ssl%';
+-------------------------------------+--------------------------------+
| Variable_name | Value |
+-------------------------------------+--------------------------------+
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| performance_schema_show_processlist | OFF |
| ssl_ca | /var/lib/mysql/ca.pem |
| ssl_capath | |
| ssl_cert | /var/lib/mysql/server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | /var/lib/mysql/server-key.pem |
+-------------------------------------+--------------------------------+
25 rows in set (0.00 sec)
4.创建用户可以登录
创建用户
create user 'user0001'@'%' identified by 'User0001!';
GRANT ALL PRIVILEGES ON *.* TO 'user0001'@'%';
FLUSH PRIVILEGES;
ALTER USER 'user0001'@'%' IDENTIFIED WITH mysql_native_password BY 'User0001!';设置ssl
alter user user0001@'%' require ssl; 取消ssl
alter user user0001@'%' require none;
5.查看用户是否使用ssl
mysql> select user,host,ssl_type,ssl_cipher from mysql.user ;
+------------------+-----------+----------+------------------------+
| user | host | ssl_type | ssl_cipher |
+------------------+-----------+----------+------------------------+
| user0001 | % | ANY | 0x |
| mysql.infoschema | localhost | | 0x |
| mysql.session | localhost | | 0x |
| mysql.sys | localhost | | 0x |
| root | localhost | | 0x |
+------------------+-----------+----------+------------------------+
5 rows in set (0.00 sec)
6.总结
1、MySQL 默认是开启SSL连接，如果强制用户使用SSL连接，那么应用程序的配置也需要明确指定SSL相关参数，否则程序会报错。
2、虽然SSL方式使得安全性提高了，但是相对地使得QPS也降低23%左右。所以要谨慎选择：
- 2.1、对于非常敏感核心的数据，或者QPS本来就不高的核心数据，可以采用SSL方式保障数据安全性；
- 2.2、对于采用短链接、要求高性能的应用，或者不产生核心敏感数据的应用，性能和可用性才是首要，建议不要采用SSL方式；
