Route all querys against some tables to another database

[Luciano Callero]

Hi:
        I'm trying to route all of my querys (Select/Insert/Update) to certain tables (the one which contains *cache* in its name) to another database within the same server. I only need the data in the second table not in the main  one. My main goal is to change the destination of my cache data to another database without changing my app, so proxysql should do this for me.


     Could i do this? I already set up proxySql correctly but i cannot make a valid query_rule to make achieve this task.  Any direction/hint will be great.

Thanks.

[René Cannaò]

Hi Luciano,

as long as you can create regular expression to match and replace your queries, it should be possible.

Therefore, the complex part here is to be able to create regex that match all the queries.

For examples:

a) if you have queries like:

"INSERT INTO cache_table1 VALUES"

you could create a query rules like:

INSERT INTO mysql_query_rules (active,match_pattern,replace_pattern,apply) VALUES (1,'^INSERT INTO cache_table1 VALUES', ''INSERT INTO otherdb.cache_table1 VALUES',1);

b) if you have queries like:
"SELECT ... FROM cache_table2 WHERE ..."
you could create a query rules like:
INSERT INTO mysql_query_rules (active,match_pattern,replace_pattern,apply) VALUES (1,'^SELECT (.*) FROM cache_table2 WHERE', ''SELECT \1 FROM otherdb.cache_table2 WHERE',1);

Etc, etc.

Depending from the table names and the queries, some optimization could be performed. For example, if all the tables are named cache_tableXYZ, the first query rules could become:
INSERT INTO mysql_query_rules (active,match_pattern,replace_pattern,apply) VALUES (1,'^INSERT INTO cache_table(...) VALUES', ''INSERT INTO otherdb.cache_table\1 VALUES',1);

You could also write a very generic rules to rewrite all "cache_table" in "otherdb.cache_table" , but I strongly recommends not to, because if "cache_table" is part of a literal it will be replaced as well.

Two further recommendations:

1) you can use stats_mysql_query_digest to find all the queries that pass through the proxy: this will make it easier to create rules for them;

2) create a match all rule at the very end of query rules to either return an error, or just track them.

For example:

INSERT INTO mysql_query_rules(rule_id,active,match_digest,error_msg,apply) VALUES (9999, 1, 'cache_table','Blocked query, contact Luciano',1);

If instead you don't want that an error is returned to the client:
INSERT INTO mysql_query_rules(rule_id,active,match_digest,apply) VALUES (9999, 1, 'cache_table',1);

Note that in both cases I am recommending to use "match_digest" instead of "match_pattern".

To verify if there are queries that are bypassing your rules you can run this:

SELECT * FROM stats.stats_mysql_query_rules;

To understand which queries are bypassing the rules you should go back to stats_mysql_query_digest :

SELECT * FROM stats.stats_mysql_query_digest WHERE digest_text LIKE '%cache_table%' AND digest_text NOT LIKE '%otherdb%';

Finally, I wish you don't have a lot of tables and a lot of queries to rewrite! :)

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Luciano]

Excelent. It works. You were really helpfull. And of course i have a lot of cache tables :).
 

 Bye. Thanks