"Actual combat": SQL Server data sync to MySQL in real time

1. Installation and Installation Mysqlconnector

2. Configure Mysqlconnector

ODBC Data Manager, System dsn-> add->mysql ODBC 5.3 ANSI driver-> fill in the data source name such as Jt,mysql IP, user name, password

3. Create a new linked server

EXEC sp_addlinkedserver
@server = ' JT ',--ODBC inside the data source name
@srvproduct = ' MySQL ',--you're free to
@provider = ' Msdasql ',--fixed this
@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

New libraries and tables---4.sqlserver and MySQL

Create DATABASE Suzhou;

CREATE TABLE SZ (
ID int NOT NULL identity (primary key),
OrderNo char () NOT NULL,
Ordertime datetime NOT NULL default GETDATE (),
Remark varchar (200)
)
Go

CREATE TABLE SZ (
ID Int (one) is not NULL,
OrderNo char () NOT NULL,
Ordertime datetime (6) NOT NULL,
Remark varchar (200),
Primary KEY (ID)
) Engine=innodb default Charset=utf8;

---5. Creating a loopback
--Establish loopback server link

EXEC sp_addlinkedserver @server = n ' loopback ', @srvproduct = N ', @provider = N ' sqlncli ',
@datasrc = @ @SERVERNAME
Go

--Set the server link option to prevent SQL Server from promoting local transactions to distributed transactions due to far process calls (emphasis)
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. Writing triggers and stored procedures

----6.1 Insert

--Rewrite Trigger
Use Suzhou
Go
Alter TRIGGER TR_INSERT_SZ on SUZHOU.DBO.SZ
For insert
As
declare @id int, @orderno char, @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

--Stored procedures
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

--Rewrite Trigger
Use Suzhou
Go
Create Trigger TR_UPDATE_SZ on SUZHOU.DBO.SZ
For update
As
Declare @orderno char (), @remark varchar (200)
Select @orderno =orderno, @remark =remark from inserted;
Begin
exec loopback.suzhou.dbo.sp_update @orderno, @remark
End
Go

--Stored procedures
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 [email protected] where [email protected]
END
Go

---update data test

Use Suzhou
Go
Update sz set remark= ' Ocpyang ' where orderno= ' a001 '
Go

----6.3 Delete

--Rewrite Trigger
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

--Stored procedures
Use Suzhou
Go
Create PROCEDURE Sp_delete (
@orderno Char (20)
)
As
BEGIN
SET NOCOUNT on;
Delete OpenQuery (JT, ' select * from sz ') where [email protected]
END
Go

---delete data test

Use Suzhou
Go
Delete from sz where orderno= ' a001 '
Go

"Actual combat": SQL Server data sync to MySQL in real time