Proxying MySQL

Discussion in 'ISPConfig 3 Priority Support' started by BobGeorge, Oct 3, 2018.

  1. BobGeorge

    BobGeorge Member HowtoForge Supporter

    We've added another node to our cluster and I've turned it into our dedicated DB server.

    What I was thinking about was installing a proxy - something like ProxySQL or whatever - on the other nodes. Then when those nodes access "localhost:3306", it's actually getting proxied to the DB server.

    This neatly solves my earlier issue of the APS package installer presuming "localhost", when I actually needed it to use the IP address of the storage server (which was the db server at the time), which required me to hack the PHP to force the right IP address for the APS installer after every update. With a proxy, then "localhost" would actually now be correct and the proxy will do the job of passing it onto the DB server.

    ...or, indeed, DB servers. Because another handy thing about using a proxy is that, when we obtain more DB servers and form a DB cluster, it can also do the job of load balancing between them (and replication / galera can keep them all in sync).

    And a proxy can also do some query caching to reduce network traffic a little and hopefully slightly improve performance.

    The problem is this. ISPConfig itself uses a local database - "dbispconfig" - and, thus, when accessing "localhost:3306" for the "dbispconfig" database, it should get its own local database for ISPConfig to work correctly (and I guess the MySQL user also has to be correct, so that it has permissions to access it too and that user is in the localhost "mysql.user" database), but all other database requests ought to go via the proxy to the DB server.

    So I'm wondering if there's a way to do this. A MySQL proxy that'll pass on (and eventually load balance) all the DB requests, except specifically for "dbispconfig" on localhost, which should actually be handled locally.

    I mean, one possibility is to place all the ISPConfig databases on the DB server - suitably renamed as "dbispconfig1", "dbispconfig2" or whatever, so that there;s no conflict - and then reconfigure each node to its respective database on the DB server. So then all DB requests can be proxied.

    But I'd rather not. Because that's a lot of messing around. And it would mean that I'd have to continue doing this strange configuration as new nodes are added to the cluster and everything would be loaded onto the DB server and require network access every time - in other words, it'd be a major "single point of failure" too.

    It's a possibility. But not at all desirable, so I'd rather not.

    So what I'm looking for is, as I say, this ability to do "if (db == 'dbispconfig') { localhost } else { proxy }". Or, more generally, a way to proxy most traffic to the DB server, but selectively exempt some databases for "localhost" to deal with.

    (And I guess, as the proxy will grab port 3306, then the localhost MySQL would have to be shifted to a different port. So the proxy grabs it all and then passes specific database queries to "localhost" on that different port.)

    I've not committed to any particular proxy at the moment - though I've been looking at ProxySQL - so if one is better than another at something like this, I'm all ears.
     

Share This Page