如何将事务性 MySQL 数据存入 SQL Server 数据库
我正在开发一个项目，该项目有一个 MySQL 事务数据库来备份 Web 应用程序。 该公司使用 SQL Server 作为后台和报告应用程序。 使用 MySQL 中的数据更新 SQL Server 的最佳方法是什么？ 现在，我们正在执行 MySQL 数据转储并进行完整恢复。 由于数据库大小的增加，这可能不再可行。
我更喜欢仅复制新插入和更新的行的解决方案。 我还需要 SQL Server 数据库在应用更新后保持静态。 基本上每天应该更换一次。 我可以从 MySQL 的本地副本（即非生产）更新 SQL Server 有没有办法以指定的时间间隔将 MySQL 复制应用到从属服务器？ 一个完美的解决方案是在 MySQL 上运行每日一次更新，以同步某个时间点的数据库。
I'm working on a project that has a MySQL transactional database backing up a web application. The company uses SQL Server for back office and reporting applications. What is the best way to update SQL Server with the data from MySQL? Right now, we are performing a dump of the MySQL data and doing a full restore. This may not be feasible much longer due to the increasing size of the database.
I would prefer a solution that copies only newly inserted and updated rows. I also need the SQL Server database to be static after the updates are applied. Basically, it should change once a day. I can update SQL Server from a local copy of MySQL (i.e. not production) Is there a way to apply MySQL replication to a slave server at specified intervals? A perfect solution is to run a once daily update on MySQL that syncs the database as of a point in time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
你能找到一种方法来对 mySQL DB 进行快照然后进行复制吗？ 它将创建数据库的即时逻辑副本，并及时冻结。
http://aspiringsysadmin.com/blog /2007/08/13/consistency-mysql-backups-using-zfs-snapshots/
ZFS 文件系统可以做到这一点 - 但您没有提到您的硬件/操作系统。
另外，也许您可以限制您正在拉取的数据 - 无论是时间敏感的数据，这样如果您的拉取需要 45 分钟，您的拉取将只获得早于 1 小时的数据。 或者为了让事情变得更安全——前一天拉一下怎么样？
我相信 SSIS 2008 有一个名为“维护”表的新模块，它执行获取更新/插入记录和选择性删除的常见任务。
Can you find a way to snapshot the mySQL DB and then do the copy? It would make an instant logical copy of the database which would be frozen in time.
http://aspiringsysadmin.com/blog/2007/08/13/consistent-mysql-backups-using-zfs-snapshots/
ZFS filesystem can do this - but you haven't mentioned your hardware/OS.
Also, perhaps you could restrict the data you are pulling - whatever is time sensitive so that your pull will only get data that is older than 1 hour if your pull takes 45 minutes. Or to make things a little safer - how about just pulling the day before?
I believe SSIS 2008 has a new module called 'maintain' table that does the common task of getting updated/inserted records and optionally deletes.
查看 DTS，Microsoft 的 ETL 工具。 这是相当不错的。 进行映射，将其安排为 cron 作业，Bob 就是你的叔叔。
Look into DTS, Microsoft's ETL tool. It's rather nice. Do the mapping, schedule it as a cron job, and Bob's your uncle.
无论您如何从 MySQL 克隆导入到 SqlServer，我认为您都不需要担心将 MySQL 复制限制在特定时间。
MySQL复制只需要主服务器中的一个线程，基本上只是将事务日志传输到从服务器。 如果可以的话，将主从 MySQL 服务器放在专用 LAN 网段上，以便复制流量不会影响 Web 流量。
Regardless of how you do the import to SqlServer from the MySQL clone, I don't think you need to worry about restricting MySQL replication to specific times.
MySQL replication only requires one thread in the master server and basically just transfers the transaction log to the slave. If you can, put the master and slave MySQL servers on a private LAN segment so that replication traffic does not impact the web traffic.
如果您拥有 SQL Server Standard 或更高版本，SQL Server 将满足您的所有需求。
顺便说一句 - 我正在做与您正在做的完全相同的事情。 SQL Server 太棒了——它很容易设置（我是 SSIS 的菜鸟）并且它在第一次尝试时就工作了。
if you have SQL Server Standard or higher, SQL Server will take care of all of your needs.
btw - I'm doing the exact same thing that you are doing. SQL Server is awesome - it was easy to setup (I'm a noob to SSIS) and it worked on the first shot.
听起来您需要做的是设置一个脚本来启动和停止从属数据库上的复制。 如果您可以通过脚本做到这一点，那么您可以在 SSIS 中建立一个工作流程，如下所示：
a= 开始复制到从属 MySQL 数据库
b= 将从属 MySQL 数据库副本导入 SQL Server
注意：3a 和 3b 可以并行运行。
我认为在这种情况下最好的选择是使用 SSIS 来启用和禁用 MySQL 数据库复制到从属数据库以及拍摄从属数据库的快照。 然后您可以通过 SQL Server 代理机制来驱动整个事情。
希望这可以帮助
It sounds like what you need to do is to set up a script to start and stop replication on a slave database. If you can do that via a script, then you can establish a workflow in SSIS such as follows:
a= Start Replication to Slave MySQL Database
b= Import Slave MySQL Database Replica into SQL Server
NB: 3a and 3b can run in parallel.
I think your best bet in such a scenario would be to use SSIS to enable and disable MySQL database replication to the slave as well as to take a snapshot of the slave database. Then you can drive the whole thing from the SQL Server Agent mechanism.
Hope this helps