ProxySQL Backend SSL Connection Error

Polyglot Projects ProxySQL
1

We have configured ProxySQL with Orchestrator to allow a front-end client to connect to two Percona servers running master slave replication. When disabling backend SSL, there are no connection issues between ProxySQL and Percona:

+-------------------------+------+------------------+-------------------------+---------------+
| hostname                | port | time_start_us    | connect_success_time_us | connect_error |
+-------------------------+------+------------------+-------------------------+---------------+
| master_percona_server  | 3306 | 1657560485825536 | 8610                    | NULL          |
| slave_percona_server | 3306 | 1657560484730191 | 36817                   | NULL          |
+-------------------------+------+------------------+-------------------------+---------------+

However, when enabling SSL on all servers and specifying both mysql-ssl_p2s_cert and mysql-ssl_p2s_key in ProxySQL, the following errors occur:

+-------------------------+------+------------------+-------------------------+----------------------------------------------+
| hostname                | port | time_start_us    | connect_success_time_us | connect_error                                |
+-------------------------+------+------------------+-------------------------+----------------------------------------------+
| master_percona_server | 3306 | 1657560922780474 | 0                       | Lost connection to MySQL server during query |
| slave_percona_server | 3306 | 1657560921681621 | 0                       | Lost connection to MySQL server during query |
+-------------------------+------+------------------+-------------------------+----------------------------------------------+

These are things we have already considered:

  • Copied LetsEncrypt Certbot fullchain.pem and privkey.pem into /var/lib/proxysql
  • chmod 600 fullchain.pem and privkey.pem
  • chown proxysql:proxysql fullchain.pem and privkey.pem
  • No errors when starting ProxySQL ([INFO] SSL keys/certificates found in datadir (/var/lib/proxysql): loading them)

Questions:

  1. Does anyone have any thoughts on why might coming across this connection error?
  2. We also have SSL enabled on the front-end client. When connecting to Percona through ProxySQL without specifying the path of mysql-ssl_p2s_cert and mysql-ssl_p2s_key in ProxySQL:
$ mysql -h127.0.0.1 -P6033 -utest_user -ptest_password -e 'SHOW SESSION STATUS LIKE "Ssl_cipher"'

The output is:

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+

This should indicate that the connection session is encrypted. Therefore, is it even necessary to configure backend SSL on ProxySQL?

1 Like
2

Do you have use_ssl set to 1 in mysql_servers?
Did you set any of the mysql_admin parameters to the SSL filenames?

1 Like
3

Thanks for your response @matthewb,

We’ve set use_ssl to 1 on mysql_servers, ensuring that the servers were loaded to runtime and saved to disk. This was also confirmed when using the command:

SELECT * FROM mysql_servers;

In my.cnf on both the MySQL server that is running on the same server as ProxySQL and the Master-Slave Percona servers, we have added in the lines:

[mysqld]
admin-ssl=ON
admin_ssl_cert=/var/lib/mysql/fullchain.pem
admin_ssl_key=/var/lib/mysql/privkey.pem

The connection error still persists.

1 Like
4

admin-ssl* parameters are only valid for the admin connection port. :slight_smile: Hence their name. You need to use the standard ssl* parameters in your my.cnf to affect the standard connections.

2 Likes
5

Oh I see, thanks for letting me know about that @matthewb!

On another note, I realized that the connection error was through MySQL’s configuration of the ssl-ca. We’re using Let’s Encrypt and there was a whole set of things that needed to be done in order to get the ssl-ca to work. After fixing this the connection between ProxySQL and Percona with SSL enabled seems to work.

Source (Even though the solution slightly stray from the direction this thread was originally going, I’ll leave it here in case anyone else finds themselves in a similar situation)

2 Likes