Currently we have MySQL master/slave configuration in one location - there are separate databases for each customer and the number is rapidly growing. I'm planing to create another pair of database servers in different location and use some sort of load balancing, like DNS round robin (DynDNS).

I'm not sure if this will actually works as there will be a pair of web servers pointing to specific database servers:

  1. Web server A => Master database server A => Slave database server A
  2. Web server B => Master database server B => Slave database server B

There won't be any replication between “cluster” A and B so if DynDNS will point the client's request to web server A however the database will be on “cluster” B this won't work?

I would like to ask for some advice what's the best solution in this case. How to configure it so I could scale it horizontally in the future?


There's a version of MySQL which supports clustering, rather than just master/slave replication. That may suit your requirements more closely.

MySQL Cluster is a write-scalable, real-time, ACID-compliant transactional database, designed to deliver 99.999% availability. With a distributed, multi-master architecture and no single point of failure, MySQL Cluster scales horizontally on commodity hardware with auto-sharding (partitioning) to serve read and write intensive workloads, accessed via SQL and NoSQL interfaces.

| improve this answer | |

Mysql Cluster would be my choice, it will enable you to grow horisontally and seprately your storage and your computing capacities: http://dev.mysql.com/doc/index-cluster.html You can also use haproxy or ldirectord to make failover. Chek this article for reference : http://flavio.tordini.org/a-more-stable-mysql-with-haproxy

| improve this answer | |
  • I know there is also Galera and Percona Xtradb cluster which is compiled with galera library however I'm not sure about the performance with multiply locations. – HTF May 22 '12 at 9:33
  • Do you know if it's possible to redirect the clients to the appropriate "cluster" (A or B) with any of the available proxy/load balancer utilities like HAProxy, Nginx or this should be done on the application level? – HTF May 22 '12 at 10:55

One more question regarding user sessions. If I use DNS load balancing and the client will be inserting some data on the website what will happen if he/she will hit the refresh button and the the DNS record will change? Is this going to work or I need some better load balancing utlity like Haproxy or Nginx?


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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