Java connect to Cloud SQL 2nd Gen from Appengine Managed VM

Question

Trying to setup connection to Clond SQL 2nd Generation for a Java Appengine with Managed VM.

What I got:

• VM is using Appengine Compat mode (FROM gcr.io/google_appengine/java-compat)
• I see a MySQL pipe on the host (SSHed, I can see /cloudsql/** socket), but default MySQL Java driver doesnt support unix sockets
• Google Driver seems to be unsupported on Managed VM (java.lang.ClassNotFoundException: com.mysql.jdbc.GoogleDriver)
• and I cannot connect to MySQL by IP
  • w/o driver it's No suitable driver found for jdbc:google:mysql://__IP__:3306/__db__
  • or timeout for jdbc:mysql://__IP__:3306/__db__ (I've added MVM external IP to authorized networks on MySQL)
  • but what interesting, I can connect to this port from MVM machine, at least port is opened. Anyway, I don't like idea of connecting through publicly open IP:PORT. MVM have a new IP each time, so I even can't add a Firewall rule to specify access from my project only

How I should configure ManagedVM app to connect to Cloud SQL?

Answer 1

I am having trouble launching a Managed VM App using Java so I can't try it out for myself right now, but it looks like you should be able to leverage this library to connect to a Unix Socket from Java. There's an example in which you'd just have to change the path of the socket:

props.put("junixsocket.file", "/cloudsql/project-id:region:instance-id");

Where project-id is your project name, region is the region in which your Cloud SQL instance lives (e.g. us-east1) and instance-id is the name of your cloud-sql instance. The string will be in the properties of your Second Generation instance under "Instance connection name".

Answer 2

We have a new Java library for connecting to Cloud SQL instances from Managed VMs and other environments: https://github.com/GoogleCloudPlatform/cloud-sql-mysql-socket-factory

It's still very new so the usual caveats apply, but we haven't found any issues in our testing.

Answer 3

Best way I found is to configure SSL connection from ManagedVM

There two things you should do.

First, prepare Java Keystore with SSL you'll get from Cloud SQL console (files server-ca.pem, client-cert.pem and client-key.pem):

echo '---------- GENERATE TrustStore'
keytool -import -alias mysqlCACert -file server-ca.pem -keystore truststore -storepass 123456

echo '---------- GENERATE KeyStore'
openssl pkcs12 -export -in client-cert.pem -inkey client-key.pem \
    -out client.p12 -name clientalias -CAfile server-ca.pem -caname root
keytool -importkeystore -deststorepass 123456 -destkeystore keystore \
    -srckeystore client.p12 -srcstoretype PKCS12 -srcstorepass 123456 -alias clientalias

You'll get two files: truststore and keystore that you'll need to put into your target VM.

Second, you should use custom Dockerfile, with files from previous step:

FROM gcr.io/google_appengine/java-compat

RUN mkdir /keys
ADD keystore /keys/
ADD truststore /keys/
ENV JAVA_OPTS -Djavax.net.ssl.keyStore=/keys/keystore -Djavax.net.ssl.keyStorePassword=123456 -Djavax.net.ssl.trustStore=/keys/truststore -Djavax.net.ssl.trustStorePassword=123456

ADD .  /app/

And, of course, don't forget to tell MySQL driver to always use SSL:

dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://__CLOUD_SQL_IP__:3306/__CLOUD_SQL_DB__?useSSL=true&requireSSL=true");

PS Notice, I'm using password 123456 to protect my keys. It's just example. Don't use it in your project. It's already taken.