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 name，Mysql的ip、用户名、密码即可
回到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.';
通过以上配置，就不会报错了
