mysql实时同步到mssql的解决方案

本文涉及的产品
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 数据库在应用程序中是必不可少的部分,mysql是开源的,所以很多人它,mssql是微软的,用在windows平台上是非常方便的,所以也有很多人用它。现在问题来了,如何将这两个数据库同步,即数据内容保持完全一致。
+关注继续查看

数据库在应用程序中是必不可少的部分,mysql是开源的,所以很多人它,mssql是微软的,用在windows平台上是非常方便的,所以也有很多人用它。现在问题来了,如何将这两个数据库同步,即数据内容保持完全一致。

MySQL Migration Toolkit是MySQL提供的开源GUI软件工具,可以针对Microsoft Access、Microsoft SQL Server、Oracle、MySQL、Sybase Server、MaxDB Database Server数据库向MySQL数据库迁移数据,这就解决了mssql同步到mysql的问题了;SQL Server迁移助手(SSMA)团队开发了针对MySQL的迁移助手Microsoft SQL Server Migration Assistant 2008 for MySQL。微软同时发布了其他三个迁移助手: SSMA for Access, SSMA for Oracle, and SSMA for Sybase (all v4.2),所以它可以解决mysql到mssql的问题,方法详见:MySQL到MsSQL的迁移工具——SSMA,实现了,结束。

……

开个玩笑,上面说的工具非常好用,可以留着备用。现在才是真正的问题:如何将这两个数据库实时同步(mysql到mssql),即数据内容实时保持完全一致。说白了,就是mysql插入(或修改、删除)一条记录,mssql也跟着同时插入(或修改、删除)该条记录,实时同步,不得有误。

问题

所谓mysql到mssql的实时同步数据。其要求是:

(1)当mysql数据库中插入一条记录,mssql数据库也同时插入对应记录;

(2)当mysql数据库中更新某些记录,mssql数据库也同时更新对应记录;

(3)当mysql数据库中删除某条记录,mssql数据库也同时删除对应记录;

这些记录变更的时间和方式都是随机的,不确定的,所以需要随时监测它的变化。同时,如下约定:

A.    在数据库同步之前,mysql和mssql数据库结构和内容完全一致;

B.    只对数据进行插入、更新和删除操作,不更改主键和结构。

分析

要实现数据库实时同步,解决方案一般有三种:一是编写程序来操作两个数据库,先将目标数据库删除,再根据原数据库进行重新生成目标数据库,但这种方法对于数据量大的数据库操作显然不可行;二是使用SQL中的触发器、存储过程和定时作业来完成实时同步;三是借助第三方的软件,如SyncNavigator,但综合经济性、稳定性和易维护性等因素,此方案慎用。

实现

(1)新建mysql临时和mssql临时数据库,数据结构与对应正式数据库相当,仅在每一个表中增加新字段OpType(用于记录数据修改的操作方式);

(2)在mysql数据库中建立触发器,作用是当mysql数据库中更改,mysql临时数据库相应表生成相应记录,并记录OpType字段(I表示插入记录,U表示修改记录,D表示删除记录);

(3)新建mysql临时数据库与mssql临时数据库之间的连接;

(4)在mssql临时数据库新建存储过程,当执行存储过程时,更新mssql临时数据库的内容,使之与mysql临时数据库内容完全一致,同时删除mysql临时数据库的内容;

(5)新建定时作业,定时调用mssql临时数据库中的存储过程;

(6)在mssql临时数据库建立触发器,根据OpType字段类型,更新mssql数据库的内容,同时删除mssql临时数据库的内容。

至此,实现了mysql数据库到mssql数据库的实时同步,其中,mysql临时数据库和mssql临时数据库只是过程数据,在同步完成后,数据表中的记录将被删除。这里所谓的实时,还取决于mssql中定时作业的计划。

Mssql连接mysql的方法

EXEC sp_addlinkedserver
@server = 'MySQL',
@srvproduct='MySql' ,
@provider = 'MSDASQL',
@provstr = 'Driver={MySQL ODBC 5.2 UNICODE Driver};
Server=localhost;
Database=mysql_temp;
User=root;
Password=****;
Option=3;'

  增加权限:

EXEC sp_addlinkedsrvlogin
@rmtsrvname='MySql' ,
@useself='false' ,
@locallogin='sa' ,
@rmtuser='root' ,
@rmtpassword='****'

  优化

由于实际数据库可能在极短时间内对一条记录进行多次频繁更新,为防止同步错误,可以两张临时表中再建立状态字段(IsUpdate)。具体操作方法:

(1)当mysql修改记录时,触发的mysql_temp表中的IsUpdate字段值标记为0;

(2)当存储过程调用时,将mysql_temp表中更新到mssql_temp的同时,将mysql_temp表中的IsUpdate字段值标记为1,并删除值为1的记录;(先更新值,再复制,最后删除,下同。)

(3)mssql_temp触发器运行时,除了将mssql_temp的记录更新到mssql外,还将删除mssql_temp表中IsUpdate字段值标记为2,并删除值为2的记录。

这样,可达到两个效果:一是更新永远是实时同步,不会有误;二是可减少存储过程的调用频率,可节省资源。

 

参考:

http://blog.csdn.net/orichisonic/article/details/46557965

http://blog.csdn.net/orichisonic/article/details/46557835

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
消息中间件 关系型数据库 MySQL
使用Flume实现MySQL与Kafka实时同步
使用Flume实现MySQL与Kafka实时同步
|
SQL 关系型数据库 数据库
|
SQL 监控 关系型数据库
RDS/MSSQL CPU持续飙高问题分析
在使用RDS/MSSQL中遇到一个持续好几个月的诡异性能问题迟迟没能得到正确解决,在一个月明风清的晚上困扰我们的疑团终于被揭开。
1875 0
|
SQL 关系型数据库 数据库
MSSQL-最佳实践-实例级别数据库上云RDS SQL Server
--- title: MSSQL-最佳实践-实例级别数据库上云RDS SQL Server author: 风移 --- # 摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种
1697 0
|
SQL 关系型数据库 数据库
MSSQL · 最佳实践 · 实例级别数据库上云RDS SQL Server
摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种解决方案以及上个月分享的RDS SDK实现数据库迁移上阿里云,本期我们分享如何将用户线下或者ECS上自建实例级别数据库一键迁移上阿里云RDS SQL Server。
1882 0
|
SQL 关系型数据库 数据库
MSSQL · 最佳实践 · RDS SDK实现数据库迁移上阿里云RDS SQL Server
--- title: MSSQL · 最佳实践 · RDS SDK实现数据库迁移上阿里云RDS SQL Server author: 风移 --- # 摘要 至今,我们完成了SQL Server备份还原专题系列七篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、以及阿里云RD
2284 0
|
SQL 关系型数据库 数据库
MSSQL · 最佳实践 · 阿里云RDS SQL自动化迁移上云的一种解决方案
摘要 至今为止我们完成了SQL Server备份还原专题系列六篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案以及如何监控备份还原进度,本期我们分享阿里云是如何基于SQL Server备份还原理论来设计RDS SQL自动化迁移上云方案的。
2084 0
|
关系型数据库 MySQL 测试技术
|
SQL 关系型数据库 MySQL
推荐文章
更多