Connect to MySQL using SSL

Connect to MySQL using SSL
lac31
You can create self-signed certificates and install with a local copy of MySQL using the following scripts and command. You can use these scripts and commands to verify MySQL Secure Sockets Layer (SSL) connectivity at a JDBC-driver level. After you have verified MySQL SSL connectivity, you connect to 
CA Live API Creator
 using the verify URL that is printed.
The combination of MySQL, JDBC drivers, and certificates creates a complex ecosystem. This ecosystem can be time consuming to configure correctly and to make work.
Configure and Test MySQL/SSL
The following scripts assume OS/X on a Mac with OpenSSL 0.9.8zg:
 
ssltest.sh
#!/bin/bash sslDir=ssltest mkdir -p "${sslDir}" rm "${sslDir}"/* ls -l "${sslDir}" caCertFile="${sslDir}/ssltest-ca-cert.pem" caKeyFile="${sslDir}/ssltest-ca-key.pem" serverCertFile="${sslDir}/ssltest-server-cert.pem" serverKeyFile="${sslDir}/ssltest-server-key.pem" serverReqFile="${sslDir}/ssltest-server-req.pem" clientCertFile="${sslDir}/ssltest-client-cert.pem" clientKeyFile="${sslDir}/ssltest-client-key.pem" clientKeystoreFile="${sslDir}/ssltest-client-keystore.p12" clientReqFile="${sslDir}/ssltest-client-req.pem" clientP12KeystoreFile="${sslDir}/ssltest-client-keystore.p12" clientJKSKeystoreFile="${sslDir}/ssltest-client-keystore.jks" caSubj="/C=US/ST=SomeState/L=SomeTown/O=SSLTest/CN=sslca/[email protected]" serverSubj="/C=US/ST=SomeState/L=SomeTown/O=SSLTest/CN=sslserver/[email protected]" clientSubj="/C=US/ST=SomeState/L=SomeTown/O=SSLTest/CN=sslclient/[email protected]" # generate a CA (certificate authority) key openssl genrsa -out "${caKeyFile}" 2048 # create that CA certificate for self-siging openssl req \ -new \ -x509 \ -nodes \ -days 365 \ -key "${caKeyFile}" \ -out "${caCertFile}" \ -subj "${caSubj}" # create a request for a server certificate openssl req \ -newkey rsa:2048 \ -days 365 \ -nodes \ -keyout "${serverKeyFile}" \ -out "${serverReqFile}" \ -subj "${serverSubj}" # and generate the signed server certificate openssl x509 \ -req \ -in "${serverReqFile}" \ -days 365 \ -CA "${caCertFile}" \ -CAkey "${caKeyFile}" \ -set_serial 01 \ -out "${serverCertFile}" # create a request for a client certificate openssl req \ -newkey rsa:2048 \ -days 365 \ -nodes \ -keyout "${clientKeyFile}" \ -out "${clientReqFile}" \ -subj "${clientSubj}" # and generate the signed client certificate openssl x509 \ -req \ -in "${clientReqFile}" \ -days 365 \ -CA "${caCertFile}" \ -CAkey "${caKeyFile}" \ -set_serial 01 \ -out "${clientCertFile}" # generate a keystore with BOTH the client cert & key openssl pkcs12 \ -export \ -in "${clientCertFile}" \ -inkey "${clientKeyFile}" \ -out "${clientP12KeystoreFile}" \ -name "mysqlAlias" \ -passout pass:kspass # convert pksc12 to jks for use in Java keytool \ -importkeystore \ -deststorepass kspass \ -destkeypass kspass \ -destkeystore "${clientJKSKeystoreFile}" \ -srckeystore "${clientP12KeystoreFile}" \ -srcstoretype PKCS12 \ -srcstorepass kspass \ -alias "mysqlAlias" ls -l "${sslDir}" exit # These might run to copy and configure my.cnf to use these certificates sudo mkdir -p /etc/certtest sudo cp "${caCertFile}" /etc/certtest/ sudo cp "${serverCertFile}" /etc/certtest/ sudo cp "${serverKeyFile}" /etc/certtest/ sudo cp "${clientJKSKeystoreFile}" /etc/certtest/ cat >ssltest-install.awk <<EOF BEGIN { inmysqld = 0 } # a simple awk script to remove existing ssl- comments in mysqld and use these newly generated ones /^\[mysqld\]/ { inmysqld = 1 print print "ssl-ca=/etc/certtest/ssltest-ca-cert.pem" print "ssl-cert=/etc/certtest/ssltest-server-cert.pem" print "ssl-key=/etc/certtest/ssltest-server-key.pem" next } /^\[/ { inmysqld = 0 print next } inmysqld && $0 ~ /^ssl-/ { next } { print } EOF sudo cp /etc/my.cnf my.cnf.original sudo gawk -f doit.awk my.cnf.original >my.cnf.tmp sudo cp my.cnf.tmp /etc/my.cnf
 
Java Test Program
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; public class JdbcTest { public static void main(String[] args) { String url = "jdbc:mysql://192.168.1.100:3306/ssltest" + "?verifyServerCertificate=false" + "&useSSL=true" + "&requireSSL=true" + "&clientCertificateKeyStoreUrl=file:///etc/certtest/ssltest-client-keystore.jks" + "&clientCertificateKeyStorePassword=kspass" + "&serverSslCert=/etc/certtest/ssltest-ca-cert.pem" + ""; String username = "ssluser"; String password = "Password1"; try (Connection conn = DriverManager.getConnection(url, username, password)) { System.out.println("Connected with database " + conn.getCatalog()); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("show status like 'ssl%'"); ResultSetMetaData meta = rs.getMetaData(); for (int col = 1; col <= meta.getColumnCount(); ++col) { if (1 != col) { System.out.print("\t"); } System.out.print(meta.getColumnLabel(col)); } System.out.println(); while (rs.next()) { for (int col = 1; col <= meta.getColumnCount(); ++col) { if (1 != col) { System.out.print("\t"); } System.out.print(rs.getObject(col)); } System.out.println(); } } catch (SQLException e) { e.printStackTrace(); } } }
Next Steps
After you have configured SSL, you can define a Java Naming and Directory Interface (JNDI) data source in your Java container, and then use that JNDI data source to connect to your database using API Creator.
For more information: