This article

This is the 8th day article of Speee Advent Calendar 2018! Please subscribe if you like.

Yesterday I went to [AWS re: Invent 2018] by @selmertsx! ](Https://tech.speee.jp/entry/2018/12/07/163341).

Today is ** connecting to multiple MySQL instances with SSL enabled in JDBC **. After reading this article, you may not have to worry about connecting to multiple MySQL instances with SSL with JDBC.

Introduction

Previously, I migrated two MySQL servers, master and replica, to Cloud SQL on GCP, as shown in the figure below. Database移行 At that time, when I enabled SSL, which I hadn't been able to do before, it was easy to switch in Ruby (Rails), but I couldn't do it immediately in Java.

This article is a memo of the solution at that time.

Suddenly aside

Since it's a big deal, how to connect with Rails. For Rails, just add the settings for SSL to database.yaml. It seems good to issue a certificate with Cloud SQL and put it in the right place. I set it in the following form (excerpt).

`database.yaml`


default: &default
    adapter: mysql2
    ...

  ssl: &ssl
    sslca: <%= ENV.fetch("HOGE_DATABASE_SSL_SERVER_CA") %>
    sslcert: <%= ENV.fetch("HOGE_DATABASE_SSL_CLIENT_CERT") %>
    sslkey: <%= ENV.fetch("HOGE_DATABASE_SSL_CLIENT_KEY") %>

  development:
    <<: *default
    database: hoge_development
    ...

  test:
    <<: *default
    database: hoge_test
    ...

  staging:
    <<: *default
    <<: *ssl
    database: hoge_staging
    ...

  production:
    <<: *default
    <<: *ssl
    database: hoge_production
    ...

The Rails application that switched the connection destination in this switching work created a base model for each DB with the database.yaml to be read changed in order to connect to multiple DBs, so add it to each database.yaml. Only by, SSL was made.

It's easy.

Main subject

For Java, it's a bit more complicated than Rails. As a premise, it is assumed that there is a JDBC setting for each connection destination.

Preparation

In Java, you can't use 3 pems as they are like Rails. It must be imported into a Java Key Store format (JKS) container. Also, if you want to import the private key, you must use the ʻopenssh` command to convert it to PKCS12 format before you can import it into JKS.

Import is as follows. Create this ** separately for each MySQL instance to connect **.

$ keytool -import -file ./server-ca.pem -alias <Something distinguished name> -keystore <File name of the container>
$ openssl pkcs12 -export -inkey ./client-key.pem -in ./client-cert.pem -name <Something distinguished name> -out ./temp.p12
$ keytool -importkeystore -srckeystore ./temp.p12 -srcstoretype pkcs12 -destkeystore <File name of the container>

--When creating a Java Key Store format container for the first time, a new one will be created with the specified file name. ―― keytool is a tool that comes with the JDK etc., and it may not be possible to pass the path. It seems to be in bin under the JDK installation path. --Every time you hit a command, you will be prompted for a password. This is a JKS-style container password that has nothing to do with the user's password. It's like a Zip with a pass.

Connection settings

After preparing JKS for each DB, add the settings to the JDBC connection URL. Specify the following six.

clientCertificateKeyStoreUrl
Specify the JKS file path.
clientCertificateKeyStoreType
Specify JKS.
clientCertificateKeyStorePassword
Specify the password you entered when you created JKS.
trustCertificateKeyStoreUrl
Specify the JKS file path. It looks good with the same one as clientCertificateKeyStoreUrl.
trustCertificateKeyStoreType
Specify JKS.
trustCertificateKeyStorePassword
Specify the password you entered when you created JKS.

The following example is the connection URL for connecting to a MySQL instance for replica with IP: www.xxx.yyy.zzz with the MySQL JDBC driver.

jdbc:mysql://www.xxx.yyy.zzz:3306/hoge?useSSL=true&amp;requireSSL=true&amp;clientCertificateKeyStoreUrl=file:///path/to/secrets/hoge_replica.jks&amp;clientCertificateKeyStoreType=JKS&amp;clientCertificateKeyStorePassword=<Password set&amp;trustCertificateKeyStoreUrl=file:///path/to/secrets/hoge_replica.jks&amp;trustCertificateKeyStoreType=JKS&amp;trustCertificateKeyStorePassword=<Password set>"

If you want to use it as a master, just switch the JKS file path and password to the one for the master. You can now connect to multiple SSL-enabled MySQL instances.

Digression

One way to connect to a MySQL instance with SSL enabled in Java was to specify it as an option when running Java. Initially, I tried to combine the master and replica certificates and keys into one JKS according to it, but for some reason the program did not work properly and I was in trouble.

After investigating various things, it seems that it is a Java bug? specification? So, if you put multiple certificates with the same issuer (Google in the case of CloudSQL) in one JKS, please select appropriately for each DB connection destination ** not ** is. So, for example, when you try to connect to the replica with the master certificate, the SSL connection is cut off.

in conclusion

Since I actually did a MySQL instance of CloudSQL, I used it as MySQL in the text, but I think that the application will be effective when making an SSL connection using JDBC in Java.

[JAVA] Connect to multiple MySQL instances with SSL enabled in JDBC