SSL Encryption at ProxySQL Part 2

  • Date:
  • Tags: proxy mysql frontend backend ssl tls proxysql gpl 2.0

This is the second part of a two part series. In SSL Encryption at ProxySQL Part 1 we have seen how to enable backend SSL encryption for ProxySQL. In this second article, we are going to see how to enable frontend SSL, so client can establish a secure remote connection towards a ProxySQL instance.

In a standard configuration, a client connection is unencrypted, which can lead to data being intercepted on the way. In previous versions of ProxySQL it was recommended to deploy ProxySQL on your application servers and use sockets to secure traffic however starting from version 2.0 Frontend SSL connections are available.

ProxySQL SSL

Note : Frontend SSL is available from ProxySQL v2.0+

Verify encryption on ProxySQL

Before going ahead let's make sure connections are not using SSL encryption.

By default, ProxySQL listens for MySQL Traffic on 0.0.0.0:6033 and ProxySQL Admin commands on 0.0.0.0:6032 (used for monitoring and configuration).

        # When Frontend SSL is disable
        $ mysql -h127.0.0.1 -P6033 -usysbench -psysbench -e '\s' | grep -P 'SSL|Connection'
        Connection id:      5
        SSL:            Not in use
        Connection:     127.0.0.1 via TCP/IP

As the above output indicates, SSL is not currently in use for our connection, even though we are connected over TCP. Assuming you already have configured backend SSL from last blog post and now we can see SSL is currently in use for backend connections.

        # When Backend SSL is enabled :
        $ mysql -h127.0.0.1 -P6033 -usysbench -psysbench -e 'SHOW SESSION STATUS LIKE "Ssl_cipher"'
        +---------------+----------------------+
        | Variable_name | Value                |
        +---------------+----------------------+
        | Ssl_cipher    | ECDHE-RSA-AES256-SHA |
        +---------------+----------------------+

Enabling encryption on ProxySQL

Now lets enable frontend SSL by using ProxySQL's Admin interface.

        UPDATE global_variables SET variable_value='true' WHERE variable_name='mysql-have_ssl';
        OR 
        SET mysql-have_ssl='true';

Load this configuration to runtime and save on disk if you want to make changes persistent.

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

Note : Changes will only apply on new client connection.

Lets go ahead and verify this by executing the following commands:

        $ mysql -h127.0.0.1 -P6032 -uadmin -padmin -e '\s' | grep -P 'SSL|Connection'
        SSL:            Cipher in use is DHE-RSA-AES256-SHA
        Connection:     127.0.0.1 via TCP/IP

        $ mysql -h127.0.0.1 -P6033 -usysbench_FB -psysbench -e '\s' | grep -P 'SSL|Connection'
        SSL:            Cipher in use is DHE-RSA-AES256-SHA
        Connection:     127.0.0.1 via TCP/IP

As we can see SSL Cipher in use while making frontend connections towards ProxySQL.

Supported protocol/ciphers for frontend SSL:

  • SSLv2
  • SSLv3
  • TLSv1
  • TLSv1.1
  • TLSv1.2

Supported ciphers:

  • DHE-RSA-AES256-GCM-SHA384
  • DHE-RSA-AES256-SHA256
  • DHE-RSA-AES256-SHA
  • DHE-RSA-CAMELLIA256-SHA
  • AES256-GCM-SHA384
  • AES256-SHA256
  • AES256-SHA
  • CAMELLIA256-SHA
  • DHE-RSA-AES128-GCM-SHA256
  • DHE-RSA-AES128-SHA256
  • DHE-RSA-AES128-SHA
  • DHE-RSA-SEED-SHA
  • DHE-RSA-CAMELLIA128-SHA
  • AES128-GCM-SHA256
  • AES128-SHA256
  • AES128-SHA
  • SEED-SHA
  • CAMELLIA128-SHA
  • DES-CBC3-SHA

We have performed a simple read-only test using sysbench against ProxySQL with backend/frontend SSL enabled and disabled. ProxySQL was configured to use 8 internal threads.

ProxySQL SSL 1
ProxySQL SSL 2

From above graphs we can see that encryption has not added any major overhead and not impacted much on performance, making it safe to use encryption and eliminate data flowing insecurely through the network.

For reference, we used following sysbench one-liner commands :

Backend SSL Test :

        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/backend_ssl/v2.0_8threads${conn}connections.${i}_line.log ; done ; done

Frontend SSL Test :

        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 --mysql-ssl=on --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 --mysql-ssl=on --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/frontend_backend/v2.0_8threads${conn}connections.${i}_line.log ; done ; done

Happy ProxySQLing !

Authored by: Ashwini Ahire & Nick Vyzas

If you have any questions please do not hesitate to contact us. Our performance and scalability experts will help you to analyze your infrastructure and help to build fast and reliable architecture. We also offer long term support and DBRE consulting for ProxySQL users.