So, I admit the title for this post is a bit ambiguous. Not only is it the second post I've written in a short period of time, but it's also focused on ProxySQL 2.0. As promised in the
previous post, I've upgraded my testing environment to ProxySQL 2.0 and was interested to see if there are any differences in behaviour from the previous version. As it turns out, there are. In the comments section of the previous post, you'll find a comment by ProxySQL Founder and CEO, René Cannaò:
We can confirm the previous configuration still works.
Great! Time to try and break it. Let's remove the
And, yay! It now works on a default configuration.
Note: If you want to use the newer more extended charset
To make sure that you always connect using SSL I recommend changing the "Use SSL" option to "Require". This way you can be sure that SSL is always used. It ensures that you receive an error if it isn't available, so you know right away if something is wrong.
In my test environment, I'm using self-signed certificates. This means that the identity of the certificate is only verified by yours truly, and not by a trustworthy certification authority. If you want to be sure about the identity of the server to which you are connecting, an externally signed certificate by a trusted certification authority is required. Keep that mind. A quick verification using the status variables shows us that our backend connection is also still using SSL (as we expected), so we're currently encrypting the entire flow of our data.
" Indeed ProxySQL doesn’t support caching_sha2_password authentication plugin, but since ProxySQL 2.0.3 (March 2019) a client connection using caching_sha2_password will be automatically switched to mysql_native_password. With regards to “OPT_CHARSET_NAME=utf8”, the problem is that your backend it is not MySQL 8.0 : the client is trying to use a charset (likely utf8mb4_0900_ai_ci) that the backend doesn’t support. ProxySQL 2.0.9 automatically fixes this, switching to the default charset defined in mysql-default_charset."René basically told us that ProxySQL 2.0 will resolve all the issues we faced while connecting through ProxySQL 1.4. Let's verify that!
Password Authentication Plugin
My Workbench version is still 8.0.18. As we figured out last time, it uses thecaching_sha2_password plugin by default and we had to change it to
mysql_native_password. René mentioned in his comment that, starting with ProxySQL 2.0.3, the proxy automatically switches to the
mysql_native_password option. I'm using version 2.0.10 (the latest version at time of writing this) so this should be working. First, I'll verify the original configuration with both
defaultAuth and
OPT_CHARSET_NAME configured in MySQL Workbench.
We can confirm the previous configuration still works.
Great! Time to try and break it. Let's remove the
defaultAuth option from the settings. It should connect just fine (which it does), as anticipated after René's comment.
Default Charset Configuration
The other change to the configuration we had to make was to select a character set. As it was pointed out to me, MySQL 8 (and thus MySQL Workbench 8.x too) usesutf8mb4 as the default character set with collation
utf8mb4_0900_ai_ci. However, this combination is not supported on my MySQL backend running 5.7.29.
mysql> SELECT @@version, @@hostname; +------------+------------+ | @@version | @@hostname | +------------+------------+ | 5.7.29-log | mysql1 | +------------+------------+ 1 row in set (0.00 sec) mysql> SHOW COLLATION WHERE Charset = 'utf8mb4'; +------------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +------------------------+---------+-----+---------+----------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | +------------------------+---------+-----+---------+----------+---------+ 26 rows in set (0.00 sec) So once again as René mentioned in his comment ProxySQL 2.0 has a solution for this namely the setting mysql-default_charset. In my test environment this is set to the default value 'utf8'. Admin> SELECT @@version; +--------------------+ | @@version | +--------------------+ | 2.0.10-27-g5b31997 | +--------------------+ 1 row in set (0.00 sec) Admin> SHOW VARIABLES LIKE 'mysql-default_charset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | mysql-default_charset | utf8 | +-----------------------+-------+ 1 row in set (0.01 sec)
Once again, as René mentioned in his comment, ProxySQL 2.0 has a solution for this (namely the
mysql-default_charset
setting).
In my test environment, this is set to the default value 'utf8'.
Admin> SELECT @@version; +--------------------+ | @@version | +--------------------+ | 2.0.10-27-g5b31997 | +--------------------+ 1 row in set (0.00 sec) Admin> SHOW VARIABLES LIKE 'mysql-default_charset'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | mysql-default_charset | utf8 | +-----------------------+-------+ 1 row in set (0.01 sec)Time to remove the
OPT_CHARSET_NAME configuration and try again.
And, yay! It now works on a default configuration.
Note: If you want to use the newer more extended charset
utf8mb4 to replace
utf8, you can just update the
mysql-default_charset variable to
utf8mb4 and everything still works. It uses the 5.7 default collation for
utf8mb4 '
utf8mb4_general_ci'. Thanks to René for pointing out these issues, and thank you for all the work your team is putting into the tool to make it better and more robust. But wait. There's more...
SSL All the Things!
Each of my tests are returning the following:SSL: not enabled. Now that we are on ProxySQL 2.0, we can leverage its full end-to-end encryption for both frontend and backend connections. I've checked the
ProxySQL manual to see what we need to change to enable SSL on the frontend AND backend connections. For the backend connections, we need to flip the
use_ssl flag in the
mysql_servers table to
1. Note that this feature was already available in 1.4. However, 2.0 makes it easier for us to configure by pre-creating the SSL certificates at initial startup.
Admin> SELECT hostgroup_id, hostname, use_ssl FROM mysql_servers; +--------------+-------------+---------+ | hostgroup_id | hostname | use_ssl | +--------------+-------------+---------+ | 100 | 192.168.0.2 | 0 | | 101 | 192.168.0.3 | 0 | | 101 | 192.168.0.4 | 0 | +--------------+-------------+---------+ 3 rows in set (0.01 sec) Admin> UPDATE mysql_servers SET use_ssl = 1; Query OK, 3 rows affected (0.00 sec) Admin> LOAD MYSQL SERVERS TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin> SAVE MYSQL SERVERS TO DISK; Query OK, 0 rows affected (0.06 sec) Let's verify if our backend connection is using SSL $ mysql -h192.168.56.2 -P33061 -uapp_rw -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.5.30 (ProxySQL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 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 | DHE-RSA-AES256-GCM-SHA384 | +---------------+---------------------------+ 1 row in set (0.00 sec) mysql>
However, the frontend connection is not yet using SSL.
mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.22, for osx10.12 (x86_64) using EditLine wrapper ... Current user: app_rw@192.168.0.8 SSL: Not in use ... Time to enable frontend SSL. The ProxySQL manual again teaches us to set the variable mysql-have_ssl to true (default value is still false). Let's try: Admin> SHOW VARIABLES LIKE 'mysql-have_ssl'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | mysql-have_ssl | false | +----------------+-------+ 1 row in set (0.00 sec) Admin> SET mysql-have_ssl = 'true'; Query OK, 1 row affected (0.00 sec) Admin> LOAD MYSQL VARIABLES TO RUNTIME; Query OK, 0 rows affected (0.00 sec) Admin> SAVE MYSQL VARIABLES TO DISK; Query OK, 143 rows affected (0.01 sec) Admin> SHOW VARIABLES LIKE 'mysql-have_ssl'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | mysql-have_ssl | true | +----------------+-------+ 1 row in set (0.00 sec)ProxySQL now supports frontend SSL connections. Let's check MySQL Workbench to verify this. Indeed, the default setting in Workbench is to use SSL whenever available, so we don't need to change anything. Et voila! SSL connectivity to our proxy server.
To make sure that you always connect using SSL I recommend changing the "Use SSL" option to "Require". This way you can be sure that SSL is always used. It ensures that you receive an error if it isn't available, so you know right away if something is wrong.
In my test environment, I'm using self-signed certificates. This means that the identity of the certificate is only verified by yours truly, and not by a trustworthy certification authority. If you want to be sure about the identity of the server to which you are connecting, an externally signed certificate by a trusted certification authority is required. Keep that mind. A quick verification using the status variables shows us that our backend connection is also still using SSL (as we expected), so we're currently encrypting the entire flow of our data.
