Some General Database Syncing Questions

[peterlaursen]

Please read this article:

http://www.webyog.com/en/whitepapers/Using…L_Databases.pdf

.. it is not up to date but basics are the same

1) Yes .. the two MySQL servers and SQLyog can run on 1, 2 or 3 machines. Also you can sync two databases on the same MySQL server from SQLyog running on same or another machine

2) In a recursive manner it will compare the content of source and target tables by generating checksums on CHUNKS of rows. It will identify rows that are different in the two tables and rows only existing in one. INSERT, UPDATE and (optionally) DELETE statements are next executed against target. A two-way sync will repeat the process twice (reversing source and target). It is very important that you understand how the Primary Key of the table is used. Basically two rows that have same value for the PK will be considered identical rows and target will be updated with data from source. The article has more details.

3) This was partly explained in 2).

However if you INSERT rows independently on both sides a PK-conflict may occur (I am not sure if I understand if this is what you plan). Two workarounds:

a: define the PK such that the same PK value will never be created on both hosts – like ad a constant column to the PK where the constant is different on either host. This can be difficult with a standard application

b: use auto_increment_increment and auto_increment_offset settings in server configuration to ensure that odd numbers for an autoincrement PK are generated on one host and even numbers on the other. This will require that you have privileges to edit server configuration.

.. in practice it is best to avoid that the same row (as identified by the PK) on both servers is touched/UPDATED between two syncs (but there are scenarios where also this is possible if you use a: or b:). However you may for instance edit content one server and simultaneously content can be INSERTED on another server by other users.

Except for reading the article practice it to understand! You can do with two servers installed on local machine and need only a single simple structurally identically table in each database.

[Sawtooth500]

Thank you, that was very helpful.

[Author]

Posts