如何实现sql server数据库与mysql数据库实时同步

Question

sql server数据库是本地的数据库，只能查看提供视图，应该可以提供日志，同步数据量不是很大，请问有没有什么方法可以快速实现？

Answer 1

本文在https://blog.csdn.net/zhaowenzhong/article/details/50599372的基础上做了测试，改动了个别有误及没有注释的地方

在同步的前提下，环境一定要搭好，测试的时候应为mysql安装的一些bug导致失败了很多次，又重装过

---安装安装mysqlconnector

http://www.mysql.com/products/connector/ 现在的版本已经是8.0了

-----配置mysqlconnector

ODBC数据管理器->系统DSN->添加->mysql ODBC 5.3 ANSI driver->填入data source name如jt,mysql的ip、用户名、密码即可

--新建链接服务器

exec sp_addlinkedserver

@server='MySqll_Aggregation',--Connector ODBC里面填的data source name

@srvproduct='MySql',--自己随便

@provider='MSDASQL',--固定这个

@datasrc='MySqll_Aggregation', ----ODBC里面data source name

@location=NULL,

--@provstr='DRIVER={MySQL ODBC 8.0 ANSI Driver};SERVER=127.0.0.1;DATABASE=mysql;UID=root;PORT=3306;', //对应connector ODBC 配置的内容 此项配置与@datasrc二者只需有一个配置即可

@catalog=NULL

执行完语句

---创建连接mysql数据库的账号及密码

exec sp_addlinkedsrvlogin

@rmtsrvname='MySqll_Aggregation',----ODBC里面data source name

@useself='false',

@rmtuser='root',---mysql账号

@rmtpassword='root';--mysql账号密码

执行语句

刷新对象资源管理器中的链接服务器，能看到的Connector ODBC里面填的data source name内容，目录下对应的是mysql里的数据库

在本地SQLSERVER数据库里建一张表（TB），在本地mysql数据库里建张表（tb），同步需要的，表结构一样（测试时的字段为id，qty）

---测试是否可以访问mysql数据库中的表

select * from openquery(MySqll_Aggregation,'SELECT * FROM tb; ')

//tb 是本地数据库mysql里面的测试表

成功的结果如下：(tb表内的状态)

执行下面的语句

--建立LOOPBACK 服务器链接

EXEC sp_addlinkedserver@server=N'loopback',@srvproduct=N' ',@provider=N'SQLNCLI',

@datasrc=@@SERVERNAME

go

--设置服务器链接选项，阻止SQL Server 由于远过程调用而将本地事务提升为分布事务（重点）

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

---编写触发器和存储过程

--insert

新建单独查询窗口，否则会报错导致失败

CREATE PROCEDURE SP_INSERT

@ID INT,

@QTY INT

AS

BEGIN

            SET NOCOUNT ON

                            INSERT OPENQUERY(MySqll_Aggregation, 'select * from tb')(id,qty) values (@id,@qty);

            SET NOCOUNT OFF

END

创建另一查询窗口

db_ty2015是SQL SERVER里同步的表TB的所在数据库

CREATE TRIGGER TR_INSERT_TB ON DB_TY2015.DBO.TB

FOR INSERT

AS

DECLARE @ID INT,

                            @QTY INT

                            SELECT @ID=ID, @QTY=QTY FROM INSERTED;

                            BEGIN

                                            EXEC loopback.db_ty2015.dbo.sp_insert  @id, @qty;

                            END

---update

单独查询窗口

CREATE PROCEDURE SP_UPDATE

@ID INT,

@QTY INT

AS

BEGIN

            SET NOCOUNT ON

                            UPDATE OPENQUERY(MySqll_Aggregation,'select * from tb') SET 

qty = @qty WHERE id=@id

            SET NOCOUNT OFF

END

CREATE TRIGGER TR_UPDATE_TB ON DB_TY2015.DBO.TB

FOR UPDATE

AS

DECLARE @ID INT, @QTY INT

SELECT @ID=ID, @QTY=QTY FROM INSERTED;

BEGIN

            EXEC  loopback.db_ty2015.dbo.sp_update  @id,  @qty;

END

--delete

CREATE PROCEDURE SP_DELETE

@ID INT

AS

BEGIN

            SET NOCOUNT ON

                            DELETE OPENQUERY(MySqll_Aggregation,'select * from tb') WHERE id=@id

            SET NOCOUNT OFF

END

CREATE TRIGGER TR_DELETE_TB ON DB_TY2015.DBO.TB

FOR DELETE

AS

            DECLARE @ID INT

            SELECT@ID=ID FROM DELETED;

BEGIN

            EXEC loopback.db_ty2015.dbo.sp_DELETE@id;

END

执行完增删改之后都能在mysql的表里看到结果

---从mysql同步表结构及数据到sqlserver上

测试语句

select * into [DB_TY2015].[dbo].[tb3] from openquery([MySqll_Aggregation],'select * from mysql.tb')

tb3是创建的一张空表，执行完后表结构变成了tb的，表数据也复制过来了

-------初始化数据 表已存在的情况

INSERT OPENQUERY([MySqll_Aggregation],'select * from tb')(id,qty) select*from [DB_TY2015].[dbo].[tb3]with(nolock)

文章转载自（https://blog.csdn.net/qq_42170268/article/details/84879045）