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:
- Web server A => Master database server A => Slave database server A
- 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?