I have 2 offices that are located around 500km apart and both offices need to access the MySQL database frequently. So to make it faster I am planning to set up two MySQL servers where each office has one server then make these two MySQL servers act as master/master replication so both offices can read/write with updated data.

Now I am wondering whether this is the right solution or there is a better one? Please advise.

  • This solution cannot be answered without knowing the volume of data or the size of your pipe between the two offices. Please provide more information. – user3914 Apr 8 '11 at 3:57

Master/master replication is certainly the way to achieve the desired results but you need to take into consideration possibly lag resulting from a combination of bandwidth and the amount of data that is to be transferred to maintain syncronisation.

  • This come across my mind too. Now that I know I can achieve using Master/Master replication, I need to test it so I can measure how much bandwidth and data is involve. Thank you. Can you suggest the best way to monitor/measure this? This is slightly new to me – Sam W. Apr 8 '11 at 9:27
  • There are no end of tools available for this (mrtg, pastmon, iptraf) - or you could just switch on replication now to a non-existent slave and see how quickly the bin files grow. NB you probably should consider wrapping up the connection in a VPN. – symcbean Apr 8 '11 at 9:31
  • @Sam, in real terms the actual bandwidth and traffic volume don't need to be measure in numbers, as all that really matters is the lag. symcbean suggestions are all good. If you don't already have MRTG, or similar, another way is to write a script which makes changes to one server and repeatedly queries both servers and measures how long it takes before both return the same result. I you would run this periodically, save every minute or so, for a few days to get a good feel for the results as the network usage changes. However, long term you should really have an automated monitoring system. – John Gardeniers Apr 8 '11 at 20:06

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.