mysql ssl 配置

mysql5.2-5.6版本配置

不推荐: mysql5.7 或者 8 版本由 mysql_ssl_rsa_setup 自动生成, 在使用程序进行ssl链接的时候会有FQDN问题,因为这个命令没有办法指定域名, CN中默认生成的一串值(我没有找到修改的办法)
使用手动创建证书明确CN信息, 链接时使用域名不会有问题

1 创建证书

cd /opt/mysqlssl/
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

# Country Name (2 letter code) [XX]:CN
# State or Province Name (full name) []:ShangHai
# Locality Name (eg, city) [Default City]:ShangHai
# Organization Name (eg, company) [Default Company Ltd]:test
# Organizational Unit Name (eg, section) []:test
# Common Name (eg, your name or your server's hostname) []:ca.damain.com
# Email Address []:

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout server-key.pem -out server-req.pem

# Country Name (2 letter code) [XX]:CN
# State or Province Name (full name) []:ShangHai
# Locality Name (eg, city) [Default City]:ShangHai
# Organization Name (eg, company) [Default Company Ltd]:test
# Organizational Unit Name (eg, section) []:test
# Common Name (eg, your name or your server's hostname) []:*.damain.com
# Email Address []:
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Signature ok
# subject=/C=cn/ST=ShangHai/L=ShangHai/O=test/OU=test/CN=server.damain.com
# Getting CA Private Key

openssl req -newkey rsa:2048 -days 3600 -nodes -keyout client-key.pem -out client-req.pem

# Country Name (2 letter code) [XX]:CN
# State or Province Name (full name) []:ShangHai
# Locality Name (eg, city) [Default City]:ShangHai
# Organization Name (eg, company) [Default Company Ltd]:test
# Organizational Unit Name (eg, section) []:test
# Common Name (eg, your name or your server's hostname) []:client.damain.com
# Email Address []:
# Please enter the following 'extra' attributes
# to be sent with your certificate request
# A challenge password []:
# An optional company name []:

openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem
openssl verify -CAfile ca.pem server-cert.pem client-cert.pem

# server-cert.pem: OK
# client-cert.pem: OK

chown -R mysql:mysql /opt/mysqlssl/

2 修改 my.cnf

# 添加证书配置
[mysqld]
ssl-ca=/opt/mysqlssl/ca.pem
ssl-cert=/opt/mysqlssl/server-cert.pem
ssl-key=/opt/mysqlssl/server-key.pem

3 重启

service mysqld restart

4 创建一个需要证书的用户

不设置 require ssl 的普通用户也可以使用ssl

5.7以上版本

mysql>create user 'testssl2'@'%' identified by '123456';
mysql>ALTER USER 'testssl2'@'%' REQUIRE SSL;

5.7以下版本

grant all on *.* to 'testssl'@'%' identified by '123456' require ssl;

5 链接

mysql -utestssl -h127.0.0.1 -p --ssl-key=/opt/mysqlssl/client-key.pem --ssl-cert=/opt/mysqlssl/client-cert.pem

说明

证书

文件	说明
ca-key.pem	CA私钥
ca.pem	自签名的CA证书
client-key.pem	连接服务器提供的私钥
client-cert.pem	连接服务器需要提供的证书
server-key.pem	服务器端私钥
server-client.pem	服务器端证书
pulibc_key.pem	密钥对公钥
private_key.pem	密钥对私钥

程序链接

ssl = {
    "ca": "ssl/ca.pem", "key": "ssl/client-key.pem", "cert": "ssl/client-cert.pem"
}
conn = pymysql.connect(ssl=ssl)