Best way to Sync data

jrahma

Hi,

I have a WinForm C# application with MySQL backend on the server in the company. The application needs to sync with a database on Web.

I thought of adding a is_synced field for the fields needs to be updated but then how can I sync? do I need to SELECT using the first mysql_local_connection and loop through avalable records then connect within the loop to mysql_cloud_connection and do the INSERT?

--> connect to mysql_local_connection
--> SELECT records need to be synced

--> While looping
--------> connect to mysql_cloud_connection
--------> INSERT or UPDATE
--> end loop

is this the best way to do it?

Thanks,
Jassim

sneakyimp

Keeping an app in sync with a database can be tricky if the database is serving more than one client (e.g., like a web server with many simultaneous users). If user 1 queries the db and gets a list of records which are stored in memory, then users 2-N all start making changes to the database, then user 1's list of records is going to get out of date very rapidly. There's not really an easy solution to this problem. You can use transactions (see START TRANSACTION, COMMIT, and ROLLBACK which are meant to handle this exact problem).

Derokorian

You'll also probably want to issue one insert query with many rows, instead of many insert queries if possible - for better performance. Also, you definitely should NOT be connecting within the loop.

jrahma

Does anyone has a sample code if I want to sync between two databases servers located at different locations / countries (so different hostname)

sneakyimp

https://dev.mysql.com/doc/refman/5.5/en/replication-howto.html