Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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!

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Accounts can have multiple databases, on different boxes, which makes this a somewhat unsuitable PK for this endeavour. I do think a good proxy solution will be the most elegant solution, allowing load to be balanced over multiple MySQL 5.x boxes as user patterns and activity change over time. I just haven't found the right proxy yet :) –  nixgeek Jul 4 '09 at 18:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.