从MS Sql 2005和MySql复制数据的建议 [英] Suggestions for replication of data from MS Sql 2005 and MySql

问题描述

我的公司当前在Sql Server 2005上运行一个事务数据库.我们将添加一个MySql(在Linux上运行)报告数据库.我们需要使复制从MS-Sql数据库运行到MySql数据库.它不必是实时的，但应该在几分钟之内.

My company currently has a transactional db running on Sql Server 2005. We are going to add a MySql (running on linux) reporting db. We'll need to get replication running from the MS-Sql db to the MySql db. It doesn't have to be real time but should be within a few minutes.

我有相当不错的MSSql Dev技能和一般的dba技能，但是没有MySql背景.我们团队中的MySql人员没有MSSql经验.

I've got pretty good MSSql Dev skills and so-so dba skills but no MySql background. The MySql guy on our team has no MSSql experience.

我想知道是否有人设置了类似的东西并且可能有一些建议.我已经看到了在两者之间迁移数据的一些事情，但是对于正在进行的复制却没有太多的事情.现在，我最好的猜测是在SSIS中进行设置，并在Sql Agent下运行它.我现在将致力于SSIS的构想，但欢迎提出任何建议.

I was wondering if anybody has setup anything similar and might have some suggestions. I've seen some things on migrating data between the two but not much for on-going replication. Right now my best guess is to set something up in SSIS and run it under the Sql Agent. I'm going to work on the SSIS idea for now but welcome any suggestions.

推荐答案

我的朋友在几乎相同的情况下(他从MSSQL的几个表中复制了一些数据到MySQL)构建了这样的东西:

Friend of mine for almost the same case (he copies some data from just a few tables from MSSQL to MySQL) built something like that:

• 为将要复制的每个表添加了触发器.在特殊表(更少或更多)中触发保存主键，操作类型(i)nsert/(u)pdate/(d)elete和源表名.
• 小型.NET应用程序每隔几分钟就会扫描此特殊表以查找新密钥，并从源MSSQL表读取数据并将其保存在MySQL的目标表中(更少或更多).

这可以正常工作，因为:

This works fine because:

• 表变化不大.
• 他只复制了几列.

优点:

• 快速&易于实施和改变.

缺点:

• 自制工具并不完美:).

这篇关于从MS Sql 2005和MySql复制数据的建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！