java通过ssl连接mysql(linux)

环境:
Os:centos 7
mysql:5.7.29
java运行客户端:windows10

1.查看服务器的证书文件
mysql数据data目录下

[root@localhost data]# pwd
/opt/mysql57/data
[root@localhost data]# ls -al *.pem
-rw-------. 1 mysql mysql 1676 Oct 30 05:22 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 30 05:22 ca.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 30 05:22 client-cert.pem
-rw-------. 1 mysql mysql 1676 Oct 30 05:22 client-key.pem
-rw-------. 1 mysql mysql 1676 Oct 30 05:22 private_key.pem
-rw-r--r--. 1 mysql mysql  452 Oct 30 05:22 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 Oct 30 05:22 server-cert.pem
-rw-------. 1 mysql mysql 1680 Oct 30 05:22 server-key.pem

我们只需要如下3个文件
ca.pem
client-cert.pem
client-key.pem

keytool只需要ca.pem,其他工具如navicat这三个文件都需要,我们把这三个文件拷贝到临时目录.

[root@localhost /]# mkdir -p /tmp/ca
[root@localhost data]# cp ca.pem /tmp/ca/
[root@localhost data]# cp client-cert.pem /tmp/ca/
[root@localhost data]# cp client-key.pem /tmp/ca/

2.生成证书(前提需要安装好jdk,keytool是jdk自带的)

[root@localhost ca]# keytool -importcert -alias MySQLCACert -file ca.pem -keystore truststore -storepass 123456
Owner: CN=MySQL_Server_5.7.29_Auto_Generated_CA_Certificate
Issuer: CN=MySQL_Server_5.7.29_Auto_Generated_CA_Certificate
Serial number: 1
Valid from: Mon Oct 30 05:22:31 EDT 2023 until: Thu Oct 27 05:22:31 EDT 2033
Certificate fingerprints:
         SHA1: F9:70:21:7F:D7:B6:86:86:7A:F7:98:37:07:C8:81:C2:9D:0A:F7:50
         SHA256: 98:05:FF:3B:B7:E1:EE:F9:3C:60:65:32:3C:58:04:6D:1B:97:58:FC:D2:D3:B2:57:B4:25:B9:ED:AD:9C:67:88
Signature algorithm name: SHA256withRSA
Subject Public Key Algorithm: 2048-bit RSA key
Version: 3

Extensions: 

#1: ObjectId: 2.5.29.19 Criticality=true
BasicConstraints:[
  CA:true
  PathLen:2147483647
]

Trust this certificate? [no]:  y
Certificate was added to keystore

这个时候可以看到生成了truststore文件

[root@localhost ca]# ls -al
total 20
drwxr-xr-x.  2 root root   83 Oct 30 21:45 .
drwxrwxrwt. 21 root root 4096 Oct 30 21:40 ..
-rw-r--r--.  1 root root 1112 Oct 30 05:56 ca.pem
-rw-r--r--.  1 root root 1112 Oct 30 05:56 client-cert.pem
-rw-------.  1 root root 1676 Oct 30 05:56 client-key.pem
-rw-r--r--.  1 root root  846 Oct 30 21:45 truststore

同时也解压可以看到这里该证书的有效期是10年
Valid from: Mon Oct 30 05:22:31 EDT 2023 until: Thu Oct 27 05:22:31 EDT 2033

这里设置的密码是123456,后面的jdbc连接需要用到这个密码

3.成之后可以查看一下是否生成成功,操作命令

[root@localhost ca]# keytool -list -keystore truststore
Enter keystore password:  
Keystore type: JKS
Keystore provider: SUN

Your keystore contains 1 entry

mysqlcacert, Oct 30, 2023, trustedCertEntry, 
Certificate fingerprint (SHA-256): 98:05:FF:3B:B7:E1:EE:F9:3C:60:65:32:3C:58:04:6D:1B:97:58:FC:D2:D3:B2:57:B4:25:B9:ED:AD:9C:67:88

4.把该truststore文件下载到客户端
我这里下载放在如下目录:
C:\linux_ca

5.mysql服务器器创建ssl用户和相应的数据库

mysql> grant all privileges on *.* to 'ssltest'@'%' identified by 'mysql' require ssl;
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> create database db_test;
Query OK, 1 row affected (0.04 sec)

6.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();
    }
}

说明:
1.在linux服务器上生成truststore文件或是在客户端使用ca.pem生成的truststore文件,java程序都可以连接.