Proxysql query routing

By SysAdmin1138 on

The proxysql project doesn't have much documentation around their query engine, even though it is quite powerful. What they do have is a table-schema for the query-rules table, and figuring out how to turn that into something useful is left as an exercise for the reader. It doesn't help that there are two ways to define the rules depending on how you plan to use proxysql.

For the on-box usecase, where proxysql is used as a local proxy for a bunch of DB-consuming processes, the config of it is likely a part of whatever you're using for configuration-management. Be that Docker, Puppet, Chef or something else. Fire once, forget. For this usecase, a config-file is most convenient.

mysql_query_rules =
(
  {
    rule_id               = 1
    active                = 1
    username              = "read_only_user"
    destination_hostgroup = 2
  },
  {
    rule_id               = 2
    active                = 1
    schemaname            = "cheese_factory"
    destination_hostgroup = 1
  }
)

Two rules. One says that if the read-only user is the one logging in, send it to the second hostgroup (which is the read-only replica). The other says that if the "cheese_factory" database is being accessed, use the first hostgroup. Seems easy. For the on-box usecase, changing rules is as easy as rolling a new box/container.

However, the other way to define these is through a SQL interface they built. This usecase is more for people operating a cluster of proxysql nodes and need to change rules and configuration on the fly with no downtime. It's this method that all of their examples are written in.

Which leaves those of us using the config-file to scratch our heads.

INSERT into mysql_query_rules (rule_id, active, username, destination_hostgroup)
VALUES (1,1,"read_only_user",2);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

INSERT into mysql_query_rules (rule_id, active, schemaname, destination_hostgroup)
VALUES (2,1,"cheese_factory",1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

These two ways of describing a rule do the same thing. If you're writing a config-management thingy for an on-box proxysql, the first is probably the only way you care about. If you're building a centralized one, the second one is the only one you care about.

For those of you looking to make the translation, or looking for the config-file schema, each of those column names in the table-schema can be a value in the mysql_query_rules array.

  • Different lines are ANDed together.
  • Rules are processed in the rule_id order.
  • The first match wins, so put your special cases in with low rule_id numbers, and your catch-alls with high numbers.
    • The flagIN, flagOUT, and apply columns allow you to get fancy, but that's beyond me right now.

Categories: