1

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.

John Gardeniers
  • 27,262
  • 12
  • 53
  • 108
Sam W.
  • 41
  • 1
  • 6
  • 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. –  Apr 08 '11 at 03:57

1 Answers1

0

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.

John Gardeniers
  • 27,262
  • 12
  • 53
  • 108
  • 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 08 '11 at 09: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 08 '11 at 09: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 08 '11 at 20:06