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

Source: Internet
Author: User

[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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.