Trigger-based database synchronization

Trigger-based synchronization records every change as it happens, so each run transfers only what actually changed instead of comparing the two databases row by row. It is one of the four approaches compared in our overview of database synchronization.

DBSync installs INSERT, UPDATE and DELETE triggers on the tables you synchronize and writes each change into a tracking table called history_store. The next run reads that table instead of the whole dataset, which is what makes repeated runs on a large database fast.

This is change capture by triggers, not by the database log. If you need log-based CDC - reading the MySQL binlog or the PostgreSQL WAL, with nothing written into your source - that is a different product: DBConvert Streams.

What it needs before you start

Rights to create triggers and a table on both sides. DBSync creates the history_store tracking table and three triggers per synchronized table - in your source database as well as your target. That surprises people who assume the source is only ever read from. If the connecting account cannot do it, the run stops with a message like Error 1142: CREATE command denied to user 'someone'@'host' for table 'history_store', or the equivalent for your engine.

A primary key on every synchronized table. Trigger-based sync matches a source row to exactly one target row by key. Tables without one have to be handled another way.

If your DBA will not grant those rights - a common answer on locked-down and hosted databases - trigger-based sync cannot be set up, and standard comparison sync is the fallback: it needs only an ordinary login that reads the source and writes the target, at the cost of comparing both sides on every run.

Benefits of the trigger-based synchronization:

1. Effective for large amounts of data. You don't have to do a full sync every time. The only recent changes will be reflected in the synchronized databases.
2. Faster synchronization. Synchronizers with "trigger synchronization" enabled replicate databases much faster because they only process modified records thanks to pre-created triggers.
3. Run sync jobs more often. Since only a subset of the databases needs to be synchronized, you can keep the replicated databases up to date.
4. Simple to configure, once you have the rights. The setup is two checkboxes - but creating triggers and a table in your source database is a permission your DBA has to grant. See What it needs before you start above.

Trigger-based database synchronization is available for both one-way sync and bi-directional synchronization.

How does it work?

1. Remote Triggers are created to capture DMLs (Insert, Update, Delete) in each table.
2. When the "insert, update or delete a record" event occurs, the corresponded trigger is fired. This record goes to the previously generated shadow tables "history_store."
3. Once the DBSync process starts the next time, it reads the records from this "history_store" in the source and destination and synchronizes these records in both the origin and destination DB.

Triggers or the database log?

Both capture changes; they differ in what they need from you.

Trigger-based works on every database pair DBSync supports, including engines that expose no usable log at all. The cost is that it writes into your source: a tracking table and three triggers per synchronized table. It needs rights to create them, and where FoxPro or DBF is one side of the pair, synchronization runs one way only.

Log-based never touches your schema - it reads changes the server already records. The cost is administrative: binary logging or logical replication has to be enabled on the server, a replication user has to exist, and on a hosted database that is often a request that gets refused.

So the question is not which is better in the abstract. It is which one your database owner will actually let you have. Triggers are available on every modern engine and need no server-level configuration, which is why DBSync uses them; log-based CDC for MySQL and PostgreSQL is DBConvert Streams.

Configure triggered database synchronization.

Below we will highlight the essential details about Trigger-based synchronization. This guide shows the options that need to be activated.

The process of setting up "trigger database sync" is divided into TWO steps. If you decide to cancel trigger-based synchronization later, an additional extra step is required.

The process of configuring "Triggered Database Synchronization" is divided into TWO steps. If you decide to cancel the trigger-based sync later, an additional step is required.

Adding Triggers and "history_store" table to both databases

By default, the "trigger sync" option is checked. The synchronization engine collects information about all upcoming changes for each table that participated in the synchronization process in the history_store table. Insert, Update, and Delete triggers are used to track related changes. Please note that the process of creating triggers may take additional time.

Triggers are created for each synchronized table for bidirectional synchronization.

Note: The first time, you should have checked only the "Copy Data" option, but "Insert sync," "Update sync," and "Drop sync" options should be unchecked. Otherwise, the process will take longer.

Here is the trigger creation log:

Process started.
Source database:-- MySQL database --
Host: 192.168.0.78
Port: 3306
User: dmsoft
Password: Yes
Database: testSuperBase
CharSet: utf8
Use SSH: No
Use SSL: No

Destination database:-- MSSQL database --
Host: 192.168.0.78
Port: 1433
User: sa
Password: Yes
Database: Test_trigger_1
CharSet: utf8

Connecting to the source database.
Connecting to the destination database.
Creating history store for testSuperBase
Creating history store for Test_trigger_1
Table 'Children' exists. Skipping creation.
Table 'Cities' exists. Skipping creation.
Table 'DMSoft' exists. Skipping creation.
Table 'Jobs' exists. Skipping creation.
Table 'Students' exists. Skipping creation.
Synchronizing data in table 'Children'.
Synchronizing data in table 'Cities'.
Synchronizing data in table 'DMSoft'.
Synchronizing data in table 'Jobs'.
Synchronizing data in table 'Students'.
Creating trigger 'after_update_Children' for 'Test_trigger_1.Children'
Creating trigger 'after_insert_Children' for 'Test_trigger_1.Children'
Creating trigger 'after_delete_Children' for 'Test_trigger_1.Children'
Creating trigger 'after_update_Cities' for 'Test_trigger_1.Cities'
Creating trigger 'after_insert_Cities' for 'Test_trigger_1.Cities'
Creating trigger 'after_delete_Cities' for 'Test_trigger_1.Cities'
Creating trigger 'after_update_DMSoft' for 'Test_trigger_1.DMSoft'
Creating trigger 'after_insert_DMSoft' for 'Test_trigger_1.DMSoft'
Creating trigger 'after_delete_DMSoft' for 'Test_trigger_1.DMSoft'
Creating trigger 'after_update_Jobs' for 'Test_trigger_1.Jobs'
Creating trigger 'after_insert_Jobs' for 'Test_trigger_1.Jobs'
Creating trigger 'after_delete_Jobs' for 'Test_trigger_1.Jobs'
Creating trigger 'after_update_Students' for 'Test_trigger_1.Students'
Creating trigger 'after_insert_Students' for 'Test_trigger_1.Students'
Creating trigger 'after_delete_Students' for 'Test_trigger_1.Students'
Closing connections.
Process completed successfully.
Total time elapsed - 00:00:03

In the two pictures below, you can see that some new triggers have been added to the synchronized tables after the process is complete.

Use Triggers for database sync.

At this point, the newly created triggers start tracking changes made to the source and target databases. You can manually run a database synchronization job at any time or configure the scheduler to synchronize databases automatically.

"History_store" table stores changes in your databases.

When the session ends, the "History_store" table will be automatically cleared to prepare for the subsequent database change tracking.

You can now choose from three possible synchronization options:

• "Insert,"
• "Update,"
• "Drop,"

allowing triggers to track all changes.

Check out the article on types of database synchronization for details.

The sync log looks like this:

Process started.
Source database:-- MySQL database --
Host: 192.168.0.78
Port: 3306
User: dmsoft
Password: Yes
Database: testSuperBase
CharSet: utf8
Use SSH: No
Use SSL: No

Destination database:-- MSSQL database --
Host: 192.168.0.78
Port: 1433
User: sa
Password: Yes
Database: Test_trigger_1
CharSet: utf8

Connecting to the source database.
Connecting to the destination database.
Table 'Children' exists. Skipping creation.
Table 'Cities' exists. Skipping creation.
Table 'DMSoft' exists. Skipping creation.
Table 'Jobs' exists. Skipping creation.
Table 'Students' exists. Skipping creation.
Synchronizing data in table 'Children'.
Synchronizing data in table 'Cities'.
Synchronizing data in table 'DMSoft'.
Synchronizing data in table 'Jobs'.
Synchronizing data in table 'Students'.
Closing connections.
Process completed successfully.
Total time elapsed - 00:00:02

Remove Triggers and clean databases.

If you no longer plan to synchronize databases, you can remove the triggers and the "History_store table" from the databases. Just uncheck the "Trigger-based sync" checkbox and commit the session. All service information (shadow tables) will be removed from your synchronized databases.

The log will look like this:

Process started.
Source database:-- MySQL database --
Host: 192.168.0.78
Port: 3306
User: dmsoft
Password: Yes
Database: testSuperBase
CharSet: utf8
Use SSH: No
Use SSL: No

Destination database:-- MSSQL database --
Host: 192.168.0.78
Port: 1433
User: sa
Password: Yes
Database: Test_trigger_1
CharSet: utf8

Connecting to the source database.
Connecting to the destination database.
Removing history store for testSuperBase
Removing history store for Test_trigger_1
Table 'Children' exists. Skipping creation.
Table 'Cities' exists. Skipping creation.
Table 'DMSoft' exists. Skipping creation.
Table 'Jobs' exists. Skipping creation.
Table 'Students' exists. Skipping creation.
Synchronizing data in table 'Children'.
Synchronizing data in table 'Cities'.
Synchronizing data in table 'DMSoft'.
Synchronizing data in table 'Jobs'.
Synchronizing data in table 'Students'.
Synchronizing data in table 'Children'.
Synchronizing data in table 'Cities'.
Synchronizing data in table 'DMSoft'.
Synchronizing data in table 'Jobs'.
Synchronizing data in table 'Students'.
Dropping triger 'after_update_Children' for 'testSuperBase'.'Children'
Dropping triger 'after_insert_Children' for 'testSuperBase'.'Children'
Dropping triger 'after_delete_Children' for 'testSuperBase'.'Children'
Dropping triger 'after_update_Children' for 'Test_trigger_1'.'Children'
Dropping triger 'after_insert_Children' for 'Test_trigger_1'.'Children'
Dropping triger 'after_delete_Children' for 'Test_trigger_1'.'Children'
Dropping triger 'after_update_Cities' for 'testSuperBase'.'Cities'
Dropping triger 'after_insert_Cities' for 'testSuperBase'.'Cities'
Dropping triger 'after_delete_Cities' for 'testSuperBase'.'Cities'
Dropping triger 'after_update_Cities' for 'Test_trigger_1'.'Cities'
Dropping triger 'after_insert_Cities' for 'Test_trigger_1'.'Cities'
Dropping triger 'after_delete_Cities' for 'Test_trigger_1'.'Cities'
Dropping triger 'after_update_DMSoft' for 'testSuperBase'.'DMSoft'
Dropping triger 'after_insert_DMSoft' for 'testSuperBase'.'DMSoft'
Dropping triger 'after_delete_DMSoft' for 'testSuperBase'.'DMSoft'
Dropping triger 'after_update_DMSoft' for 'Test_trigger_1'.'DMSoft'
Dropping triger 'after_insert_DMSoft' for 'Test_trigger_1'.'DMSoft'
Dropping triger 'after_delete_DMSoft' for 'Test_trigger_1'.'DMSoft'
Dropping triger 'after_update_Jobs' for 'testSuperBase'.'Jobs'
Dropping triger 'after_insert_Jobs' for 'testSuperBase'.'Jobs'
Dropping triger 'after_delete_Jobs' for 'testSuperBase'.'Jobs'
Dropping triger 'after_update_Jobs' for 'Test_trigger_1'.'Jobs'
Dropping triger 'after_insert_Jobs' for 'Test_trigger_1'.'Jobs'
Dropping triger 'after_delete_Jobs' for 'Test_trigger_1'.'Jobs'
Dropping triger 'after_update_Students' for 'testSuperBase'.'Students'
Dropping triger 'after_insert_Students' for 'testSuperBase'.'Students'
Dropping triger 'after_delete_Students' for 'testSuperBase'.'Students'
Dropping triger 'after_update_Students' for 'Test_trigger_1'.'Students'
Dropping triger 'after_insert_Students' for 'Test_trigger_1'.'Students'
Dropping triger 'after_delete_Students' for 'Test_trigger_1'.'Students'
Closing connections.
Process completed successfully.
Total time elapsed - 00:00:09