Data synchronization between MySQL and MSSQL

MySQL

There are three main methods for Mysql Data Synchronization:
1. Use the MySQL database synchronization function
2. Use the features of the MySQL database (the database has a fixed directory and is stored as a file) To synchronize the database directory for data synchronization.
3. Use the dedicated MySQL Database Synchronization Software

1. Use the MySQL database synchronization function (refer toArticle, Which is very detailed)
MySQL versions later than 3.23.15 provide the database replication function. This feature enables two databases to be synchronized, in master-slave mode, and in mutual backup mode.

The settings of the Database Synchronization replication function are reflected in the MySQL settings file. MySQL configuration file (usually my. CNF), in the Unix environment in/etc/MySQL/My. CNF or in the MySQL user's home directory my. CNF.

In Windows, if the C: root directory contains the my. CNF file, the configuration file is used. When running the winmysqladmin.exe tool of mysql, this tool will name my. CnF in the C: root directory mycnf. Bak. Create My. ini in the WINNT directory. The configuration file is read when the MySQL server is started. Therefore, you can copy the content in my. cnf to the my. ini file and use the my. ini file as the configuration file of the MySQL server.

Setting method:
Set the sample environment:
Operating System: window2000professional
MySQL: 4.0.4-beta-max-NT-Log
Aip: 10.10.10.22
BIP: 10.10.10.53
A: Set
1. Add the most synchronous User Account:
Grantfileon *. * tobackup @ '10. 10.10.53 'identifiedby '123'
2. Add a database as the synchronization database:
Createdatabasebackup
B: Set
1. Add the most synchronous User Account:
Grantfileon *. * tobackup @ '10. 10.10.22 'identifiedby '123'
2. Add a database as the synchronization database:
Createdatabasebackup
Master/Slave Mode: A-> B
A is the master
Modify the my. ini file of amysql. Add the following configuration to the mysqld configuration item:
Server-id = 1
Log-bin
# Set the log to be recorded. You can set log-bin = C: mysqlbakmysqllog to set the log file directory,
# Mysqllog is the name of the log file. MySQL creates several log files with Different Extensions named mysqllog.
BINLOG-do-DB = backup # specify the database for logs
Restart the database service.
Use the showmasterstatus command to view logs.
B is slave
Modify the my. ini file of bmysql. Add the following configuration to the mysqld configuration item:
Server-id = 2
Master-host = 10.10.10.22
Master-user = backup # synchronize user accounts
Masters-Password = 1234
Master-Port = 3306
Master-connect-retry = 60 default Retry Interval 60 seconds
Replicate-do-DB = backup tells slave to only update the backup database.
Restart Database
Use showslavestatus to check the synchronization configuration.
Note: Because slave configuration information is set, MySQL generates master.info in the database directory.
Therefore, if you want to modify the slave configuration, you must first delete the file. Otherwise, the modified configuration does not take effect.
Dual-host mutual backup mode.
If you add slave settings to aand master settings to B, you can synchronize data between B and.
Add the following settings to the mysqld configuration item in the configuration file of:
Master-host = 10.10.10.53
Master-user = backup
Masters-Password = 1234
Replicate-do-DB = backup
Master-connect-retry = 10
Add the following settings to the mysqld configuration item in the configuration file of B:
Log-bin = C: mysqllogmysqllog
BINLOG-do-DB = backup
Note: *. Err log file is generated when an error occurs. The synchronization thread exits. When an error is corrected, the synchronization mechanism should be executed to run slavestart.
Restarting the AB machine enables two-way hot standby.
Test:
Batch insert AA (1872000) big data table to B
Database A can update 2500 data entries per second.

2. Synchronize database directories in the same way as file synchronization. Set the two database directories to be synchronized!
The disadvantage is that data synchronization can only be performed in one direction and can be used as a backup solution.

3. Use dedicated MySQL synchronization software for synchronization
The software in this area includes sqlbalance and myreplicator. The advantage is convenient and intuitive, and there are many competing functions!
The disadvantage is the same as 2. Only single synchronization is allowed!
Of course, you can also modify the image website'sProgramTo submit data to the parent database, read the data under the current image. However, it is difficult to modify the data! It is also very difficult for common users to modify! You can understand it! Give everyone a thought! If you have the ability, try it!

4. Data Synchronization for the MySQL Forum
Due to the uncontrollable (poor expression) of data sources, Forum data is real-time, and data from image forums must be considered. How can we synchronize data between image forums and parent forums?
Use the MySQL built-in Database Synchronization function described in section 1 to implement the mutual backup mode!
However, I did not test the specific application! Stability cannot be guaranteed!
If you have the ability, we recommend that you use the following methods to synchronize data. This reduces the efficiency, but reduces the number of errors!
For example, image Forum Data Synchronization:
1. The data of the parent forum and image forum is all written in the database of the parent Forum. In master-slave mode, the read data is only read locally. You need to modify the program!

2. Each time you write data, the data is submitted to two databases at the same time, which is safe, but inefficient. You have to modify the program!

MSSQL

MSSQL Data Synchronization uses database replication technology to synchronize data updates (from the network, it is also a perfect tutorial)
Concept of Replication
Replication is a technology that copies a set of data from one data source to multiple data sources. It is an effective way to publish a set of data to multiple storage sites. With the replication technology, users can publish a copy of data to multiple servers, so that different server users can share the data within the permitted range of permissions. The replication technology ensures that data distributed in different locations is automatically updated synchronously to ensure data consistency.
The basic elements of SQL replication include
Publishing Server, subscription server, distribution server, publications, articles
How SQL replication works
Sqlserver mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distribution server contains a distribution database that can receive all changes to the data and save the changes, and then distribute the changes to the subscription server.
Sqlserver replication technology type
Sqlserver provides three Replication technologies:
1. Copy a snapshot (this will be used if we stay there)
2. Transaction Replication
3. Merge and copy
As long as the above concepts are clarified, we will have a certain understanding of replication. Next we will perform the copy step.
First, configure the Publishing Server
(1) Select the specified [server] Node
(2) Select the [Publish, subscribe server, and distribute] command from the [copy] sub-menu in the [tools] drop-down menu.
(3) In the dialog box that appears, click [next] And then follow the prompts until the operation is completed.
(4) After setting the Publishing Server, the system adds a replication monitor to the tree structure of the server. A distribution database (distribution) is also generated)
Create a publication
(1) Select the specified server
(2) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. A dialog box is displayed.
(3) Select the database for which you want to create the publication, and click [Create release]
(4) In the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed. The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can be checked for help)
(5) Click [next] system requirements to specify the type of database server that can subscribe to the release. sqlserver allows data replication between different databases, such as oracle or access. But here we choose to run the "sqlserver2000" Database Server
(6) Click [next]. A dialog box for defining the document is displayed, that is, the table to be published.
(7) then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes a shared database.
Design subscription
(1) Select the specified subscription Server
(2) select [request subscription] from the [tools] drop-down menu in the [copy] submenu
(3) Click [next] until the system prompts you to check the running status of the sqlserver proxy service. The precondition for performing the copy operation is that the sqlserver proxy service must be started.
(4) Click [finish]. Complete the subscription operation.
The above steps are actually copied successfully. But how can we know whether the replication is successful? This method can be used to quickly check whether the operation is successful. Expand copy under the Publishing Server -- publish content -- Right-click Publish content -- properties -- click live -- status, and then click Run Agent immediately. Then click agent properties, click live scheduling, and set Scheduling to every day. occurred, every minute, between 0:00:00 and 23:59:59. The next step is to determine whether the replication is successful. Open C:/ProgramFiles/microsoftsqlserver/MSSQL/repldata/UNC/xiaowangzi_database_database and check whether there are some folders with time as the file name that will generate one in about one minute. If you do not believe it, open your database. Check the specified subscription database of the subscribed server to see if you have seen the table you just released-
A manual synchronization Solution
-- Regularly synchronize data on the server
-- Example:
-- Test environment: sqlserver2000, remote server name: xz, User name: SA, no password, Test Database: Test
-- Tables on the server (query analyzer is connected to a table created on the server)
Createtable [user] (idintprimarykey, numbervarchar (4), namevarchar (10 ))
Go
-- Perform the following operations on the LAN (Local Machine)
-- Local table, State Description: NULL indicates new record, 1 indicates modified record, 0 indicates no change record
Ifexists (select * fromdbo. sysobjectswhereid = object_id (n' [user] ') andobjectproperty (ID, n'isusertable') = 1)
Droptable [user]
Go
Createtable [user] (idintidentity (1, 1), numbervarchar (4), namevarchar (10), statebit)
Go
-- Create a trigger to maintain the value of the state field
Createtriggert_stateon [user]
Afterupdate
As
Update [user] setstate = 1
From [user] ajoinsertedbona. ID = B. ID
Wherea. stateisnotnull
Go
-- To facilitate synchronization, create a connection server to the server to be synchronized
-- The remote server name is xz, the user name is SA, and no password is required.
Ifexists (select1frommaster .. sysserverswheresrvname = 'srv _ lnk ')
Execsp_dropserver 'srv _ lnk ', 'droplogins'
Go
Execsp_add1_server 'srv _ lnk ', '', 'sqloledb', 'xization'
Execsp_add1_srvlogin 'srv _ lnk ', 'false', null, 'sa'
Go
-- Create a stored procedure for synchronization
Ifexists (select * fromdbo. sysobjectswhereid = object_id (n' [DBO]. [p_synchro] ') andobjectproperty (ID, n' isprocedure') = 1)
Dropprocedure [DBO]. [p_synchro]
Go
Createprocp_synchro
As
-- Setxact_aborton
-- Start the MSDTC Service of the remote server
-- Execmaster .. xp_mongoshell 'isql/s "xz"/u "sa"/P ""/Q "execmaster .. xp_mongoshell ''netstartmsdtc'', no_output "', no_output
-- Start the MSDTC Service of the Local Machine
-- Execmaster .. xp_mongoshell 'netstartmsdtc ', no_output
-- Performs distributed transaction processing. If the table uses the ID column as the primary key, use the following method:
-- Begindistributedtransaction
-- Synchronize Deleted Data
Deletefromsrv_lnk.test.dbo. [user]
Whereidnotin (selectidfrom [user])
-- Synchronize new data
Insertintosrv_lnk.test.dbo. [user]
Selectid, number, namefrom [user] wherestateisnull
-- Synchronize modified data
Updatesrv_lnk.test.dbo. [user] Set
Number = B. number, name = B. Name
Fromsrv_lnk.test.dbo. [user]
Join [user] Bona. ID = B. ID
Whereb. State = 1
-- Update the local flag after synchronization
Update [user] setstate = 0 whereisnull (State, 1) = 1
-- Committran
Go
-- Create a job and regularly execute the data synchronization Stored Procedure
Ifexists (select1frommsdb .. sysjobswherename = 'data ')
Executemsdb. DBO. sp_delete_job @ job_name = 'data'
Execmsdb .. sp_add_job @ job_name = 'data'
-- Create a job
Declare @ sqlvarchar (800), @ dbnamevarchar (250)
Select @ SQL = 'execp _ Synchro '-- Data Processing Command
, @ Dbname = db_name () -- Name of the database for processing data
Execmsdb .. sp_add_jobstep @ job_name = 'data ',
@ Step_name = 'data synchronization ',
@ Subsystem = 'tsql ',
@ Database_name = @ dbname,
@ Command = @ SQL,
@ Retry_attempts = 5, -- number of retries
@ Retry_interval = 5 -- Retry Interval
-- Create Scheduling
Execmsdb .. sp_add_jobschedule @ job_name = 'data ',
@ Name = 'schedule ',
@ Freq_type = 4, -- daily
@ Freq_interval = 1, -- run once a day
@ Active_start_time = 00000--0: Execution
Go