SqlServer即時資料同步到MySql，sqlserver即時資料

SqlServer即時資料同步到MySql，sqlserver即時資料
---安裝安裝mysqlconnectorhttp://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='MySqll_Aggregation', ----ODBC裡面data source name@location=NULL,--@provstr='DRIVER={MySQL ODBC 5.3 ANSI Driver};SERVER=172.17.29.33;DATABASE=bi;UID=zhaowenzhong;PORT=3306;', --和@datasrc屬性二選一@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]GOEXE Cmaster.dbo.sp_serveroption @server=N'MySqll_Aggregation',@optname=N'rpc out',@optvalue=N'TRUE'GOEXEC 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=@@SERVERNAMEgo--設定伺服器連結選項，阻止SQL Server 由於遠程序呼叫而將本地事務提升為分布事務（重點）USE [master]GOEXEC master.dbo.sp_serveroption @server=N'loopback',@optname=N'rpc out',@optvalue=N'TRUE'GOEXEC master.dbo.sp_serveroption @server=N'loopback',@optname=N'remote proc transaction promotion',@optvalue=N'false'GO---編寫觸發器和預存程序--insertCREATE TRIGGER TR_INSERT_TB ON  DB_TY2015.DBO.TBFOR INSERTASDECLARE@ID INT,                                @QTY INT                                SELECT@ID=ID,@QTY=QTY FROM INSERTED;                                BEGIN                                                EXEC loopback.db_ty2015.dbo.sp_insert@id,@qty;                                ENDCREATE PROCEDURE SP_INSERT@ID INT,@QTY INTASBEGIN                SET NOCOUNT ON                                INSERT OPENQUERY(db_ty2015,'select * from tb')(id,qty) values (@id,@qty);                SET NOCOUNT OFFEND---updateCREATE TRIGGERT R_UPDATE_TB ON DB_TY2015.DBO.TBFOR UPDATEASDECLARE @ID INT,@QTY INTSELECT@ID=ID,@QTY=QTY FROM INSERTED;BEGIN                EXEC loopback.db_ty2015.dbo.sp_update@id,@qty;ENDCREATE PROCEDURE SP_UPDATE@ID INT,@QTY INTASBEGIN                SET NOCOUNT ON                                UPDATE OPENQUERY(db_ty2015,'select * from tb') SET qty=@qty WHERE id=@id                SET NOCOUNT OFFEND--deleteCREATE TRIGGER TR_DELETE_TB ON DB_TY2015.DBO.TBFOR DELETEAS                DECLARE @ID INT                SELECT@ID=ID FROM DELETED;BEGIN                EXEC loopback.db_ty2015.dbo.sp_DELETE@id;ENDCREATE PROCEDURE SP_DELETE@ID INTASBEGIN                SET NOCOUNT ON                                DELETE OPENQUERY(db_ty2015,'select * from tb') WHERE id=@id                SET NOCOUNT OFFEND-------初始化資料     表已存在的情況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')

來源：http://blog.csdn.net/zhaowenzhong/article/details/50599372