I'm working on a web application that exists at two sites. Each system is hosted on it's own LAMP stack, with access restricted to specific users on the network.
When content is updated via user input on one side, an XML is transferred from that system to the other to update it, so both systems match.
These files are transferred by a 'guaranteed delivery' system. However, sometimes the XMLs get bunched up and arrive in the wrong order, causing problems at the receiving end.
This is particularly noticable with rapid user input, as the delay from the transmission system causes the files to 'bump into eachother'.
It was suggested that database replication would be more efficient to keep the systems in sync.
My question is: Is replication suitable for small incremental changes, does it have to update every single item each time or just the part that has changed?
Another question I have is, what protocol would the databases use to communicate from two separate sites?
Best Answer
Replication was made for small changes, so you're on the right track.
To keep the systems in sync, at the database level, you will need to setup a mysql master-master replication scenario. I've used the tutorial from this linke (http://howtodba.com/how-to-build-a-cheap-web-cluster-with-replicated-storage-and-mysql-master-master-configuration/), to setup such configuration.
Depending on how much data is changed, you can choose between [statement] or [row-based] replication. More info about the differences between the 2 types: http://www.databasejournal.com/features/mysql/article.php/3922266/Comparing-MySQL-Statement-Based-and-Row-Based-Replication.htm