Related Documentation Download this Manual Excerpts from this Manual


This page has moved or been replaced. The new page is located here:

Please update any bookmarks that point to the old page.

Download this Manual
User Comments
  Posted by J A on March 15, 2007
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
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
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 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

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

(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.

String url =

Connection con =
url,"javatestuser", "secretpassword");

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

} catch(Exception ex){

8. compile and run the program
$ javac
$ java \ \ \ \ \ \

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
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
(Nearly the) same for me as with the above comment - when trying to add the client cert, I was getting "erreur keytool : 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)


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,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:

Slightly shorter than the original tutorial(s) isn't it? ;)
  Posted by Steve Cohen on September 12, 2009
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.

  Posted by Darryl Miles on June 12, 2012

1) If you are taking an X.509 cert and key from another source (such as openssl output) and trying to use it as the client side certificate.

You can import this into JSSE by exporting from openssl as PKCS12 format:

openssl -export -in mynew.crt -inkey mynew.key -out mynew.p12 -name "mynew"
Password: password1234

You can import into JSSE then using:

keytool -importkeystore -destkeystore keystore -deststorepass keyPassword -srckeystore mynew.p12 -srcstorepass password1234 -srcstoretype PKCS12 -alias mynew

2) You MUST ensure the "keystore password" and the private "key password" are idential (since it is possible for them to end up different). Since the way ExportControlled#getSSLSocketFactoryDefaultOrConfigured(MysqlIO) works it is uses the same password as configured from "clientCertificateKeyStorePassword" to both open the key store file *the JKS format file) and to decrypt the private key inside the file.

If you are having a problem that you get UnrecoverableKeyException "can not recover key" then you can change the password of the key with:

keytool -keypasswd -keystore keystore -alias mynew
Enter keystore password: keyPassword
Enter key password for <mynew>: password1234
New key password for <mynew>: keyPassword
Re-enter new key password for <mynew>: keyPassword

Note this example of the mismatch between "keystore password" and "key password" is kind of based on having followed option item 1 above.
  Posted by Mark Matten on July 23, 2012
After LITERALLY SPENDING A WEEK DOING THIS I have finally managed to connect using a client certifiacte (REQUIRES X509 on the user defintion)!!!!

rem NOTE: these commands are run using the Java 6 (1.6) JDK as it requires the "-importkeystore" command
rem which is not available before this JDK version.

rem Import the self signed Certifacte Authority certificate into a keystore.
keytool -import -alias mysqlCACert -file ca-cert.pem -keystore truststore -storepass truststore
rem Shows only the signed certificate.
keytool -v -list -keystore truststore -storepass truststore

rem Create a PKCS12 file from an existing signed client certifcate and its private key.
rem set password to "keystore".
openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -out client.p12 -name clientalias -CAfile ca-cert.pem -caname root
rem Import the combined certificate and private key into the keystore.
keytool -importkeystore -deststorepass keystore -destkeystore keystore -srckeystore client.p12 -srcstoretype PKCS12 -srcstorepass keystore -alias clientalias

Then specify the trusted certifcates file (the truststore) and the client certificate/key file (the keystore) in your Java application either via the connection URL, via the JVM start-up parameter arguments (-D<var>=<val>,...), or System.setProperty(var,val),...

It actually works!!!
  Posted by Mohamed Nabil on April 17, 2013
assuming you are debugging ssl using or System.setProperty("","all");

If you faced an Eexception of type (Communications link failure:
The last packet successfully received from the server was 546 milliseconds ago.
The last packet sent successfully to the server was 531 milliseconds ago.)

it is likely to find this line logged by
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA

This TLS_DHE_RSA_WITH_AES_256_CBC_SHA is the standard DHE-RSA-AES256-SHA cipher used by Mysql Server 5.6
on mysql client type
mysql> \s
you will find
Cipher in use is DHE-RSA-AES256-SHA

Then solution for this exception, you MUST update JDK 7 JSSE by updating JCE from JDK download page (Java SE Downloads) find and download (Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files 7) then replace local_policy.jar & US_export_policy.jar as in README file section installation
paste them to JDK_HOME/jre/lib/security
and also, for windows users past them to JRE on program files
I've updated JCE even I'm using jdk1.7.0 update 17 at this post time

Now this line must disappear from logging
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA
  Posted by Mohamed Nabil on April 18, 2013
** Connect JDBC SSL using X509 user
** Ref.

If you have followed steps to create CA, Server, Client certificates and you can connect using mysql with no errors logged to mysql.err; the file used with variable log_erro=/file/name on my.cnf
> Example 2: Creating SSL Files Using a Script on Unix

You can continue to connect Connector/J to MySQL using user created REQUIRE X509

** 1st- Create X509 user

a- Check that certificate signed correctly with Certificate Authority
openssl verify -CAfile ca-cert.pem client-cert.pem
a message like "/client-cert.pem: OK" shall out

b- obtain certificate Issuer and Subject using
openssl x509 -noout -in client-cert.pem -issuer
openssl x509 -noout -in client-cert.pem -subject

c- Use out put of step (b) to fill GRANT statement ISSUER & SUBJECT parameters with obtained output
CREATE USER 'user_name'@'localhost' IDENTIFIED BY 'pass_word';
GRANT ALL PRIVILEGES ON database.* TO 'user_name'@'localhost' IDENTIFIED BY 'pass_word' REQUIRE X509;

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' IDENTIFIED BY 'pass_word' REQUIRE
ISSUER '/C=FI/ST=Country/L=City/O=Organization/OU=Organization Unit/CN=CERTIFICATE AUTHORITY/'
AND SUBJECT '/C=FI/ST=Country/L=City/O=Organization/OU=Organization Unit/CN=CLIENT CERTIFICATE NAME/'

d- validate user created, by successfully login using
> mysql -u user_name -p \
--ssl-ca=/opt/mysq/cert/ca-cert.pem \
--ssl-cert=/opt/mysq/cert/client-cert.pem \

* or run command
> mysql -u user_name -p

after configure client section at configuration file my.cnf then connect using mysql client command line; no need to restart MySQL server after this update since it is client side only

** 2nd- JDBC configuration
following steps by Alexander Soklakov from

1st- for SSL user user use the certificate conversions and TrustStore and KeyStore sample documented here
2nd- for X509 user user use the following steps:

a- add Certificate Authority to a new TrustStore
keytool -import -alias mysqlCACert -file ca-cert.pem -keystore truststore -storepass pass_word -deststoretype JKS

b- Converting openssl created client certificate to accepted java keystore PKCS12
openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem -certfile client-cert.pem -name "Name" -out client.p12

a- Add new PKCS12 certificate to key store JKS type
keytool -importkeystore -srckeystore client.p12 -srcstoretype pkcs12 -destkeystore keystore -storepass pass_word -deststoretype JKS

2nd- configure TrustStore and KeyStore as in documentation


try {
mysqlDS = new MysqlDataSource();





// Create a connection object
connection = mysqlDS.getConnection("user_name", "pass_word");
} catch (Exception x) {

3rd- Check JSSE output log tracked by that this line is not exist or you will get Communications link failure
Ignoring unavailable cipher suite: TLS_DHE_RSA_WITH_AES_256_CBC_SHA
To solve problem see previous comment

4th- Check Connector J output tracked by mysqlDS.setLogger("com.mysql.jdbc.log.StandardLogger");
URL jdbc:mysql://
Driver Version mysql-connector-java-5.1.23 ( Revision: ${bzr.revision-id} )

- Now every thing shall work fine as expected

- to rest assured every thing is working properly rerun this SQL statement and corrupt issuer or subject content
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' IDENTIFIED BY 'pass_word' REQUIRE
ISSUER '/C=FI/ST=Country/L=City/O=Organization/OU=Organization Unit/CN=CA CORRUPT DATA HERE/'
AND SUBJECT '/C=FI/ST=Country/L=City/O=Organization/OU=Organization Unit/CN=Client CORRUPT DATA HERE/'

try to connect using JDBC or mysql client tool command line
mysql.err shall show something like :
2013-04-17 11:25:51 764 [Note] X509 issuer mismatch: should be '/C=FI/ST=Country/L=City/O=Organization/OU=Organization Unit/CN=Certificate Authority Name/'
2013-04-17 11:25:51 764 [Note] X509 subject mismatch: should be '/C=FI/ST=Country/L=City/O=Organization/OU=Organization Unit/CN=Client Certificate Name/'


Sign Up Login You must be logged in to post a comment.