Wednesday, February 6, 2013

Using master and slave databases with Symfony 2 + Doctrine

One of our customers works from different locations in the Netherlands, but with their backoffice on a server in south-east Germany and an extra penalty caused by the crappy internet connection, this can sometimes be very slow.
So the first thing I thought of to solve this is to run a mirror of their backoffice on location. Setting up the MySQL replication[1] and automatic syncing of files[2] was easy. But then my code had to be adjusted to work with a Master server (the server in Germany) and a Slave server (within the local network).
For those of you unfamiliar with the concept of Master and Slave database servers: The idea is is to have multiple database servers, one Master server (who 'owns' all the data) and one, or multiple, Slave servers, who receive a copy of all the changes from the Master server. The Master server needs to be accessible to all the Slave servers (i.e. via the internet), but the Slave servers can be servers within a local network.
When I started my search on the internet for information on how to achieve this, I found many complicated solutions, including solutions that told you to change vendor code, to write custom connection classes etc. But I figured there had to be an easier way and (thankfully) I was right.