How to Connect via SSL/PEM files to MySQL? - Google Cloud

1

I have a Google Cloud instance (managed MySql 5.7.14) I wish to connect to. It is setup to only allow SSL connections.

My key files are located in:
~/db/keys/server-ca.pem
~/db/keys/client-cert.pem
~/db/keys/client-key.pem

I have tried both following additional JDBC connection strings
sslca=’~/db/keys/server-ca.pem’;sslcert=’~/keys/client-cert.pem’;sslkey=’~/db/keys/client-key.pem’
and
ssl_ca=’~/db/keys/server-ca.pem’;ssl_cert=’~/keys/client-cert.pem’;ssl_key=’~/db/keys/client-key.pem’
to no avail

Addtl. info:

I don’t want to setup SSH tunneling. There is no tunnel to connect on the managed instance to and I don’t have a server available on the subnet.
I don’t want to setup a read-only, non SSL user. Db IP is exposed and I need security.
Running on a local Mac OS X instance (I’m giving Metabase a try).
I found an old forum post (oct 16) about this but no answer then.

2

Full understand the way you want to run this given your environment. I think I would go for something similar.

I’ve no experience with MySQL with SSL myself (more SQL Server, Redshift etc. here). Anyway I’l try a few quick shots.

I just googled https://www.google.dk/search?q=jdbc+mysql+ssl%2Fpem

First hit for me is https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html (which seems sort a good bet for a canonical source?)

Briefly scanning it, the MySQL JDBC page has no mention of neither ssl_xxx nor sslxxx variations of JDBC parameters. So where did you find info about those? If it’s part of Metabase info - maybe it’s in need of an update …?

It mentions sticking certs in the Java Keystore using keytool and also has instructions for converting the .pem files to a form keytool can digest using openssl. That is a bit cumbersome to work with as far as I can remember. Last time I had to do similar I ended up using http://keystore-explorer.org/ to pull in the .pem into the Keystore directly.

HTH

3

Hi Jornh,

Thx a lot for taking the time to contribute.

  • The ssl_xxx parameters I tried from my SQLAlchemy JDBC parameters which allow me to specify the necessary pem files on my filesystem for my python backend that’s actually using the database. It was a bit of a shot in the dark. Thanks for the info and I’ll give a shot - I had seen things similar to what you described but given the cumbersome nature of converting the files and storing in the java keystore I was hoping someone with a similar situation would have a more elegant way of dealing with it. I’ll report on my findings if I get around to trying it
  • in the mean time I’ve setup replication of my hosted DB to a local docker instance of MySQL that is unsecured and things are running fine - although with a slight delay. Replication guide: https://www.digitalocean.com/community/tutorials/how-to-set-up-master-slave-replication-in-mysql
4

Ahh, Python != Java. (Even if I’ve also seen SQLAlchemy has a JDBC driver)