Important note: When testing query routing with ProxySQL using comments and the MySQL client, you have to use the "-c" command line option in order for the comment to not be stripped away when running queries. You want to preserve the comment so ProxySQL can apply the appropriate query rule to the query.These are the query rules that I have in place - where 10 is my Writer hostgroup and 20 is my reader hostgroup.-c, --comments Preserve comments. Send comments to the server. The default is --skip-comments (discard comments), enable with --comments.
Here are the MySQL Galera hosts. We have a dedicated server for write traffic, MARIADB-001, that is the only ONLINE server in hostgroup 10. And the other servers are for read traffic, MARIADB-002 and MARIADB-003, they are set to ONLINE in hostgroup 20.+---------+--------+--------------+-----------------------------+-----------------------+-----------+ | rule_id | active | match_digest | match_pattern | destination_hostgroup | multiplex | +---------+--------+--------------+-----------------------------+-----------------------+-----------+ | 10 | 1 | NULL | ^SELECT .* FOR UPDATE$ | 10 | NULL | | 20 | 1 | NULL | -- route to master | 10 | NULL | | 30 | 1 | NULL | -- route to slave | 20 | NULL | | 40 | 1 | NULL | ^SELECT | 20 | NULL | +---------+--------+--------------+-----------------------------+-----------------------+-----------+
Next I test the rules to make sure they are working correctly. We can see the query with the comment "-- route to master" goes to the primary writer server and the query with the comment "-- route to slave" goes to the read-only server.mysql> SELECT hostgroup_id, hostname, status, weight FROM runtime_mysql_servers ORDER BY hostgroup_id, weight DESC; +--------------+-------------+--------------+--------+ | hostgroup_id | hostname | status | weight | +--------------+-------------+--------------+--------+ | 10 | MARIADB-001 | ONLINE | 100 | | 10 | MARIADB-002 | OFFLINE_SOFT | 90 | | 10 | MARIADB-003 | OFFLINE_SOFT | 80 | | 20 | MARIADB-001 | OFFLINE_SOFT | 100 | | 20 | MARIADB-002 | ONLINE | 90 | | 20 | MARIADB-003 | ONLINE | 80 | +--------------+-------------+--------------+--------+
Next, I moved traffic from the current master by setting it to OFFLINE_SOFT, and then enabled one of the other MySQL Galera masters to be the new primary writer by setting it to ONLINE.MySQL [(none)]> select @@hostname; -- route to master +-----------------------------------------+ | @@hostname | +-----------------------------------------+ | MARIADB-001.us-west-2 | +-----------------------------------------+MySQL [(none)]> select @@hostname; -- route to slave +-----------------------------------------+ | @@hostname | +-----------------------------------------+ | MARIADB-003.us-west-2 | +-----------------------------------------+
I used the MySQL client and the same session by never disconnecting the client. I expected the write traffic to go to the new write master MARIADB-002 server but I was a little surprised when it did not. It continued to go to the MARIADB-001 server.UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'MARIADB-001' AND hostgroup_id = 10; UPDATE mysql_servers SET status = 'ONLINE' WHERE hostname = 'MARIADB-002' AND hostgroup_id = 10; UPDATE mysql_servers SET status = 'OFFLINE_SOFT' WHERE hostname = 'MARIADB-002' AND hostgroup_id = 20; LOAD MYSQL SERVERS TO RUNTIME; SELECT hostgroup_id, hostname, status, weight FROM runtime_mysql_servers ORDER BY hostgroup_id, weight DESC; +--------------+-------------+--------------+--------+ | hostgroup_id | hostname | status | weight | +--------------+-------------+--------------+--------+ | 10 | MARIADB-001 | OFFLINE_SOFT | 100 | | 10 | MARIADB-002 | ONLINE | 90 | | 10 | MARIADB-003 | OFFLINE_SOFT | 80 | | 20 | MARIADB-001 | OFFLINE_SOFT | 100 | | 20 | MARIADB-002 | OFFLINE_SOFT | 90 | | 20 | MARIADB-003 | ONLINE | 80 | +--------------+-------------+--------------+--------+
MySQL [(none)]> select @@hostname; -- route to master +-----------------------------------------+ | @@hostname | +-----------------------------------------+ | MARIADB-001.us-west-2 | +-----------------------------------------+
All queries that have @ in their query_digest will disable multiplexing, and will never be enabled again. Handling of switchovers from nodes gaining OFFLINE_SOFT status When multiplexing is disabled due to any of the reasons described here, an active connection will remain connected to a node that has gone the OFFLINE_SOFT status. Queries will also remain to be routed to this node. If you use a connection pool mechanism in the application, make sure you recycle your connections often enough in a Galera cluster. If an active transaction was the reason for multiplexing to be disabled, the connection is moved after the transaction has finished.
They provide remediation to this by creating a new query rule to allow this behavior if you want it:
So I created the following rule and, magically, all of my concerns have washed away and the query routing was working exactly the way that I was expecting it to.mysql_query_rules.multiplexing allows to enable or disable multiplexing based on matching criteria. The field currently accepts these values: 0 : disable multiplex 1 : enable multiplex 2 : do not disable multiplex for this specific query containing @
Then I tested again and after the failover, my queries with the "-- route to master" comment now route to the correct primary write MySQL server.INSERT INTO mysql_query_rules (rule_id,active,match_digest,multiplex) VALUES(1,'1','^SELECT @@hostname',2); LOAD MYSQL QUERY RULES TO RUNTIME;
MySQL [(none)]> select @@hostname; -- route to master +-----------------------------------------+ | @@hostname | +-----------------------------------------+ | MARIADB-002.us-west-2 | +-----------------------------------------+MySQL Database Consulting Services
Ready to optimize your MySQL Database for the future?