如何做好跨数据库的信息同步？ [英] How to do cross-database information syncing?

问题描述

我设计一个目录，在多个数据源的数据将覆盖在其他来源的数据改变或更新时。有些数据库是MySQL和SQL Server和一些信息将AD / LDAP。

I am designing a directory where data in multiple sources will have to override the data in other sources when altered or updated. Some of the databases are MySQL, SQL Server and some of the info will be AD/LDAP.

我的问题是这样的：有一个设计模式，这种类型的数据库的传播，减少交通堵塞和prevent错误？另外这个项目将在PHP中，因此，如果有人知道类似的开放源代码项目，我可以从相适应，这将是很好过的。有可能将不得不一些数据库之间的一些逻辑。

My question is this: is there a design pattern for this type of database propagation, to reduce traffic and prevent errors? Also this project will be in PHP, so if anyone knows of a similar open source project I could adapt from, that would be nice too. There will probably have to be some logic between some of the databases.

推荐答案

您将需要一些方法来标志被同步记录。我们使用的系统那样的，其中每个表以同步具有保持syncstate一列。当一条记录被修改，它修改它的状态太（在触发器）和同步工具查询修改记录每隔几分钟。

You'll need some way to flag the records to be synced. We use a system like that, in which each table to sync has a column that keeps the syncstate. When a record is modified, it modifies its state too (in a trigger) and a synchronization tool queries for modified records every few minutes.

缺点是，你将需要大量的code正确地处理这个问题，特别是因为你不能直接删除记录。同步工具首先需要了解，需要执行实际删除。除此之外，这是很难建立一个良好的队列这种方式，因此，如果记录同步之前他们的父母，你会得到一个错误。而每一个必须同步表需要一个额外的列。

Disadvantage is that you will need lots of code to handle this correctly, especially because you cannot delete records directly. The sync tool first needs to know and needs to perform the actual delete. Besides that, it is hard to build a good queue this way, so if records are synced before their parents are, you'll get an error. And every table that must be synced needs an extra column.

所以现在即将实施的新的解决方案。此解决方案使用单独的表的队列。队列包含指向其他表中的记录（主键值，并引用表名/字段名）。此队列是目前唯一表以监测变化，因此，所有的表需要做的就是实现一个触发器，当修改队列标志着修改的记录。因为它是在一个单独的表中的单个队列中，这增加了我前面提到的问题的解决方案：

So now there is a new solution about to be implemented. This solution uses a separate table for the queue. The queue contains pointers to records in other tables (primary key value and a reference to table name/field name). This queue is now the only table to monitor changes, so all a table need to do is implement a single trigger that marks the modified records as modified in the queue. Because it is a single queue in a separate table, this adds solutions for the problems I mentioned earlier:

• 记录可以被立即删除。同步工具找到一个id在排队，验证它不再存在，所以将其删除从其他数据库太
• 儿童父母的依赖关系自动解决。一个新的母公司将在其子前的队列中，已删除的父母将在那里落后的儿童。唯一的问题，你可能会发现在交联的记录，尽管延期提交可能是一个解决方案。
• 在不需要的所有表中额外的列。只有一个队列，一些辅助表，包含每个表的一个函数调用一个触发器来进行同步。

不幸的是，我们还没有完全实现这个解决方案，所以我不能告诉你，如果它将ectually更好地工作，虽然测试肯定认为如此。

Unfortunately we've not fully implemented this solution, so I can't tell you if it will ectually work better, though the tests definately suggest so.

记住，这个系统做了一个在记录一个副本。我认为这是最好的approch了。复制数据，然后（之后）过程中，它在目标服务器上。我不认为这是对数据进行处理，而复制是个好主意。如果出现任何错误，你将有一个工作的调试和地狱恢复/重新计算数据。

Mind that this system does a one on one copy of records. I think that is the best approch too. Copy the data, and then (afterwards) process it on the target server. I don't think it is a good idea to process the data while copying it. If anything goes wrong, you'll have a hell of a job debugging and restoring/recalculating data.

这篇关于如何做好跨数据库的信息同步？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！