In a similar line to...
What I'd like to know is whether there are any really good proxies for MySQL out there? Part of my platform is a "shared hosting" environment and customers typically configure (as an example) their application to talk to 'mysql4-7.db.domain.tld' at the moment.
Unfortunately this makes upgrades an, "All or nothing" approach and testing is tricky.
What I'd like to do is to point everyone at a highly available set of proxies, have those handle authentication and pass on queries to the appropriate database server.
Features I'd love to see;
- configurable from a database
- some kind of shared state, so we can do seamless failover
- not a total drag on performance
- additional logging/metrics to identify heavy users/databases, should be possible given the proxy will see all queries going through?
- ability to listen upon multiple IP addresses and ports
The end goal is to be able to move users over a single database at a time, so we can handle upgrades in stages and mostly without customers noticing or caring. Please bear in mind we don't write the applications for these guys, nor provide "in depth" technical support (to the level of application configuration) and some of our users are pretty clueless. Hence this train of thought ;)
I have considered creating DNS records for each database similar to:
Then pointing those at the correct database server, we do have a constraint that database names (across all DB platforms) must be unique... Is there any better solution?
Happy to answer questions. Thanks for your time!