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程序都可以连接.
