MySQL开启SSL连接

查看mysql是否已开启ssl

show variables like 'have%ssl%';

生成证书

生成 CA 私钥

openssl genrsa 2048 > ca-key.pem

通过 CA 私钥生成数字证书

openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem

生成服务器证书

创建 MySQL 服务器 私钥和请求证书

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

将生成的私钥转换为 RSA 私钥文件格式

openssl rsa -in server-key.pem -out server-key.pem

用CA 证书来生成一个服务器端的数字证书

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

生成客户端证书

创建客户端的 RSA 私钥和数字证书

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

将生成的私钥转换为 RSA 私钥文件格式

openssl rsa -in client-key.pem -out client-key.pem

用CA 证书来生成一个客户端的数字证书

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

修改my.ini

在mysqld中加入：

# 开启ssl
#ssl-ca=D:/mysql/ssl/ca-cert.pem
#ssl-cert=D:/mysql/ssl/server-cert.pem
#ssl-key=D:/mysql/ssl/server-key.pem
#require_secure_transport = ON

重启mysql服务，此时navicat等客户端工具都连不上mysql了。

navicat 设置

需要勾选上SSL并选择客户端对应的证书文件：

PHP 设置

MySQLi

$db = mysqli_init();
if (!$db) exit('db init error');
mysqli_options($db, MYSQLI_OPT_SSL_VERIFY_SERVER_CERT, true);
$db->ssl_set(
    'D:/mysql/ssl/client-key.pem',
    'D:/mysql/ssl/client-cert.pem',
    'D:/mysql/ssl/ca-cert.pem',
    null,
    null
);
if (!$db->real_connect($dbhost, $dbuser, $dbpw, $dbname,config::get("database.port",'3306'), null, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT)) {
    exit('db conect error')
}
$result = mysqli_query($db, "SELECT * FROM projects LIMIT 0, 10");
while ($row = mysqli_fetch_assoc($result)) {
    var_dump($row);
};

PDO

$options[PDO::MYSQL_ATTR_SSL_KEY] = 'D:/mysql/ssl/client-key.pem';
$options[PDO::MYSQL_ATTR_SSL_CERT] = 'D:/mysql/ssl/client-cert.pem';
$options[PDO::MYSQL_ATTR_SSL_CA] = 'D:/mysql/ssl/ca-cert.pem';
$options[PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT] = false;
$dbh = new PDO('mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8', $user, $password, $options);