Route config for a lot of servers with the same database name

Polyglot Projects ProxySQL
1

Hello All.
I made a little reserch for proxy for MySQL and deciaded to use ProxySQL where I have a possibility to collect audit logs (which client IP, what to done on the database). I’m known, I can add all my servers but I can’t find in my mind how to setup the route rules for them. On these servers I have the database which have the same name on all servers. The database user name on each server is the same.
I know, that I can run one instance for one server, but I think that it is not the better way to do it.
As sum as: I need to have a proxy for mysql with possibility to collect audit logs when we have a lot of the MySQL servers with the same name of database and the same username on each server. I try to setup this as a point of monitor for support team to be able to check what which done on database. I’m able to recognize the person by client_IP (from proxysql logs).
Great, if will be able to setup read_only access for some database users or databases.
Could someone help me with proxysql configuration, how to do it or propose another solution for my requirments?

Thanks in advanced for you help.

1 Like
2

You can create routing rules based on incoming client IP “client_addr”, or you can run proxysql on multiple ports and filter via “proxy_port” or “proxy_addr”. Since all your usernames are the same and all database names are the same, this is really your only option without modifying your application code.

You could change your SQL to be something like this:

SELECT /* param:foo */ colA, colB FROM db.table WHERE colB=2

and use the match_pattern rule to regex match param:foo or param:bar, or whatever else.

2 Likes
3

@matthewb Thank you very much for your helpful answer. Regards.

1 Like