Two-way replication with MySQL to sync instances of a database app between multiple offline clients?

Question

I am tasked with building an application whose database backend needs to be replicated in both directions over an unknown number of clients who are usually offline. Before I explain in detail, my question is whether MySQL replication is feasible for this project or if I should look into other technologies.

Here's the general use case:

• User installs software on personal laptop.
• User loads data from the master database server onto her laptop.
• User disconnects from the network and proceeds to operate on this data remotely.
• User remotely connects back to the master database server to commit her changes and retrieve any changes that have since been committed by other people (two-way synchronisation).

I have no experience with database replication of any kind, so this is quite an interesting challenge. Can MySQL replication accomplish what I'm looking for? Or, do you know of another technology that will accomplish this more effectively that MySQL? I've read through the docs ( http://dev.mysql.com/doc/refman/5.0/en/replication.html ) and it looks like it is more geared towards master/slave replication.

Answer 1

MySQL supports one-way replication only.

While this doesn't directly answer your question, MS SQL Server 2008 supports this scenario (merge replication) very well.

In fact, you can use the free MS SQL Server 2008 Express on clients, reducing the need to have multiple SQL Server licenses.

Also, take a look at the Microsoft Sync Framework.

SQL Express - Client Synchronization Sample on MSDN

Microsoft Sync Framework Support in Visual Studio 2008

There are also forums here: SyncFx - Microsoft Synchronization Services

Answer 2

The type of multi-master replication that you are describing is one of the primary use cases for CouchDB.

CouchDB Technical Overview