SqlServer实时数据同步到MySql
发布时间：2020-12-12 13:08:15 所属栏目：MsSql教程 来源：网络整理
导读：---安装安装mysqlconnector http://www.mysql.com/products/connector/ /* 配置mysqlconnector ODBC数据管理器-系统DSN-添加-mysql ODBC 5.3 ANSI driver-填入data source name如jt,mysql的ip、用户名、密码即可 */ --新建链接服务器 exec? sp_addlinkedserv
|
---安装安装mysqlconnector
http://www.mysql.com/products/connector/
/*
配置mysqlconnector
ODBC数据管理器->系统DSN->添加->mysql ODBC 5.3 ANSI driver->填入data source name如jt,mysql的ip、用户名、密码即可 */
--新建链接服务器 exec? sp_addlinkedserver @server = 'MySqll_Aggregation' , --ODBC里面data source name @srvproduct = 'MySql' , --自己随便 @provider = 'MSDASQL' , --固定这个 @datasrc =NULL, @location =NULL, @provstr = 'DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=172.17.29.33;DATABASE=bi;UID=zhaowenzhong;PORT=3306;' , @catalog = NULL
---创建连接mysql数据库的账号及密码 exec? sp_addlinkedsrvlogin @rmtsrvname = ' MySqll_Aggregation ' , ---- ODBC里面data source name @useself = 'false' , @rmtuser = 'zhaowenzhong' , ---mysql账号 @rmtpassword = 'mysqldba@2015' ; --mysql账号其密码
---测试是否可以访问mysql数据库中的表 select? *? from? openquery ( MySqll_Aggregation , 'SELECT * FROM tb; ' ) -----建立允许远程访问连接操作 USE? [master] GO EXE C master . dbo . sp_serveroption @server = N'MySqll_Aggregation' , @optname = N'rpc out' , @optvalue = N'TRUE' GO EXEC? master . dbo . sp_serveroption @server = N'MySqll_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 ---编写触发器和存储过程 --insert CREATE? TRIGGER? TR_INSERT_TB? ON ? DB_TY2015 . DBO . TB FOR? INSERT AS DECLARE @ID? INT , ???????????????????????????????? @QTY? INT ???????????????????????????????? SELECT @ID = ID , @QTY = QTY? FROM? INSERTED ; ???????????????????????????????? BEGIN ???????????????????????????????????????????????? EXEC? loopback . db_ty2015 . dbo . sp_insert @id , @qty ; ???????????????????????????????? END CREATE? PROCEDURE? SP_INSERT @ID? INT , @QTY? INT AS BEGIN ???????????????? SET? NOCOUNT? ON ???????????????????????????????? INSERT? OPENQUERY ( db_ty2015 , 'select * from tb' )( id , qty )? values? ( @id , @qty ); ???????????????? SET? NOCOUNT? OFF END ---update CREATE? TRIGGER T R_UPDATE_TB? ON? DB_TY2015 . DBO . TB FOR? UPDATE AS DECLARE? @ID? INT , @QTY? INT SELECT @ID = ID , @QTY = QTY? FROM? INSERTED ; BEGIN ???????????????? EXEC? loopback . db_ty2015 . dbo . sp_update @id , @qty ; END CREATE? PROCEDURE? SP_UPDATE @ID? INT , @QTY? INT AS BEGIN ???????????????? SET? NOCOUNT? ON ???????????????????????????????? UPDATE? OPENQUERY ( db_ty2015 , 'select * from tb' )? SET? qty = @qty? WHERE? id = @id ???????????????? SET? NOCOUNT? OFF END --delete CREATE? TRIGGER? TR_DELETE_TB? ON? DB_TY2015 . DBO . TB FOR? DELETE AS ???????????????? DECLARE? @ID? INT ???????????????? SELECT @ID = ID? FROM? DELETED ; BEGIN ???????????????? EXEC? loopback . db_ty2015 . dbo . sp_DELETE @id ; END CREATE? PROCEDURE? SP_DELETE @ID? INT AS BEGIN ???????????????? SET? NOCOUNT? ON ???????????????????????????????? DELETE? OPENQUERY ( db_ty2015 , 'select * from tb' )? WHERE? id = @id ???????????????? SET? NOCOUNT? OFF END -------初始化数据? ? ? 表已存在的情况 insert ?into ? penquery ( MySqll_Aggregation , 'select * from bi.tb' ) ? select * from [FSLogin] . [dbo] . [tb] with ( nolock ) ---从mysql同步表结构及数据到sqlserver上 select? *? into ? [SqlServerDBName] . dbo . MYTest ? from ?? openquery ( localmysql , 'select * from mysqldbname.weibosession' ) ? （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
