[Practice]: Synchronize SQL server data to mysql and SQL Server mysql in real time

[Practice]: Synchronize SQL server data to mysql and SQL Server mysql in real time

1. Install and install mysqlconne

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 address, user name, and password.

3. Create a linked server

Exec sp_addmediaserver
@ Server = 'jt ', -- data source name in ODBC
@ Srvproduct = 'mysql', -- whatever you want
@ Provider = 'msdasql ', -- fix 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_add1_srvlogin
@ 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. create databases and tables for sqlserver and 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. Create a loop
-- Create a LOOPBACK server Link

EXEC sp_addmediaserver @ 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 Remote Procedure Calls (Focus)
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 prodemo', @ optvalue = n'false'
GO

---- 6. Compile the trigger and stored procedure

---- 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 (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

 

 

-- Stored Procedure
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 (20), @ remark varchar (200)
Select @ orderno = orderno, @ remark = remark from inserted;
Begin
Exec loopback. suzhou. dbo. sp_update @ orderno, @ remark
End
Go

 

 

-- Stored Procedure
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 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 Procedure
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 data test

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

 

 

How to synchronize data from SQL 2005 to MYSQL in real time

There are syntax differences between mysql and sqlserver, and direct SQL import is not feasible. However, you can use the bcp command of sqlserver to export the data in the table to a txt file in the specified format. For example, use commas to separate columns, lines are separated by \ n \ r.
Mysql can use the load data infile command to import such data to the library.

Because sqlserver is unfamiliar, you need to study the bcp command by yourself,
For the load data Syntax of mysql, refer:
Load data infile '/tmp/result. text' into table test_table fields terminated by', 'Lines terminated by '\ n ';
It means that fields are separated by commas, and rows are separated by \ n. The data file is result. text.

Data in the mysql database is synchronized with data in the SQL Server

I will provide you with two ideas:
1. First, you must have a business layer, a data access interface layer, and a data layer (the last two layers can be merged). When there is data operation in the business layer, you must operate on the data of the other two layers at the same time, the transaction is committed only when both tasks are successful. This ensures data consistency.
2. Design and implement a data synchronization tool. I have implemented a Data Synchronization tool between SQL Server and Oracle, using a defined table (primary key, field, table name, synchronization identifier, etc) this function is used to synchronize tables in two databases to the same content as DataSet. You can specify the synchronization cycle as required.