Bidirectional Database synchronization

Photo by imgix on Unsplash

In case of the most simple one-way sync process programs transfer distinctive data in one direction from Master (Source) to Slave (Target) database. This mode is used, for example, for offloading part of data or a whole database for further analysis and reporting.

Bidirectional synchronization is used when it’s necessary to keep all synced databases identical with the up-to-date information collected from all nodes. Data is replicated across all nodes, where all of them actively participate in data exchange. This configuration can be used to replicate the same data between several nodes to reduce server loads and for making faster access to multiple web sites from different locations.

This article shows how does Bi-directional Synchronization Pattern work in screenshots.

Bidirectional synchronization. How it works?

The following example illustrates bi-directional sync process. For the simplicity let’s assume that table structures are the same for both tables.

Initially two tables have the following data:

Table#1

Table#2

Then we change the row #6 firstly in Table#1 then in Table#2

Table#1

Table#2

In the next step, we modify row #7 in both tables. But firstly changes are made in Table#2, then in Table#1.

Table#2

Table#1

Start Bidirectional sync in the software you chose.

As a result, we’ve got identical data in both tables.

Table#1

Table#2

Both tables have the most recent data according to the latest changes sorted by time. The row # 6 data came from Table#2 and the row # 7 has the data from Table#1.

Our DBSync data synchronization tools support Bidirectional sync and allow having up-to-date data among MySQL, MSSQL, Oracle, PostgreSQL, Firebird databases. If you chose them for two-way sync, please look thorough the following notes:

Notes:

• Back up your data before running bidirectional syncing for the first time.
• Check “Bidirectional sync” option at the Customization step and software will perform all the rest automatically.
• Bidirectional synchronization uses triggers to track data changes. Therefore, ‘Trigger-based sync’ option has to be checked as well (it is checked by default).
• Requirement: Don’t change time on the Source and Destination servers manually as databases’ tables will have the most recent data according to the latest changes sorted by time. Anyway, if the server time has been changed, just clear history tables and assign triggers once again.

One-way synchronization. How is it different from Bidirectional sync?

In one-way synchronization, all changes from primary table are transferred to secondary one, but no changes are ever copied back to the primary table.

Let’s assume that originally two tables have the following data:

Table#1

Table#2

Then we change data in rows #6 and #7 in primary table:

Table#1

Now let’s carry out one-way synchronization. Note, that ‘Bidirectional sync’ option is checked off:

As a result, we have the identical data in both tables. In this case data come in one direction only from Table#1 to Table#2.

Table#1

Table#2

I hope you found this article informative and interesting and it will help you in your synchronization skills!

Originally published at https://support.dbconvert.com on May 27, 2020.