I have a production environment setup where my drupal site is hosted on two servers, mapped to the same domain, each with a copy of code and database, running on XAMPP. So basically, they are two independent entities.
Now I have users accessing the site through the domain, making updates, which go to either one of the servers and I find that keeping the databases in sync is a real problem.
What are my options? Is there a way to 'merge' the databases and retain all data?
You can resolve this problem in several way:
Use only one database server. Disable one mysql server and connect drupal on both servers to one mysql server. This is not good solution if your database is havely loaded.
U can set up master to master mysql replication. In this scenario you will be able to use both mysql servers and all updates will be replicated to both of mysql servers. This will nicely balance load to mysql database, without any changes to drupal code.
You can use mysql replication master slave. Here you must modify drupal code that all updates on database will be done on master server and than replicated to slave. That is good solution if you have small amount of updates on database because this solution can't balance input and updates on database on both mysql. But master slave replication is much more simpler to configure and maintance.
U can set up third server for mysql database only and connect both sites to the same mysql server. You can than configure you websevers to be slaves of master database only for backup purposes
I think you need to look at master-master/multi-master replication. This is where both servers have databases which are masters and each update will commit to the database then replicate through to the other database keeping your databases in sync.
http://en.wikipedia.org/wiki/Multi-master_replication
http://xeround.com/blog/2011/10/master-master-database-replication
