Some General Database Syncing Questions

[Sawtooth500]

I'm new to SQLYog – is there documentation somewhere that provides an overview of how database syncing exactly works?

Specifically, can I do the following:

1. I'm going to be working with a Joomla database that's on a linux server; I will have to make a copy of the database for dev purposes which will be on a different server too. Meanwhile there are about 15 articles a day added to the actual database. Now since SQLYog isn't available for linux, can I run SQLYog at home on a Windows machine and then have it sync between both databases even though they are both remote databases?

2. How exactly does that data sync work? How does SQLYog deal with syncing overlapping data in the two databases? For example, lets say we had a table that had an auto-increment field. Obviously, the data put in this table in the actual environment would be different than that put in the dev environment. So how is that kind of syncing handled?

3. Is SQLYog dependable enough in synching that when I'm done with the dev database I can just replace the real one with the dev one and all the modifications I made in dev will be there along with all the new stuff put into the real one?

Thank you for your help!

[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