Connect to multiple MySQL instances with SSL enabled in JDBC

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.

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.

Recommended Posts

Connect to multiple MySQL instances with SSL enabled in JDBC
Connect to MySQL 8 with Java
Whether to enable SSL when using JDBC with MySQL.
[Java] Connect to MySQL
Settings for connecting to MySQL with Spring Boot + Spring JDBC
How to connect MySQL / MariaDB + HikariCP with Liferay 7 / DXP
Connect to DB with Java
Connect to oracle with eclipse!
[Android] Connect to MySQL (unfinished)
I tried connecting to MySQL using JDBC Template with Spring MVC
Operation to connect multiple Streams @Java
JDBC connection with MySQL 8.x * Notes *
To display multiple lines with UILabel
mysql2 fails to install with bundle install
Connect to Rails server with iPhone
Update MySQL from 5.7 to 8.0 with Docker
I want to select multiple items with a custom layout in Dialog
[Java] I installed JDBC and tried to connect with servlet + MySQL. (There is a version using DAO / Bean)
Connect to Amazon EC2 with SSH (Ubuntu)
Note to have multiple values in HashMap
Deploy to heroku with Docker (Rails 6, MySQL)
Edit Mysql with commands in Docker environment
How to use MySQL in Rails tutorial
Connect from Java to MySQL using Eclipse
How to execute multiple commands in docker-compose.yml
How to redirect to http-> https when SSL is enabled in Rails × Heroku environment
Solution that gives an error when trying to connect to DB (MySQL) in Java
[Rails] How to register multiple records in the intermediate table with many-to-many association
[Java] I tried to connect using a connection pool with Servlet (tomcat) & MySQL & Java