Skip navigation links
17.3.4.6 Using Master/Slave Replication with ReplicationConnection »
Section Navigation      [Toggle]

17.3.4.5. Connecting Securely Using SSL

SSL in MySQL Connector/J encrypts all data (other than the initial handshake) between the JDBC driver and the server. The performance penalty for enabling SSL is an increase in query processing time between 35% and 50%, depending on the size of the query, and the amount of data it returns.

For SSL Support to work, you must have the following:

The system works through two Java truststore files, one file contains the certificate information for the server (truststore in the examples below). The other file contains the certificate for the client (keystore in the examples below). All Java truststore files are password protected by supplying a suitable password to the keytool when you create the files. You need the file names and associated passwords to create an SSL connection.

You will first need to import the MySQL server CA Certificate into a Java truststore. A sample MySQL server CA Certificate is located in the SSL subdirectory of the MySQL source distribution. This is what SSL will use to determine if you are communicating with a secure MySQL server. Alternatively, use the CA Certificate that you have generated or been provided with by your SSL provider.

To use Java's keytool to create a truststore in the current directory , and import the server's CA certificate (cacert.pem), you can do the following (assuming that keytool is in your path. The keytool should be located in the bin subdirectory of your JDK or JRE): 

shell> keytool -import -alias mysqlServerCACert \
                                  -file cacert.pem -keystore truststore

You will need to enter the password when prompted for the keystore file. Interaction with keytool will look like this: 

Enter keystore password:  *********
Owner: EMAILADDRESS=walrus@example.com, CN=Walrus,
       O=MySQL AB, L=Orenburg, ST=Some-State, C=RU
Issuer: EMAILADDRESS=walrus@example.com, CN=Walrus,
       O=MySQL AB, L=Orenburg, ST=Some-State, C=RU
Serial number: 0
Valid from:
   Fri Aug 02 16:55:53 CDT 2002 until: Sat Aug 02 16:55:53 CDT 2003
Certificate fingerprints:
    MD5:  61:91:A0:F2:03:07:61:7A:81:38:66:DA:19:C4:8D:AB
    SHA1: 25:77:41:05:D5:AD:99:8C:14:8C:CA:68:9C:2F:B8:89:C3:34:4D:6C
Trust this certificate? [no]:  yes
Certificate was added to keystore

You then have two options, you can either import the client certificate that matches the CA certificate you just imported, or you can create a new client certificate.

To import an existing certificate, the certificate should be in DER format. You can use openssl to convert an existing certificate into the new format. For example: 

shell> openssl x509 -outform DER -in client-cert.pem -out client.cert

You now need to import the converted certificate into your keystore using keytool: 

shell> keytool -import -file client.cert -keystore keystore -alias mysqlClientCertificate

To generate your own client certificate, use keytool to create a suitable certificate and add it to the keystore file: 

 shell> keytool -genkey -keyalg rsa \
     -alias mysqlClientCertificate -keystore keystore

Keytool will prompt you for the following information, and create a keystore named keystore in the current directory.

You should respond with information that is appropriate for your situation: 

Enter keystore password:  *********
What is your first and last name?
  [Unknown]:  Matthews
What is the name of your organizational unit?
  [Unknown]:  Software Development
What is the name of your organization?
  [Unknown]:  MySQL AB
What is the name of your City or Locality?
  [Unknown]:  Flossmoor
What is the name of your State or Province?
  [Unknown]:  IL
What is the two-letter country code for this unit?
  [Unknown]:  US
Is <CN=Matthews, OU=Software Development, O=MySQL AB,
 L=Flossmoor, ST=IL, C=US> correct?
  [no]:  y

Enter key password for <mysqlClientCertificate>
        (RETURN if same as keystore password):

Finally, to get JSSE to use the keystore and truststore that you have generated, you need to set the following system properties when you start your JVM, replacing path_to_keystore_file with the full path to the keystore file you created, path_to_truststore_file with the path to the truststore file you created, and using the appropriate password values for each property. You can do this either on the command line: 

-Djavax.net.ssl.keyStore=path_to_keystore_file
-Djavax.net.ssl.keyStorePassword=password
-Djavax.net.ssl.trustStore=path_to_truststore_file
-Djavax.net.ssl.trustStorePassword=password

Or you can set the values directly within the application: 

 System.setProperty("javax.net.ssl.keyStore","path_to_keystore_file");
System.setProperty("javax.net.ssl.keyStorePassword","password");
System.setProperty("javax.net.ssl.trustStore","path_to_truststore_file");
System.setProperty("javax.net.ssl.trustStorePassword","password");

You will also need to set useSSL to true in your connection parameters for MySQL Connector/J, either by adding useSSL=true to your URL, or by setting the property useSSL to true in the java.util.Properties instance you pass to DriverManager.getConnection().

You can test that SSL is working by turning on JSSE debugging (as detailed below), and look for the following key events: 

...
*** ClientHello, v3.1
RandomCookie:  GMT: 1018531834 bytes = { 199, 148, 180, 215, 74, 12, »
  54, 244, 0, 168, 55, 103, 215, 64, 16, 138, 225, 190, 132, 153, 2, »
  217, 219, 239, 202, 19, 121, 78 }
Session ID:  {}
Cipher Suites:  { 0, 5, 0, 4, 0, 9, 0, 10, 0, 18, 0, 19, 0, 3, 0, 17 }
Compression Methods:  { 0 }
***
[write] MD5 and SHA1 hashes:  len = 59
0000: 01 00 00 37 03 01 3D B6 90 FA C7 94 B4 D7 4A 0C  ...7..=.......J.
0010: 36 F4 00 A8 37 67 D7 40 10 8A E1 BE 84 99 02 D9  6...7g.@........
0020: DB EF CA 13 79 4E 00 00 10 00 05 00 04 00 09 00  ....yN..........
0030: 0A 00 12 00 13 00 03 00 11 01 00                 ...........
main, WRITE:  SSL v3.1 Handshake, length = 59
main, READ:  SSL v3.1 Handshake, length = 74
*** ServerHello, v3.1
RandomCookie:  GMT: 1018577560 bytes = { 116, 50, 4, 103, 25, 100, 58, »
   202, 79, 185, 178, 100, 215, 66, 254, 21, 83, 187, 190, 42, 170, 3, »
   132, 110, 82, 148, 160, 92 }
Session ID:  {163, 227, 84, 53, 81, 127, 252, 254, 178, 179, 68, 63, »
   182, 158, 30, 11, 150, 79, 170, 76, 255, 92, 15, 226, 24, 17, 177, »
   219, 158, 177, 187, 143}
Cipher Suite:  { 0, 5 }
Compression Method: 0
***
%% Created:  [Session-1, SSL_RSA_WITH_RC4_128_SHA]
** SSL_RSA_WITH_RC4_128_SHA
[read] MD5 and SHA1 hashes:  len = 74
0000: 02 00 00 46 03 01 3D B6 43 98 74 32 04 67 19 64  ...F..=.C.t2.g.d
0010: 3A CA 4F B9 B2 64 D7 42 FE 15 53 BB BE 2A AA 03  :.O..d.B..S..*..
0020: 84 6E 52 94 A0 5C 20 A3 E3 54 35 51 7F FC FE B2  .nR..\ ..T5Q....
0030: B3 44 3F B6 9E 1E 0B 96 4F AA 4C FF 5C 0F E2 18  .D?.....O.L.\...
0040: 11 B1 DB 9E B1 BB 8F 00 05 00                    ..........
main, READ:  SSL v3.1 Handshake, length = 1712
...

JSSE provides debugging (to STDOUT) when you set the following system property: -Djavax.net.debug=all This will tell you what keystores and truststores are being used, as well as what is going on during the SSL handshake and certificate exchange. It will be helpful when trying to determine what is not working when trying to get an SSL connection to happen.


User Comments

Posted by J A on March 15 2007 12:23pm[Delete] [Edit]

Yet again, the MySQL documentation fails abysmally. Followed the steps above to the letter - didn't work. AAAARG!

Posted by Mark Matthews on March 26 2007 9:52pm[Delete] [Edit]

Where exactly did things fall apart for you? I've used the steps here recently and they worked for me. Without telling the documentation folks where _exactly_ things went wonky, they're not going to be able to fix things to help you out.

Posted by Pronab Saha on April 19 2007 4:15am[Delete] [Edit]

I've found that connecting from a Java client using a self-signed certificate does not work for me. The client certificate needs to be signed by the CA that the MySQL server trusts.

I've read some of the discussions on the MySQL forums about this and understand that others are/were having the same problem as me. After some experimentation, I've got it to work and wanted to share that information here.

The http://access1.sun.com/techarticles/Keytool.html site was an absolute essential source of information in getting over the "Failed to establish chain from reply” error message that I kept on getting when trying to import a certificate signed by a CA that is not trusted by java.

For testing purposes, I have setup a local CA using openssl. Also, I am on a Linux environment and using MySQL version 5.0.37. I am assuming that the local CA has already been setup and will focus on the steps I followed to ensure that Java keystore can import the signed certificate.

1. Create a self-signed certificate and key pair so that Java can use it
$ keytool -genkeypair -keyalg rsa -validity 3650 -alias mysqlClientCertificate -keystore keystore

2 Export that self-signed certificate
This is so that our CA can sign it and send it back as a signed response.
$ keytool -certreq -v -alias mysqlclientcertificate -keystore keystore -file javaclient.cr

3 CA signs the certificate request
$ openssl ca -out javaclientPEM.csr -config ./openssl.cnf -infiles javaclient.cr

(the openssl.cnf file has the typical configuration values and nothing special)

4 Combine two certificates
Combine the just signed certificate with the CA’s root certificate. This is so that that key tool utility can successfully verify the signed certificate. The way it verifies this is by first checking to see if it trusts the issuer. This is done by going to the $JAVA_HOME/ jre/lib/security/cacerts store to check of the issuer is trusted.

Unfortunately, because we are acting as our own certificate authority, we won’t be in that cacerts file.

The article mentioned at the beginning of this section recommends two ways of helping keytool to do its work. The first approach is to import our CA’s certificate into the $JAVA_HOME/ jre/lib/security/cacerts store. A second way is to put the CA’s certificate first followed by the just signed certificate. I used the second way.

In the below command, please note that the importance of cacert file coming before the just signed cert is critical. Without this sequence, importing into the java keystore would fail with the exception "Failed to establish chain from reply”

$ openssl crl2pkcs7 -nocrl -certfile cacert.pem -certfile javaclientPEM.csr -outform DER -out combined_responsepk7.der

5 Import into the key store
Import the “rootCA+signed request” file into the key store. Note that this combined file needs to be in pk7 and DER format
$ keytool -importcert -v -alias mysqlclientcertificate -file combined_responsepk7.der -keystore keystore

6 List out the key store
List the contents of the key store to verify that the previously self-signed certificate associated with the mysqlclientcertificate alias is now issued by our CA.
$ keytool -list -v -keystore keystore

7.Use a simple java program to test the connection. Of course, to test it, you need to have mysql-connector-java-5.0.5-bin.jar in the CLASSPATH (this is the version of the connector I used)

import java.sql.*;


public class HelloWorld{
public static void main(String args[]){
System.out.println("Starting program");
try {
//Register the JDBC driver for MySQL.
Class.forName("com.mysql.jdbc.Driver");

String url =
"jdbc:mysql://localhost:3306/mysql?useSSL=true&requireSSL=true";

Connection con =
DriverManager.getConnection(
url,"javatestuser", "secretpassword");

//Display URL and connection information
System.out.println("URL: " + url);
System.out.println("Connection: " + con);

con.close();
} catch(Exception ex){
ex.printStackTrace();
}
}
}

8. compile and run the program
$ javac HelloWorld.java
$ java \
-Djavax.net.ssl.keyStore=/home/ca/java/keystore \
-Djavax.net.ssl.keyStorePassword=passpass \
-Djavax.net.ssl.trustStore=/home/ca/java/truststore \
-Djavax.net.ssl.trustStorePassword=passpass \
-Djavax.net.debug=all \
HelloWorld

the -D debug all is helpful to see what's happening as suggested in the documentation.

Note that the trust store contains our local CA's root cert.

Note that javatestuser inside of my mysql database instance has been setup with the GRANT command to require SSL..etc.

Posted by Matthew Farhat on May 12 2008 12:50am[Delete] [Edit]

I was able to establish a ssl connection using these instructions, but I had to make a couple adjustments which were to exclude the steps for creating a client entry in the keystore as well as excluding System.setProperty(...) for both the entries involving %keyStore%.
By following the instructions exactly, I was able to instantiate the mysql server with ssl capabilities. However, I repeatedly received Communications link failure when attempting to connect to the database until making the exclusions described above.

Posted by Stiepan Aurélien on March 31 2009 4:17pm[Delete] [Edit]

(Nearly the) same for me as with the above comment - when trying to add the client cert, I was getting "erreur keytool : java.security.SignatureException: Signature does not match.".
I have a self-signed certs setup.

In short, by doing 'keytool -import -alias ca-key.pem -file ca-cert.pem -keystore truststore' on the client machine (OS X.5.6)

and

GRANTing necessary priviledges on the to be accessed by Java database on the server machine to the appropriated user@the client machine's network address (see http://forums.mysql.com/read.php?30,165914,165941)

I was able to use the remote db through an SSL connection with Netbeans 6.5 by passing it the following JDBC URL:
jdbc:mysql://server.tld/db_name?verifyServerCertificate=false&useSSL=true&requireSSL=true

Slightly shorter than the original tutorial(s) isn't it? ;)

Posted by Steve Cohen on September 12 2009 12:30pm[Delete] [Edit]

The problem with this documentation and with many of the comments is that they don't specify what KIND of SSL connection they are trying to set up. They all say something like "GRANT user with appropriate syntax to require use of SSL". But that begs the question which kind of SSL access they are trying to create. This is important to know.

In Stiepan Aurélien's comment above (not to pick on him, many comments have this problem) for example, it seems that he is doing "REQUIRE SSL" - that is, simple encryption of the data transmission rather than "REQUIRE X509" which encompasses both encryption and authentication. That is why his method can be simpler.

But users need to understand which of these things they are trying to do, and there should be separate sets of instructions appropriate to each choice. Instead we have something of a jumble here.

Add your own comment.