SSL/TLS connection to MySQL

[kuerzingera]

Hi,

does sql_exporter version 0.5 support ssl/tls connections to MySQL server?

I tried to configure this in "data_source_name" of sql_exporter.yml with additional parameters in DSN but haven't been successful so far.

e.g.

data_source_name: "mysql://my_user:my_pw@tcp(my_host:3306)/my_db?tls=true&tls.ca=server-ca.pem&tls.cert=client-cert.pem&tls.key=client-key.pem"
  
=> Error: No metrics gathered, [from Gatherer #1] x509: certificate signed by unknown authority

data_source_name: "mysql://my_user:my_pw@tcp(my_host:3306)/my_db?tls=true&tls=skip-verify&tls.ca=server-ca.pem&tls.cert=client-cert.pem&tls.key=client-key.pem"

=> Error: No metrics gathered, [from Gatherer #1] Error 1045: Access denied for user 'my_user'@'my_host' (using password: YES)
(note: I can access database manually with this user from machine where sql_exporter is running.)

The same behaviour or errors at using key_store file as an alternative:
e.g.
data_source_name: "mysql://my_user:my_pw@tcp(my_host:3306)/my_db?tls=true&tls=skip-verify&tls.key_store=my-ssl-mysqlcert-i.p12&tls.key_store_password=my_pw123"

---

In Readme or sql_exporter help I cannot really find any hint about ssl/tls connections to MySQL database.
Is there anyone who can give a hint or has an example how to tackle this issue?

Thanks

[burningalchemist]

Hi @kuerzingera,

Just in case, the current and supported version of sql_exporter is v0.16.

Please also check the documentation on the DSN string here. The connection string format you're using MySQL-driver specific, you need to use the format provided by xo/dburl (link is in the documentation).

TLS/SSL should be supported, and I'm happy to help you with the issue, but let's start with the right configuration. 👍

[kuerzingera]

Hi @burningalchemist ,

Thank you so far.

Regarding the different sql_exporter versions I realized that I must have been landed here in wrong github project? I'm sorry for confusion.
Our used sql_exporter version is in fact 0.5

sql_exporter --version 
sql_exporter, version 0.5 (branch: master, revision: fc5ed07ee38c5b90bab285392c43edfe32d271c5)
  build user:       root@f24ba5099571
  build date:       20190114-09:25:05
  go version:       go1.11.3

And documentation about DSN string in Readme we have is a little bit different from given in this project.

Nevertheless we succeeded to establish first SSL connection from sql_exporter client to our MySQL server (Cloud SQL) by changing SSL mode of server (lower level) and defining this data_source_name template in sql_exporter.yml:

data_source_name: "mysql://my_user:my_pw@tcp(my_host:3306)/my_db?tls=skip-verify"

On MySQL server side (Cloud SQL) we basically can configure following optional SSL modes for connections:
1.) Allow unencrypted network traffic (not recommended)
2.) Allow only SSL connections (Only allows connections using SSL/TLS encryption. Certificates will not be verified.)
3.) Require trusted client certificates (Only allows connections from clients that use a valid client certificate and SSL encryption)

So far we have just been successful to establish SSL connection with above server side SSL mode number 2 (Connection with SSL/TLS encryption without verfying certificates).

We also tried with server SSL mode 3 (including certificate verification) by passing more parameters like tls.key_store / tls.key_store_password in DSN.
(like connect always works e.g. for other of our applications with this server side ssl mode.)

data_source_name: "mysql://my_user:my_pw@tcp(my_host:3306)/my_db?tls=true&tls.key_store=%2Fc%2Fwork%2Fkeystore%2Fmy-ssl-mysqlcert-i.p12&tls.key_store_password=myPw123"

But result at calling sql_exporter metrics is always:
No metrics gathered, [from Gatherer #1] x509: certificate signed by unknown authority.
(note: here used certificates were generated for SQL connection on server side in Cloud and are just self signed.)

I ask myself if it possible at all for sql_exporter to establish ssl/tls connection to MySQL server with passing valid client certificates for verification?

BR

[burningalchemist]

Hi @kuerzingera, version v0.5 is unmaintained since 2019. It's the original project I used to contribute to (made by free), but I had to hard fork the project to continue maintenance, development, and security updates on my own.

---

It's possible, that v0.5 contains the old MySQL driver, which might have issues. Also, there is no special code to handle database connection on the sql_exporter side. So I'd rather check driver repository, and maybe file an issue there.

It's also possible that MySQL Driver doesn't support passing path for TLS certificates at the moment - have a look here: go-sql-driver/mysql#926.

Also related discussion: go-sql-driver/mysql#908 (comment)

So it's likely what you are trying to achieve is not supported via DSN by the driver so far, and sql_exporter operates specifically via DSN to support multiple databases without too much custom code.