Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to copy SQLServer2008 data to MySQL database". The content of the article is simple and clear, and it is easy to learn and understand. Please follow the editor's ideas to study and learn "how to copy SQLServer2008 data to MySQL database".

First of all, we need to configure the distribution and publishing database normally, so we won't talk too much about it here.

Then you can start creating a new release for copying to MySQL, and you can create a table for testing.

[ID] [int] IDENTITY (1) NOTNULL, [TestNAME] [varchar] (400) NULL, [CreateDate] [datetime] NULL, CONSTRAINT [PK _ MySQL_Repl_Test] PRIMARYKEYCLUSTERED ([ID] ASC) ALTERTABLEdbo. [MySQL _ Repl_Test] ADDCONSTRAINT [DF _ MySQL_Repl_Test_CreateDate] DEFAULT (getdate ()) for [CreateDate] GO

-- add transaction publication

EXECsp_addpublication@publication=N'Test',@description=N'Transactionalpublicationofdatabase',@sync_method=N'concurrent_c',@retention=0,@allow_push=N'true',@allow_pull=N'false',@allow_anonymous=N'false',@enabled_for_internet=N'false',@snapshot_in_defaultfolder=N'true',@compress_snapshot=N'false',@ftp_port=0,@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',@autogen_sync_procs=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'GOEXECsp_addpublication_snapshot@publication=N'Test',@frequency_type=1,@frequency_interval=0,@frequency_relative_interval=0,@frequency_recurrence_factor=0,@frequency_subday=0,@frequency_subday_interval=0 @ 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

-- add transaction items

EXECsp_addarticle@publication=N'Test',@article=N'MySQL_Repl_Test',@source_owner=N'dbo',@source_object=N'MySQL_Repl_Test',@type=N'logbased',@description=N'',@creation_script=N'',@pre_creation_cmd=N'drop',@schema_option=0x0000000000004071,@identityrangemanagementoption=N'none',@destination_table=N'MySQL_Repl_Test',@status=8,@vertical_partition=N'false',@ins_cmd=N'SQL',@del_cmd=N'SQL',@upd_cmd=N'SQL'GO

-- add a transaction subscription

EXECsp_addsubscription@publication=N'Test',@subscriber=N'MYSQL',@destination_db=N'tt',@subscription_type=N'Push',@sync_type=N'automatic',@article=N'all',@update_mode=N'readonly',@subscriber_type=3EXECsp_addpushsubscription_agent@publication=N'Test',@subscriber=N'MYSQL',@subscriber_db=N'tt',@job_login=NULL,@job_password=NULL,@subscriber_security_mode=0,@subscriber_login=N'root',@subscriber_password=NULL,@subscriber_provider=N'MSDASQL',@subscriber_datasrc=N'MySQLTest',@frequency_type=64 @ frequency_interval=1,@frequency_relative_interval=1,@frequency_recurrence_factor=0,@frequency_subday=4,@frequency_subday_interval=5,@active_start_time_of_day=0,@active_end_time_of_day=235959,@active_start_date=0,@active_end_date=0,@dts_package_location=N'Distributor'GO

Once created, you can observe the replication through the replication Monitor. You can also observe changes in the MySQL database from SQLServer through the LinkServer mentioned earlier.

What to pay attention to when copying the data from SQLServer2008 to MySQL

1. Create @ replicate_ddl=0 when publishing, otherwise there will be the following error:

Message 21678, level 16, status 1, procedure sp_MSrepl_addpublication, line 1110

The parameter "@ enabled_for_het_sub" can be set to "true" only if @ replicate_ddl is set to "0".

In other words, for heterogeneous databases, DDL operations cannot be replicated, which is more laborious for later maintenance.

2. The previously established DSN is the location of @ subscriber_datasrc=N'MySQLTest' in the code.

3. Some of the above parameters do not mean that they must be set in this way for reference.

4. Due to environmental limitations, during the test, the publishing library and the distribution library are under the same instance of the same server, so the DSN is only established once. I guess it should be fine to establish it at the distributor, and there will be an environment for testing later.

Thank you for your reading, the above is the content of "how to copy the data of SQLServer2008 to the MySQL database". After the study of this article, I believe you have a deeper understanding of how to copy the data of SQLServer2008 to the MySQL database. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.