MySQL SSL configuration

The defaults for initiating a connection to a MySQL server were changed in the recent past, and (from a quick look through the most popular questions and answers on stack overflow) the new values are causing a lot of confusion. What is worse is that the standard advice seems to be to disable SSL altogether, which is a bit of a disaster in the making.

The typical error message encountered will be something along the lines of:

WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

Now, if your connection is genuinely not exposed to the network (localhost only) or you are working in a non-production environment with no real data, then sure: there's no harm in disabling SSL by including the option useSSL=false.

For everyone else, the following set of options are required to get SSL working with certificate and host verification and the weak protocol options disabled:

• useSSL=true
• sslMode=VERIFY_IDENTITY
• trustCertificateKeyStoreUrl=file:path_to_keystore
• trustCertificateKeyStorePassword=password
• enabledTLSProtocols=TLSv1.2

So as a working example, let us suppose you wish to connect one of the Atlassian products (Jira, Confluence, Bamboo etc.) to a MySQL server using SSL, then you'll need to follow the following broad steps.

• First, make sure you have a valid certificate generated for the MySQL server host, and that the CA certificate is installed onto the client host (if you are using self-signed, then you'll likely need to do this manually, but for the popular public CAs it'll already be there).

• Next, make sure that the java keystore contains all the CA certificates. On Debian/Ubuntu this is achieved by running:

    update-ca-certificates -f
    chmod 644 /etc/ssl/certs/java/cacerts

• Then finally, update the connection string used by the Atlassian product to include all the required options, which on Debian/Ubuntu would be something like:

jdbc:mysql://mysql_server/confluence?useSSL=true&sslMode=VERIFY_IDENTITY&trustCertificateKeyStoreUrl=file%3A%2Fetc%2Fssl%2Fcerts%2Fjava%2Fcacerts&trustCertificateKeyStorePassword=changeit&enabledTLSProtocols=TLSv1.2&useUnicode=true&characterEncoding=utf8