【实战】:sqlserver数据实时同步到mysql_MySQL
程序员文章站
2022-03-28 10:29:45
...
1.安装安装mysqlconnector 2.配置mysqlconnector ODBC数据管理器->系统DSN->添加->mysql ODBC 5.3 ANSI driver->填入data source name如jt,mysql的ip、用户名、密码即可 3.新建链接服务器 exec sp_addlinkedserver @server='jt', --ODBC里面data source name @srvproduct='mysql', --自己随便 @provider='MSDASQL', --固定这个 @datasrc=NULL, @location=NULL, @provstr='DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=192.168.5.188;DATABASE=suzhou;UID=root;PORT=3306;', @catalog = NULL exec sp_addlinkedsrvlogin @rmtsrvname='jt', @useself='false', @rmtuser='root', @rmtpassword='password'; select * from openquery(jt,'SELECT * FROM sz ; ') GO USE [master] GO EXEC master.dbo.sp_serveroption @server=N'jt', @optname=N'rpc out', @optvalue=N'TRUE' GO EXEC master.dbo.sp_serveroption @server=N'jt', @optname=N'remote proc transaction promotion', @optvalue=N'false' GO ---4.sqlserver和mysql新建库和表 create database suzhou; create table sz( id int not null identity(1,1) primary key, orderno char(20) not null, ordertime datetime not null default getdate(), remark varchar(200) ) go create table sz( id int(11) not null , orderno char(20) not null, ordertime datetime(6) not null , remark varchar(200), primary key (id) ) engine=innodb default charset=utf8; ---5.建立回环 --建立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 ----6.编写触发器和存储过程 ----6.1 insert --重写触发器 use suzhou go alter trigger tr_insert_sz on suzhou.dbo.sz for insert as declare @id int, @orderno char(20),@ordertime datetime,@remark varchar(200) select @id=id,@orderno=orderno,@ordertime=ordertime,@remark =remark from inserted; begin print @id print @orderno print @ordertime print @remark exec loopback.suzhou.dbo.sp_insert @id,@orderno,@ordertime,@remark end go --存储过程 use suzhou go create PROCEDURE sp_insert( @id int, @orderno char(20), @ordertime datetime, @remark varchar(200) ) AS BEGIN SET NOCOUNT ON; Insert openquery(jt, 'select * from sz')(id,orderno,ordertime,remark)values(@id,@orderno,@ordertime,@remark) END go ----6.2 update --重写触发器 use suzhou go create trigger tr_update_sz on suzhou.dbo.sz for update as declare @orderno char(20),@remark varchar(200) select @orderno=orderno,@remark =remark from inserted; begin exec loopback.suzhou.dbo.sp_update @orderno,@remark end go --存储过程 use suzhou go create PROCEDURE sp_update( @orderno char(20), @remark varchar(200) ) AS BEGIN SET NOCOUNT ON; update openquery(jt, 'select * from sz') set remark=@remark where orderno=@orderno END go ---update数据测试 use suzhou go update sz set remark='ocpyang' where orderno='a001' go ----6.3 delete --重写触发器 use suzhou go create trigger tr_delete_sz on suzhou.dbo.sz for delete as declare @orderno char(20) select @orderno=orderno from deleted; begin exec loopback.suzhou.dbo.sp_delete @orderno end go --存储过程 use suzhou go create PROCEDURE sp_delete( @orderno char(20) ) AS BEGIN SET NOCOUNT ON; delete openquery(jt, 'select * from sz') where orderno=@orderno END go ---delete数据测试 use suzhou go delete from sz where orderno='a001' go
上一篇: MySQL提数据高分页效率
下一篇: 究极程序员跨过的艰难六步
推荐阅读
-
SQLSERVER数据导入到MYSQL_MySQL
-
Sqlserver2000连接Oracle11G数据库进行数据实时同步
-
Oracle 数据库定时同步到 SqlServer方法
-
sqlserver数据实时同步到mysql
-
使用GoldenGate实现MySQL到Oracle的数据实时同步
-
MySQL数据库实时同步数据到Hadoop分布式文件系统的工具Applier
-
【实战】:sqlserver数据实时同步到mysql_MySQL
-
MySQL数据库实时同步数据到Hadoop分布式文件系统的工具Applier
-
【实战】:sqlserver数据实时同步到mysql_MySQL
-
Sqlserver2000连接Oracle11G数据库进行数据实时同步