How sync the MYSQL database to remote server and from remote to desktop server

Question

I make a application, which has to interfaces. one is desktop and other is web application. both have their own databases (which are same is structure).
I want to sync the database from desktop to remote server and also from remote server to desktop but i have no idea that how it does.
I use the MYSql database. and my desktop application is in .NET
NOTE: There are more than one destop systems who update their databases and also sync databases.

Answer 1

It depends where data is created and modified.

• If desktop application only shows data from central server and needs to be updated periodically, use replication.

• If you need everything to be in sync, use clustering. In this case, all clients should be online, which might not be suitable for you, in which case you'll need to...

• roll out your own custom solution (i.e. add some kind of marks/flags in the database tables, like last_updated, etc. and use those to send changes back and forth). Be prepared to deal with update conflict resolution and all other kind of problems that come with a distributed environment

Answer 2

I just implemented a similar syncing process between a desktop application and web server.

I use unique temporary primary keys when I create records on the desktop application. Therefore, when I upload the data, the server looks up the primary key, and if it doesn't exist, it creates a new record, changing the temporary primary key to a real one. The server then returns the real primary key for each record.

I use XML to upload all new records from the desktop and download all records from the server in the same HTTP transaction.

It's important to do the entire syncing process in a single transaction so in case it fails, you can roll back.

Also, our users must enter a user name and password to sync, so those parameters are also sent in the POST.

Be sure to filter and validate all data received from the client before you insert it into your database.