Real-time Data Synchronization from SqlServer to MySql

Real-time Data Synchronization from SqlServer to MySql
--- Install and install mysqlconnectorhttp: // www.mysql.com/products/connector//*
ODBC data Manager-> system DSN-> Add-> mysql ODBC 5.3 ANSI driver-> Fill in the data source name, such as jt, mysql ip address, user name, and password.

*/

-- Create a connection server exec sp_addlinkedserver @ server = 'mysqll _ aggregation', -- data source name @ srvproduct = 'mysql' in ODBC, -- randomly @ provider = 'msdasql ', -- fix this @ datasrc = 'mysqll _ aggregation', ---- data source name @ location = NULL in ODBC, -- @ provstr = 'driver = {MySQL ODBC 5.3 ansi driver }; SERVER = 172.17.29.33; DATABASE = bi; UID = zhaowenzhong; PORT = 3306; ', -- select either @ catalog or @ datasrc attribute.

--- Create an account and password for connecting to the mysql database exec sp_add1_srvlogin @ rmtsrvname = 'mysqll _ Aggregation ', ---- data source name @ useself = 'false' in ODBC, @ rmtuser = 'zhaowenzhong ', --- mysql account @ rmtpassword = 'mysqldba @ 100'; -- password of the mysql account

--- Test whether you can access the table select * from openquery (MySqll_Aggregation, 'select * FROM tb; ') in the mysql database ----- create a Cmaster that allows remote access to the database for USE [master] GOEXE. 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 -- create a LOOPBACK server Link (loop) EXEC sp_addmediaserver @ server = n'loopback', @ srvproduct = n'', @ provider = n'sqlncli ', @ datasrc = @ SERVERNAMEgo -- set server link options, prevent SQL Server from promoting local transactions to distributed transactions due to remote process calls (important) 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 --- compile the TRIGGER and stored procedure -- 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 each 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) --- synchronize table structure and data from mysql to SQL Server select * into [SqlServerDBName]. dbo. MYTest from openquery (localmysql, 'select * from mysqldbname. weibosession ')

Source: http://blog.csdn.net/zhaowenzhong/article/details/50599372