Trigger-based synchronization

Trigger-based synchronization replicates data in near real time. DBSync installs triggers on the source and target tables; the triggers capture every INSERT / UPDATE / DELETE into a history_store table. Each subsequent sync run reads only the recorded changes, instead of comparing the entire dataset row by row.

Why use it

• Effective on large data volumes. Databases larger than ~2 GB do not need a full comparison each run - only recorded changes are replicated.
• Much faster than classic sync. A 1M-row database with Insert / Update / Drop sync takes ~90 minutes without triggers vs. ~12 minutes with triggers (DBConvert benchmarks).
• Near real-time. Run sync as often as needed - the imported data is the most recent for the given primary key.
• Simple configuration. No developer-level expertise required; tick the option and run.

Trigger-based sync is the foundation for both one-way sync and bidirectional sync.

Requirements

• Direct access to both databases with privileges to create triggers and create tables.
• Every synchronized table must have a Primary Key.

If the connecting user is not allowed to create triggers - a common restriction on locked-down or hosted databases of any engine - trigger-based sync cannot be set up. In that case use the standard, non-trigger synchronization instead: it compares the source and target row by row (Insert / Update / Drop) and needs no triggers.

1. Install triggers and history_store

First run sets up the infrastructure: a history_store table on each side, plus Insert / Update / Delete triggers on every synchronized source table. Trigger creation can take noticeable time on large schemas; for bidirectional sync triggers are created on both sides.

For the first run, tick Copy data only - leave Insert sync, Update sync, and Drop sync off. This gets data and triggers in place quickly without an expensive full comparison.

Sample log from the first run:

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

After the run, each synchronized table has an auxiliary history_store table for change tracking.

2. Use triggers for sync

From now on, the triggers track every change. Subsequent sync runs - manual or scheduled - only replicate what is recorded in history_store.

When a sync run finishes, the history_store entries are cleared and ready for the next batch of changes.

Now enable Insert sync, Update sync, and Drop sync as needed - triggers will feed them with the recorded changes.

A regular run's log is much shorter than the first run:

Process started.
Source database:-- MySQL database --
Host: 192.168.0.78
Port: 3306
User: dmsoft
Database: testSuperBase
CharSet: utf8

Destination database:-- MSSQL database --
Host: 192.168.0.78
Port: 1433
User: sa
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

3. Remove triggers and clean up

To stop trigger-based sync, untick Trigger-based sync at the customization stage and commit the session. DBSync drops the triggers and the history_store tables on both sides.

The cleanup log lists every dropped trigger:

Process started.
Source database:-- MySQL database --
Host: 192.168.0.78
Port: 3306
User: dmsoft
Database: testSuperBase
CharSet: utf8

Destination database:-- MSSQL database --
Host: 192.168.0.78
Port: 1433
User: sa
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
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 trigger 'after_update_Children' for 'testSuperBase'.'Children'
Dropping trigger 'after_insert_Children' for 'testSuperBase'.'Children'
Dropping trigger 'after_delete_Children' for 'testSuperBase'.'Children'
Dropping trigger 'after_update_Children' for 'Test_trigger_1'.'Children'
Dropping trigger 'after_insert_Children' for 'Test_trigger_1'.'Children'
Dropping trigger 'after_delete_Children' for 'Test_trigger_1'.'Children'
Dropping trigger 'after_update_Cities' for 'testSuperBase'.'Cities'
Dropping trigger 'after_insert_Cities' for 'testSuperBase'.'Cities'
Dropping trigger 'after_delete_Cities' for 'testSuperBase'.'Cities'
Dropping trigger 'after_update_Cities' for 'Test_trigger_1'.'Cities'
Dropping trigger 'after_insert_Cities' for 'Test_trigger_1'.'Cities'
Dropping trigger 'after_delete_Cities' for 'Test_trigger_1'.'Cities'
Dropping trigger 'after_update_DMSoft' for 'testSuperBase'.'DMSoft'
Dropping trigger 'after_insert_DMSoft' for 'testSuperBase'.'DMSoft'
Dropping trigger 'after_delete_DMSoft' for 'testSuperBase'.'DMSoft'
Dropping trigger 'after_update_DMSoft' for 'Test_trigger_1'.'DMSoft'
Dropping trigger 'after_insert_DMSoft' for 'Test_trigger_1'.'DMSoft'
Dropping trigger 'after_delete_DMSoft' for 'Test_trigger_1'.'DMSoft'
Dropping trigger 'after_update_Jobs' for 'testSuperBase'.'Jobs'
Dropping trigger 'after_insert_Jobs' for 'testSuperBase'.'Jobs'
Dropping trigger 'after_delete_Jobs' for 'testSuperBase'.'Jobs'
Dropping trigger 'after_update_Jobs' for 'Test_trigger_1'.'Jobs'
Dropping trigger 'after_insert_Jobs' for 'Test_trigger_1'.'Jobs'
Dropping trigger 'after_delete_Jobs' for 'Test_trigger_1'.'Jobs'
Dropping trigger 'after_update_Students' for 'testSuperBase'.'Students'
Dropping trigger 'after_insert_Students' for 'testSuperBase'.'Students'
Dropping trigger 'after_delete_Students' for 'testSuperBase'.'Students'
Dropping trigger 'after_update_Students' for 'Test_trigger_1'.'Students'
Dropping trigger 'after_insert_Students' for 'Test_trigger_1'.'Students'
Dropping trigger 'after_delete_Students' for 'Test_trigger_1'.'Students'
Closing connections.
Process completed successfully.
Total time elapsed - 00:00:09