快速,持续,稳定,傻瓜式
支持Mysql,Sqlserver数据同步

基于触发器的数据库同步

请联系QQ:1793040 索取软件

使用基于触发器的同步算法几乎可以实时复制数据。ABCdbline通过在更改发生时捕获并交付已修改信息的更新并在异构环境中提供连续的数据同步来实现此目的。

基于触发器的数据库同步可以将基于预先创建的触发器的一个数据库中所做的更改反映到分布式异构数据库系统中的另一个数据库中。

与传统同步过程相比,基于触发器的同步机制具有以下优势:

对大量数据集有效。如果您的数据库超过2 Gb,则不必每次都执行完全同步。仅有的最近更改将反映在同步数据库中。

更快的速度同步。同步器可以更快地复制数据库,因为它们仅由于预先创建的触发器而处理更改的记录。

不带触发器的经典同步处理标准化的100万条记录数据库(插入/更新/删除同步选项为ON),持续01小时32分钟。

使用活动触发器转移相同的100万条记录大约需要12-13分钟

根据提供的数据,您可以轻松地注意到在DBSync软件中实现的基于触发器的同步功能的显着优势。

近实时同步。基于触发器的同步系统允许您根据需要频繁运行同步会话。如果记录被更改,则可以立即(或通过Scheduler)运行同步会话,并且导入的数据将始终“胜过”要导入到的现有记录中的数据。因此,您复制的数据库将保持最新状态。

配置简单。配置基于触发器的同步非常简单,并且不需要任何开发人员级别的技术技能。

基于触发器的同步机制既可用于单向同步,也可用于双向同步。

要求:

  • 您应该可以使用创建触发器和表的权限直接访问同步数据库。
  • 基于触发器的同步仅适用于具有主键的表

下面,我们将说明插图手册中最重要的细节,以显示需要激活的确切选项。触发同步配置的过程分为2个一般阶段。如果您决定取消实时同步并为了清理同步的数据库,则需要额外的步骤。

将触发器和“ history_store”表添加到两个数据库

将为参与同步过程的每个表收集辅助信息。所有即将进行的更改将由插入,更新和删除触发器处理,并且信息存储在“ history_store”表中。请注意,触发器创建过程可能会花费额外的时间。在每个同步表中创建触发器以进行双向同步。

 

注意:要实现触发器嵌入,您需要具有允许您创建触发器和表的特权。

 

 

注意:第一次您应该只选中“复制数据”选项,但是应取消选中“插入同步”,“更新同步”和“删除同步”选项。否则,该过程将花费更长的时间。

这是触发器创建的日志:

 

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

您可以看到在处理完成之后,已将一些辅助信息添加到同步表中。

使用触发器进行数据库同步

创建的触发器开始跟踪数据库中所做的更改。您可以在想要复制数据的任何时间手动启动同步过程,也可以将调度程序配置为自动执行同步。

“ History_store”表保留数据库中的更改。

 

会话结束后,将自动清除“ History_store”表,以准备进行下一个数据库更改跟踪。

现在,您可以检查3个同步选项:插入,更新和删除,允许触发器跟踪所有更改。

同步日志应如下所示:

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

删除触发器并清理数据库

如果不再需要实时同步,则可以从数据库中删除触发器和“ History_store表”。只需取消选中“基于触发器的同步”选项并提交会话即可。所有辅助信息将从同步数据库中删除。

该日志将类似于:

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

相关推荐

咨询软件
 
QQ在线咨询
售前咨询热线
QQ1793040