Sunday, June 28, 2009

What is the difference between MySQL server replication and SQLyog synchronization

What is the difference between MySQL server replication and SQLyog synchronization?

Technically they are completely different. MySQL server replication basically operates in the way that the server defined as the 'slave' reads the binary log of the 'master' and replicates operations performed by the master. SQLyog/SJA synchronization uses SQL to query the servers, compares the data using advanced checksum algorithms and performs the INSERTS, UPDATES and DELETES needed to bring databases in sync.

Both methods have their advantages and drawbacks. The most important aspect in favour of the method used by SQLyog synchronization is, that it is possible even if one or both of the databases are in a "shared environment" such as an ISP. You don't need any special privileges to use SQLyog synchronization - only simple SELECT, INSERT, UPDATE and DELETE privileges. And you won't need your own dedicated hardware.

When the SQLyog Job Agent (SJA) connects to the databases that should be synchronized, it does so as any client does. There is no downtime for other users/connections and all data are available for other users while the sync process is running. In that respect SQL server replication and SQLyog synchronization are similar. And as far as the end result goes: having identical databases (if that was what you specified) at both servers they are identical.

No comments: