As we all know, MySQL supports using SSL to secure connections by encrypting the data in transit and protecting it from snooping on the wire.

As of now, since version v1.2.0e, ProxySQL supports SSL connections to the backends. Frontend SSL is enabled by default from version 2.0, which is under development.
Even if frontend SSL is not available before 2.0 version , there is a way to secure frontend traffic by deploying ProxySQL with app hosts and use sockets instead of tcp.

This document will cover how to integrate ProxySQL Backend SSL with MySQL to use an encrypted connection.

ProxySQL instance

Enable Encryption on MySQL

Configuring MySQL for Encrypted Connections is out of the scope of this article, used information found in the following link to enable SSL on server.

First, we must check if MySQL server supports SSL connections.
Log into MySQL…

Create a user to permit only SSL-encrypted connection on MySQL Server.

Test a secure connection:

As ProxySQL is forwarding traffic to all backend servers, we need to keep the same *.pem files on all database instances.
You can copy below following files from any DB node to all backends.
Remember that you have to change their ownership from root user to mysql.

Once you are done, restart MySQL servers.

Enable Encryption on ProxySQL

At this stage, connection attempts to host and port 6033 will not use SSL because no key and no certificate has been configured. Instead, normal non-SSL connections will be established.
We must now transfer ca.pem, client-cert.pem, and client-key.pem to ProxySQL server under folder /var/lib/proxysql/

Currently, as seen in the ProxySQL configuration, SSL-related variables are not defined. We will have to change this.

First of all, we have to tell ProxySQL that our backend nodes use SSL. Setting ‘use_ssl’ column in mysql_servers will do the trick. Remember that you have to load the changed configuration to runtime and eventually save it to disk.

Let’s see how it looks like now:

As can be seen above, all looks good. Now it’s time to configure ProxySQL to use keys and certificates that will allow it to connect to the backend using SSL:

Again, after setting up those variables remember to load the configuration to runtime and save it to persistent storage. Let’s see how it looks like now:

Everything looks as expected.
Once we are done with above changes, we have to restart ProxySQL service.

At this point, all new connections to host and port 6033 will use SSL.
We can verify this by executing below commands:

Using SSL creates some overhead and to understand how big it is we performed a simple read-only test using sysbench against ProxySQL with backend SSL enabled and disabled. ProxySQL was configured to use 4 internal threads and we are happy to announce that results are quite consistent.

benchmark qps
benchmark latency

For the reference, we used following sysbench command :

For more details :

Authored by : Ashwini Ahire