SQLSERVER2012通过ODBC实时同步数据到Mysql5.7

安装环境:

Windows-SQLSERVER 2012

Aliyun-RDS(MySQL-5.7.28)

1. 安装安装mysqlconnector

http://www.mysql.com/products/connector/




下载5.3.13这个版本


下载安装略:

 

2. 配置mysqlconnector

打开控制面板:

ODBC数据管理器->系统DSN->添加->mysqlODBC 5.3 ANSI driver->填入data source nameMysqlip、用户名、密码即可

 

回到SQLSERVER MSSM中:

--新建链接服务器
exec sp_addlinkedserver

@server='MySql_Aggregation' ,  --ODBC里面datasource name

@srvproduct='MySql' ,

@provider='MSDASQL' ,   --固定这个

@datasrc=NULL,

@location=NULL,

@provstr='DRIVER={MySQL ODBC 5.3 ANSIDriver};SERVER=127.0.0.1;DATABASE=test;UID=root;PORT=3306;',

@catalog = NULL

---创建连接mysql数据库的账号及密码
exec sp_addlinkedsrvlogin

@rmtsrvname='MySql_Aggregation',

@useself='false',

@rmtuser='root',

@rmtpassword='rscpass123.';

--通过连接测试查看test库中的z_user_favorite表中的数据:
select * from openquery(MySql_Aggregation,'SELECT * FROM z_user_favorite; ')

-----建立允许远程访问连接操作
USE [master]

GO

EXEC master .dbo. sp_serveroption @server=N'MySql_Aggregation' , @optname= N'rpc out', @optvalue=N'TRUE'

GO

EXEC master .dbo. sp_serveroption @server=N'MySql_Aggregation' , @optname= N'remote proc transaction promotion',@optvalue =N'false'

GO

--建立LOOPBACK 服务器链接
EXEC sp_addlinkedserver @server = N'loopback' , @srvproduct = N' ' , @provider= N'SQLNCLI',

@datasrc = @@SERVERNAME

Go

--设置服务器链接选项,阻止SQL Server 由于远过程调用而将本地事务提升为分布事务(重点)
USE [master]

GO

EXEC master .dbo. sp_serveroption @server=N'loopback', @optname= N'rpc out', @optvalue=N'TRUE'

GO

EXEC master .dbo. sp_serveroption @server=N'loopback', @optname= N'remote proc transaction promotion' ,@optvalue=N'false'

GO


---编写触发器和存储过程

---前题条件:mysql中已有数据,跟SQLSERVER中的表结构一样
---测试的数据库test, 测试表:z_user_favorite_insert
---验证从SQLSERVER对表z_user_favorite_insert进行DML后,查看数据是否同步更改到MYSQL对应的表中
---z_user_favorite_insert表结构如下:
CREATETABLE`z_user_favorite`(

    `id`INT(10)NOTNULLAUTO_INCREMENTCOMMENT'自增ID',

    `store_id`INT(11)NOTNULLDEFAULT'0'COMMENT'店铺',

    `goods_id`INT(11)NOTNULLDEFAULT'0'COMMENT'商品ID',

    `user_id`INT(11)NOTNULLDEFAULT'0'COMMENT'账户ID',

    `create_time`INT(11)NOTNULLDEFAULT'0'COMMENT'创建时间',

    `type`TINYINT(11)NOTNULLDEFAULT'0'COMMENT'默认收藏',

    `visitor_id`INT(10)UNSIGNEDNOTNULLDEFAULT'0'COMMENT'游客ID',

    PRIMARYKEY(`id`)USINGBTREE,

    INDEX`store_id`(`store_id`)USINGBTREE,

    INDEX`goods_id`(`goods_id`)USINGBTREE,

    INDEX`user_id`(`user_id`)USINGBTREE,

    INDEX`idx_visitor_id`(`visitor_id`)USINGBTREE,

    INDEX`idx_create_time`(`create_time`)USINGBTREE,

    INDEX`idx_user_id_store_id`(`user_id`,`store_id`)USINGBTREE

)

COMMENT='账户收藏'

COLLATE='utf8mb4_unicode_ci'

ENGINE=InnoDB

AUTO_INCREMENT=2616947

;

 

--插入数据:

CREATE PROCEDURE sp_z_user_favorite_insert

@id INT,

@store_id INT,

@goods_id INT,

@user_id INT,

@create_time INT,

@type INT,

@visitor_id INT

AS

BEGIN

                 SET NOCOUNT ON

                                 INSERTOPENQUERY (MySql_Aggregation, 'select * from z_user_favorite')(id,store_id,goods_id,user_id,create_time,type,visitor_id) values(@id,@store_id,@goods_id,@user_id,@create_time,@type,@visitor_id);

                 SET NOCOUNT OFF

END

 

GO

 

CREATE TRIGGER tr_insert_z_user_favorite ONtest.dbo.z_user_favorite

FOR INSERT

AS

DECLARE

@id INT,

@store_id INT,

@goods_id INT,

@user_id INT,

@create_time INT,

@type INT,

@visitor_id INT

 SELECT @id =id, @store_id=store_id,@goods_id=goods_id,@user_id=user_id,@create_time=create_time,@type=type,@visitor_id=visitor_idFROM INSERTED;

                                 BEGIN

                                                EXEC loopback.test.dbo.sp_z_user_favorite_insert @id ,@store_id,@goods_id,@user_id,@create_time,@type,@visitor_id;

                                 END

                                                                    

--更新

CREATE TRIGGER tr_update_z_user_favorite ONtest.dbo.z_user_favorite

FOR UPDATE

AS

DECLARE

@id INT,

@store_id INT,

@goods_id INT,

@user_id INT,

@create_time INT,

@type INT,

@visitor_id INT

 SELECT @id =id,@store_id=store_id,@goods_id=goods_id,@user_id=user_id,@create_time=create_time,@type=type,@visitor_id=visitor_idFROM INSERTED;

BEGIN

                 EXECloopback.test.dbo.sp_update_z_user_favorite @id,@store_id,@goods_id,@user_id,@create_time,@type,@visitor_id;

END

GO

CREATE PROCEDURE sp_update_z_user_favorite

@id INT,

@store_id INT,

@goods_id INT,

@user_id INT,

@create_time INT,

@type INT,

@visitor_id INT

AS

BEGIN

                 SET NOCOUNT ON

                                 UPDATEOPENQUERY (MySql_Aggregation, 'select * from z_user_favorite') set id =@id,store_id=@store_id,goods_id=@goods_id,user_id=@user_id,create_time=@create_time,type=@type,visitor_id=@visitor_idwhere id =@id

                SET NOCOUNT OFF

END

 

--删除

CREATE TRIGGER tr_delete_z_user_favorite ONtest.dbo.z_user_favorite

FOR DELETE

AS

DECLARE

@id INT

 SELECT @id =id FROM DELETED ;

BEGIN

                 EXECloopback.test.dbo.sp_delete_z_user_favorite @id;

END

GO

CREATE PROCEDURE sp_delete_z_user_favorite

@id INT

AS

BEGIN

                 SET NOCOUNT ON

                                 DELETEOPENQUERY (MySql_Aggregation, 'select * from z_user_favorite') where @id =id

                 SET NOCOUNT OFF

END


错误解决方法:

运行以下查询:
declare @erp_detail_id int;
declare @stock int;
declare @transfer_qty int;
select @erp_detail_id=s.detailID,@stock=s.stock,@transfer_qty=s.TransferOutQty from EgStock_StockSpace s (nolock) where s.detailID=167033
UPDATE OPENQUERY (MySql_Aggregation, 'select * from nsy_scm.stock') set stock =@stock, transfer_qty=@transfer_qty,update_by='lvjianda' where erp_detail_id=@erp_detail_id
当重复运行以上SQL语句时,报错如下:
OLE DB provider "MSDASQL" for linked server "MySql_Aggregation" returned message "无法为更新定位行。一些值可能已在最后一次读取后已更改。".
Msg 7343, Level 16, State 4, Line 6
The OLE DB provider "MSDASQL" for linked server "MySql_Aggregation" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.


解决方法:

1.进入ODBC配置栏, 将Cursors/Results中有Return matched rows instead of affected rows这项前面打勾


2.将旧的LINKSERVER删除,增加新的LINKSERVER,在drivers中带上参数option=2

--新建链接服务器
exec sp_addlinkedserver
@server='MySql_Aggregation' ,  --ODBC里面data source name
@srvproduct='MySql' ,
@provider='MSDASQL' ,   --固定这个
@datasrc=NULL,
@location=NULL,
@provstr='DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=127.0.0.1;DATABASE=test;UID=root;PORT=3306;option=2;',
@catalog = NULL

---创建连接mysql数据库的账号及密码
exec sp_addlinkedsrvlogin
@rmtsrvname='MySql_Aggregation',
@useself='false',
@rmtuser='root',
@rmtpassword='rscpass123.';

通过以上配置,就不会报错了

分割线
感谢打赏
江西数库信息技术有限公司
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS