SqlServer实时数据同步到MySql

迷南。 2022-08-20 03:25 290阅读 0赞

\---安装安装mysqlconnector

[http://www.mysql.com/products/connector/][http_www.mysql.com_products_connector]

/\*

配置mysqlconnector

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

\*/

![Center][]

\--新建链接服务器

exec  sp\_addlinkedserver

@server = 'MySqll\_Aggregation' , \--ODBC里面data source name

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

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

@datasrc =NULL,

@location =NULL,

@provstr = 'DRIVER=\{MySQL ODBC 5.3 ANSI Driver\};SERVER=172.17.29.33;DATABASE=bi;UID=zhaowenzhong;PORT=3306;' ,

@catalog = NULL

![Center 1][]

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

exec  sp\_addlinkedsrvlogin

@rmtsrvname = ' MySqll\_Aggregation ' , \---- ODBC里面data source name

@useself = 'false' ,

@rmtuser = 'zhaowenzhong' , \---mysql账号

@rmtpassword = 'mysqldba@2015' ; \--mysql账号其密码

![Center 2][]

![Center 3][]

![Center 4][]

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

select  \*  from  openquery ( MySqll\_Aggregation , 'SELECT \* FROM tb; ' )

\-----建立允许远程访问连接操作

USE  \[master\]

EXE C master . dbo . sp\_serveroption @server = N'MySqll\_Aggregation' , @optname = N'rpc out' , @optvalue = N'TRUE'

EXEC  master . dbo . sp\_serveroption @server = N'MySqll\_Aggregation' , @optname = N'remote proc transaction promotion' , @optvalue = N'false'

\--建立LOOPBACK 服务器链接

EXEC  sp\_addlinkedserver @server = N'loopback' , @srvproduct = N' ' , @provider = N'SQLNCLI' ,

@datasrc = @@SERVERNAME

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

USE  \[master\]

EXEC  master . dbo . sp\_serveroption @server = N'loopback' , @optname = N'rpc out' , @optvalue = N'TRUE'

EXEC  master . dbo . sp\_serveroption @server = N'loopback' , @optname = N'remote proc transaction promotion' , @optvalue = N'false'

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

\--insert

CREATE  TRIGGER  TR\_INSERT\_TB  ON   DB\_TY2015 . DBO . TB

FOR  INSERT

DECLARE @ID  INT ,

@QTY  INT

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

BEGIN

EXEC  loopback . db\_ty2015 . dbo . sp\_insert @id , @qty ;

END

CREATE  PROCEDURE  SP\_INSERT

@ID  INT ,

@QTY  INT

BEGIN

SET  NOCOUNT  ON

INSERT  OPENQUERY ( db\_ty2015 , 'select \* from tb' )( id , qty )  values  ( @id , @qty );

SET  NOCOUNT  OFF

END

\---update

CREATE  TRIGGER T R\_UPDATE\_TB  ON  DB\_TY2015 . DBO . TB

FOR  UPDATE

DECLARE  @ID  INT , @QTY  INT

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

BEGIN

EXEC  loopback . db\_ty2015 . dbo . sp\_update @id , @qty ;

END

CREATE  PROCEDURE  SP\_UPDATE

@ID  INT ,

@QTY  INT

BEGIN

SET  NOCOUNT  ON

UPDATE  OPENQUERY ( db\_ty2015 , 'select \* from tb' )  SET  qty = @qty  WHERE  id = @id

SET  NOCOUNT  OFF

END

\--delete

CREATE  TRIGGER  TR\_DELETE\_TB  ON  DB\_TY2015 . DBO . TB

FOR  DELETE

DECLARE  @ID  INT

SELECT @ID = ID  FROM  DELETED ;

BEGIN

EXEC  loopback . db\_ty2015 . dbo . sp\_DELETE @id ;

END

CREATE  PROCEDURE  SP\_DELETE

@ID  INT

BEGIN

SET  NOCOUNT  ON

DELETE  OPENQUERY ( db\_ty2015 , 'select \* from tb' )  WHERE  id = @id

SET  NOCOUNT  OFF

END

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

insert  into   penquery ( MySqll\_Aggregation , 'select \* from bi.tb' )   select \* from \[FSLogin\] . \[dbo\] . \[tb\] with ( nolock )

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

select  \*  into   \[SqlServerDBName\] . dbo . MYTest   from    openquery ( localmysql , 'select \* from mysqldbname.weibosession' )

[http_www.mysql.com_products_connector]: http://www.mysql.com/products/connector/
[Center]: /images/20220731/6f378c9444df487ab91de33611cbcc69.png
[Center 1]: /images/20220731/1ef05d05f0e54ca98c1b26275bdc0a83.png
[Center 2]: /images/20220731/02a4f22719d24c5cbb76401134a30853.png
[Center 3]: /images/20220731/2ba29e6abf9c4e9b96b4267a4c17b9b2.png
[Center 4]: /images/20220731/edf59af7f92540bcad334caf6485c409.png