Using MySQL Workbench to Connect Through ProxySQL 2

Posted in: MySQL, Open Source, Technical Track

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ò:

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 the caching_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) uses utf8mb4 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 utf8mb4utf8mb4_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.

Conclusion

Thank you René for your comments. They definitely encouraged me to get this post done much sooner than later. ProxySQL 2.0 is out-of-the-box compatible with MySQL Workbench 8.0. No more tweaks are required (as opposed to using version 1.4, as described in my previous post).

Additionally, with 2.0 we gain the ability to use full end-to-end encryption to protect our data in flight. It’s time to upgrade your ProxySQL installation and enable these features. They will make the internet a little bit of a safer place.

Stay safe!

email

Interested in working with Matthias? Schedule a tech call.

About the Author

Lead Database Consultant
Matthias has been passionate about computers since the age of 10. He has been working with them ever since. Currently he's a Lead Database Consultant in one of the MySQL teams at Pythian where he's the technical lead for his team. Together with his team he works to provide the best possible service to the customers.

1 Comment. Leave new

Pedro Vidigal
May 20, 2020 12:57 pm

Great blog Matthias!

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *