In a similar line to...
http://serverfault.com/questions/35899/mysql-5-1-replication-topologies-multi-master
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:
databasename.db.domain.tld
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!
A cheap and cheerful way to use the DNS solution would be to incorporate something specific to the account in the db name. A client wouldn't have any expectation of duplicated database names on one account, and this would be simple to generate, as you would have to have the list by-customer available via your provisioning system.
If you go down this route, I'd suggest using something that can never change from the account, like the account number (rather than the management interface login name for example). Otherwise if you ever have to do a name change for any reason, you have to update the DNS everywhere, or have some system to grandfather the old names for reasonable period of time.
mysqlproxy unfortunately comes with performance costs. but you can script in it quite advanced things. maybe you want to use it just temporarily during shuffling dbs around.
i only used it in test env, i do not have it running in production.