sql-server – 从MSSQL2005到MYSQL 5.x的单向复制

我有一个 mysql数据库充当我们的websever后端.我们有我们希望将数据推送到我们的mysql服务器的生产mssql服务器.

我已经成功地使mysql服务器成为ODBC系统数据源.

我可以从我们的mssql表到mysql表中执行插入更新语句,所以我知道我确实可以通过系统ODBC连接连接到mysql服务器.

我使用以下方法设置了本地出版物：

-- Adding the transactional publication
use [repl_test]
exec sp_addpublication @publication = N'Repl_test',@description = N'Transactional publication of database',@sync_method = N'concurrent_c',@retention = 0,@allow_push = N'true',@allow_pull = N'false',@allow_anonymous = N'true',@enabled_for_internet = N'false',@snapshot_in_defaultfolder = N'true',@compress_snapshot = N'false',@ftp_port = 21,@allow_subscription_copy = N'false',@add_to_active_directory = N'false',@repl_freq = N'continuous',@status = N'active',@independent_agent = N'true',@immediate_sync = N'true',@allow_sync_tran = N'false',@allow_queued_tran = N'false',@allow_dts = N'false',@replicate_ddl = 0,@allow_initialize_from_backup = N'false',@enabled_for_p2p = N'false',@enabled_for_het_sub = N'true',@autogen_sync_procs = 'false'
GO

我添加了快照代理：

exec sp_addpublication_snapshot @publication = N'Repl_test',@frequency_type = 4,@frequency_interval = 4,@frequency_relative_interval = 1,@frequency_recurrence_factor = 0,@frequency_subday = 4,@frequency_subday_interval = 1,@active_start_time_of_day = 0,@active_end_time_of_day = 235959,@active_start_date = 0,@active_end_date = 0,@job_login = null,@job_password = null,@publisher_security_mode = 1
GO

我已经为非mssql服务器添加了订阅：

use [repl_test]
exec sp_addsubscription @publication = N'Repl_test',@subscriber = N'MYSQL',@destination_db = N'TestTable',@subscription_type = N'Push',@sync_type = N'automatic',@article = N'all',@update_mode = N'read only',@subscriber_type = 1

我还设置了推送订阅代理：

exec sp_addpushsubscription_agent @publication = N'Repl_test',@subscriber_db = N'TestTable',@subscriber_security_mode = 0,@subscriber_login = N'root',@subscriber_password = 'PASSWORD',@subscriber_provider = N'MSDASQL',@subscriber_datasrc = N'mysqltest',@frequency_type = 64,@frequency_interval = 0,@frequency_relative_interval = 0,@frequency_subday = 0,@frequency_subday_interval = 0,@active_start_date = 20101202,@active_end_date = 99991231,@enabled_for_syncmgr = N'False',@dts_package_location = N'Distributor'
GO

一切都是顺利创建的,我确保选择了至少一篇文章(只有一个名为“TestTable”的表.

运行复制监视器时,我得到以下错误：

Error messages:
The process could not connect to Subscriber 'MYSQL'. (Source: MSSQL_REPL,Error number: MSSQL_REPL20084)
Get help: http://help/MSSQL_REPL20084
 Data source name not found and no default driver specified (Source: MSSQLServer,Error number: IM002)
Get help: http://help/IM002

我想在“@subscriber_provider”和“@subscriber_datasrc”中搞砸了.如果无法通过复制完成,我们将不得不设置某种SQL代理作业来进行更新/复制.

欢迎任何帮助.

UPDATE1
我在“TestTable”的初始复制中取得了成功.我不得不使用sp_addarticle的存储过程,以便复制不会在连接时删除mysql表.

exec sp_addarticle @publication = N'Repl_test',@article = N'TestTable',@source_owner = N'dbo',@source_object = N'TestTable',@type = N'logbased',@pre_creation_cmd = N'none',@ins_cmd = N'SQL',@del_cmd = N'SQL',@upd_cmd = N'SQL',@schema_option = 0x20025081,@status = 24
GO

复制现在抱怨我的表中有一个dupe唯一键.我想我仍然可以更多地使用添加文章存储过程.

UPDATE2
在初始复制期间接收以下错误

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"MSREPL7" set xactts = _binary'}4',timecol = {ts '2010-12-' at line 1 (Source: MSSQL_REPL,Error number: MSSQL_REPL20046)
Get help: http://help/MSSQL_REPL20046

然后后续错误是关于重复键.

解决方法

sp_addrticlecolumn中的变量@force_reinit_subscription必须为null或0.

我评论说,这是一种魅力.

这是完整的工作代码：

--step 1

-- Adding the transactional publication
use [repl_test]
exec sp_addpublication @publication = N'Repl_test',@autogen_sync_procs = 'false'
GO
--add the article to the publication
exec sp_addarticle @publication = N'Repl_test',@schema_option = 0x8000000,@status = 24
GO

--add all of the columns to the article
exec sp_articlecolumn @publication = N'Repl_test',@refresh_synctran_procs = 1
GO

--end step1

--step2
--add the publication snaphot
exec sp_addpublication_snapshot @publication = N'Repl_test',@publisher_security_mode = 1
GO
--end step2

--step3
--add the subscriber(s)
use [repl_test]
exec sp_addsubscription @publication = N'Repl_test',@subscriber = N'mysqltest',@destination_db = N'repl_test',@subscriber_type = 3
GO

--add the pushing subscription agent
exec sp_addpushsubscription_agent @publication = N'Repl_test',@subscriber_db = N'repl_test',@frequency_interval = 1,@dts_package_location = N'Distributor'
GO
--end step3

（编辑：淮北站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!