MySQL, ProxySQL & SSL

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 blog covers 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.
https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html

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

    mysql> show global variables like '%ssl%';
    +---------------+-----------------+
    | Variable_name | Value           |
    +---------------+-----------------+
    | have_openssl  | YES             |
    | have_ssl      | YES             |
    | ssl_ca        | ca.pem          |
    | ssl_capath    |                 |
    | ssl_cert      | server-cert.pem |
    | ssl_cipher    |                 |
    | ssl_crl       |                 |
    | ssl_crlpath   |                 |
    | ssl_key       | server-key.pem  |
    +---------------+-----------------+

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

GRANT ALL PRIVILEGES ON *.* TO 'sysbench'@'172.16.1.%' IDENTIFIED BY 'sysbench' REQUIRE SSL;

Test a secure connection:

root@proxysql-host:/data/benchmark/v2.0# mysql -usysbench -psysbench -h 172.16.1.102 -P3306 -e '\s' | grep SSL  

SSL:  Cipher in use is DHE-RSA-AES256-SHA  

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.

root@mysql-host:/data/mysql# ls -lhtr *.pem  
-rw-r--r-- 1 mysql mysql 1.1K Mar 22 08:07 ca.pem
-rw------- 1 mysql mysql 1.7K Mar 22 08:07 ca-key.pem
-rw------- 1 mysql mysql 1.7K Mar 22 08:07 server-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Mar 22 08:07 server-cert.pem
-rw------- 1 mysql mysql 1.7K Mar 22 08:07 client-key.pem
-rw-r--r-- 1 mysql mysql 1.1K Mar 22 08:07 client-cert.pem
-rw-r--r-- 1 mysql mysql  452 Mar 22 08:07 public_key.pem
-rw------- 1 mysql mysql 1.7K Mar 22 08:07 private_key.pem

Once you are done, restart MySQL servers.

Enable Encryption on ProxySQL

At this stage, connection attempts to host 127.0.0.1 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/

mysql> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%ssl%';
+-------------------------------------+----------------+
| variable_name                       | variable_value |
+-------------------------------------+----------------+
| mysql-have_ssl                      | false          |
| mysql-session_idle_show_processlist | true           |
| mysql-ssl_p2s_ca                    | (null)         |
| mysql-ssl_p2s_cert                  | (null)         |
| mysql-ssl_p2s_key                   | (null)         |
| mysql-ssl_p2s_cipher                | (null)         |
+-------------------------------------+----------------+
6 rows in set (0.00 sec)

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

UPDATE mysql_servers SET use_ssl=1 WHERE port=3306;  
LOAD MYSQL SERVERS TO RUNTIME;  
SAVE MYSQL SERVERS TO DISK;

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:

mysql> select hostgroup_id,hostname,port,status,max_connections,use_ssl from mysql_servers;
+--------------+--------------+------+--------+-----------------+---------+
| hostgroup_id | hostname     | port | status | max_connections | use_ssl |
+--------------+--------------+------+--------+-----------------+---------+
| 0            | 172.16.1.103 | 3306 | ONLINE | 3000            | 1       |
| 0            | 172.16.1.104 | 3306 | ONLINE | 3000            | 1       |
| 0            | 172.16.1.105 | 3306 | ONLINE | 3000            | 1       |
| 0            | 172.16.1.102 | 3306 | ONLINE | 3000            | 1       |
+--------------+--------------+------+--------+-----------------+---------+
4 rows in set (0.00 sec)

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:

SET mysql-ssl_p2s_cert="/var/lib/proxysql/client-cert.pem";  
SET mysql-ssl_p2s_key="/var/lib/proxysql/client-key.pem";  
SET mysql-ssl_p2s_ca="/var/lib/proxysql/ca.pem";  
SET mysql-ssl_p2s_cipher='ECDHE-RSA-AES256-SHA';  

LOAD MYSQL VARIABLES TO RUNTIME;  
SAVE MYSQL VARIABLES TO DISK;  

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:

mysql> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql%ssl%';
+-------------------------------------+-----------------------------------+
| variable_name                       | variable_value                    |
+-------------------------------------+-----------------------------------+
| mysql-have_ssl                      | false                             |
| mysql-session_idle_show_processlist | true                              |
| mysql-ssl_p2s_ca                    | /var/lib/proxysql/ca.pem          |
| mysql-ssl_p2s_cert                  | /var/lib/proxysql/client-cert.pem |
| mysql-ssl_p2s_key                   | /var/lib/proxysql/client-key.pem  |
| mysql-ssl_p2s_cipher                | ECDHE-RSA-AES256-SHA              |
+-------------------------------------+-----------------------------------+
6 rows in set (0.00 sec)

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 127.0.0.1 and port 6033 will use SSL.
We can verify this by executing below commands:

root@sysbench-host:/data/benchmark/v2.0# mysql -h127.0.0.1 -P6033 -usysbench -psysbench  
mysql: [Warning] Using a password on the command line interface can be insecure.  
Welcome to the MySQL monitor. Commands end with ; or \g.  
Your MySQL connection id is 2  
Server version: 5.5.30 (ProxySQL)  
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.  

mysql> SHOW SESSION STATUS LIKE 'Ssl_cipher';
+---------------+----------------------+
| Variable_name | Value                |
+---------------+----------------------+
| Ssl_cipher    | ECDHE-RSA-AES256-SHA |
+---------------+----------------------+
1 row in set (0.00 sec) 

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.

SSL-Latency

For the reference, we used following sysbench command :

for conn in 1 8 128 ; do for i in $(seq 1 3) ; do echo "${conn}:${i}"; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench --mysql-password=sysbench --db-driver=mysql --mysql-host=127.0.0.1 --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=60 run ; ulimit -n 1048576; ./src/sysbench ./src/lua/oltp_read_only.lua --db-debug=on --report-interval=20 --table-size=70000000 --tables=20 --mysql-db=sbtest_rw --mysql-user=sysbench --mysql-password=sysbench --db-driver=mysql --mysql-host=127.0.0.1 --max-requests=0 --mysql-port=6033 --db-ps-mode=disable --skip-trx=on --threads=${conn} --max-time=120 run |tee /data/benchmark/v2.0/v2.0_4threads${conn}connections.${i}_line.log ; done ; done

For more details : https://github.com/sysown/proxysql/wiki/SSL-Support

Authored by : Ashwini Ahire