526互联

mysql使用自制(self signed)证书(ssl)不使用默认安装的证书

发布时间 2023-10-31 16:16:46作者: slnngk

环境:
OS:Centos 7
mysql:5.7.29

 

1.生成服务器密钥和证书(有效期30年)


$ openssl req -x509 -days 10800 -newkey rsa:1024 -keyout server-key.pem -out server-cert.pem -subj '/DC=com/DC=example/CN=server' -passout pass:qwerty

$ openssl rsa -in server-key.pem -out server-key.pem -passin pass:qwerty -passout pass:


 

2.生成客户端密钥和证书


$ openssl req -x509 -days 10800 -newkey rsa:1024 -keyout client-key.pem -out client-cert.pem -subj '/DC=com/DC=example/CN=client' -passout pass:qwerty

$ openssl rsa -in client-key.pem -out client-key.pem -passin pass:qwerty -passout pass:


 

3.将客户端和服务器证书合并到CA证书文件中

$ cat server-cert.pem client-cert.pem > ca.pem

 

这个时候生成的文件如下：

[root@localhost ca_new]# ls -al
total 20
drwxr-xr-x. 2 root root  110 Oct 31 03:55 .
drwxr-xr-x. 5  631  503  207 Oct 31 02:19 ..
-rw-r--r--. 1 root root 1718 Oct 31 03:55 ca.pem
-rw-r--r--. 1 root root  859 Oct 31 03:55 client-cert.pem
-rw-r--r--. 1 root root  887 Oct 31 03:55 client-key.pem
-rw-r--r--. 1 root root  859 Oct 31 03:54 server-cert.pem
-rw-r--r--. 1 root root  887 Oct 31 03:54 server-key.pem

 

4.拷贝到mysql配置的证书目录

[root@localhost ca_new]# cp *.pem /opt/mysql57/myca/

修改权限

[root@localhost ca_new]# chown -R mysql:mysql /opt/mysql57/myca/

 

mysql证书的配置如下：

ssl-ca=/opt/mysql57/myca/ca.pem
ssl-cert=/opt/mysql57/myca/server-cert.pem
ssl-key=/opt/mysql57/myca/server-key.pem

 

5.重启动数据库
/opt/mysql57/bin/mysqld_safe --defaults-file=/opt/mysql57/conf/my.cnf --user=mysql &

 

6.生成java使用的truststore文件

[root@localhost tmp]# cp /opt/mysql57/myca/ca.pem /tmp/
[root@localhost tmp]# cd /tmp/

[root@localhost tmp]# keytool -importcert -alias MySQLCACert -file ca.pem -keystore truststore -storepass 123456
Owner: CN=server, DC=example, DC=com
Issuer: CN=server, DC=example, DC=com
Serial number: da72ea45b6db0b4f
Valid from: Tue Oct 31 03:54:26 EDT 2023 until: Mon May 26 03:54:26 EDT 2053
Certificate fingerprints:
         SHA1: 79:AA:1B:33:AE:54:C9:35:D9:4A:0A:4F:CD:06:27:74:56:65:83:41
         SHA256: E1:1F:4A:84:98:03:F2:2A:4B:67:A3:CF:D9:47:0A:CE:10:50:B6:58:53:A0:DB:C8:5B:BD:FA:07:00:26:83:81
Signature algorithm name: SHA256withRSA
Subject Public Key Algorithm: 1024-bit RSA key (weak)
Version: 3

 

7.navicate(15版本)连接

 

8.java程序连接

package ssltest;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;


public class mytest_linux {

    Connection con;
    public static String user;
    public static String password;

    public void getConnection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("数据库驱动加载成功");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        user = "ssltest";
        password = "mysql";  // 填自己的密码
        try {
            //con = DriverManager.getConnection("jdbc:mysql://192.168.1.105:13306/db_test?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8&useSSL=true", user, password);

            con = DriverManager.getConnection("jdbc:mysql://192.168.1.108:13306/db_test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&verifyServerCertificate=true&requireSSL=true&sslMode=verify_ca&trustCertificateKeyStoreUrl=file:C:/linux_ca/truststore&trustCertificateKeyStorePassword=123456", user, password);
            
            
            System.out.println("数据库连接成功");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        mytest_linux c = new mytest_linux();
        c.getConnection();
    }
}