Teach you step-by-step website Synchronous Mirror (reprint) _mysql

1. Introduce

Now the site with the increase in traffic, a single server can not afford a huge amount of traffic, there is no convenient way to solve this problem, the answer is "there"! For example, set up a server farm, to carry out a balanced load.
But if we want to solve the problem of exchange of visits such as telecom Netcom (the sadness of Chinese netizens ...), the solution will be incompetent!
The most convenient way to solve this problem is to set up a mirror site! By the visitors themselves to choose the right network for their fastest web site! This can solve the wiring problem, but also can solve the traffic problem!

2. Data classification of Web site synchronization

Web site data is basically divided into two categories:
A class is a file, such as html,asp,php, such as Web documents, or Rar,zip,rm,avi and other downloadable files!
To achieve their synchronization is very simple, with the FTP synchronization software can be! As for which I will do in the following details.

A class of database data files, such as Mysql,sql server and so on!
Database synchronization methods are many, the simplest way is to synchronize the database directory just OK!
In the back I will also do a detailed explanation!

3. Synchronization of Web site files

The main tool used here is FTP, Web site file synchronization is divided into two situations, one is local to remote, one is remote to remote (FXP)! The first kind of needless to say, the second remote to remote that is FXP, supporting its software is also a lot, but really suitable for multiple site synchronization mirror is not much!
Below I introduce a few I think good software! (PS: If you are familiar with the FTP command, you can make a batch of file time.)
1. First I would like to recommend the Chinese-made FTP software "The network is vivid", the function is very powerful, especially in the site of the synchronous mirror, unfortunately, this very classic software is no longer updated, the last update time is March 2003, the last version is 3.12! Although it is still very easy to use! The following is an official profile:

The network vividly absorbed all the functions of cuteftp and Updatanow, and added many functions that other software did not have: Support website mutual transmission, support website synchronization (Updata now), support on the back of the platform (multithreading upload multiple files), can open multiple sites at the same time ; multi-site plan upload function, support mirror site; Support for macro operations support planning operations; Support for file advanced comparison uploads; Support for directory hiding filtering (for friends who use Forntpage as a homepage); server automatic recognition function; Explorer browsing mode; You can customize commands Support the RFC959 standard with better stability, complete information return mechanism and error monitoring mechanism complete Chinese help.

2. The second paragraph is made by reget the same development company dedicated to the Web site synchronization software "Websynchronizer", with this software, you will experience the convenience of the Web site synchronization, easy and easy. The latest version is 1.3.62, online can find the final version of XX is 1.1 version! Here is a brief introduction:

File Synchronization Tool-Websynchronizer, by the well-known software Reget Software publishing company launched, is the Web site synchronization, file mirroring, file backup of the perfect tool, you can perform the following main work: 1 the local folder and Remote folder synchronization; 2 Synchronization of folders in two remote computers, 3 two native data synchronization.

3. Other software, such as the synchronous Shuttle (AUTOSYNCFTP), can also achieve simple Web site synchronization, but the software is very unstable and has been discontinued for 2001 years. So don't think about it! and the last time a friend mentioned Sitemirro, Because the Internet can not find the available version, so there is no way to test!

4. Network Vivid website Synchronous mirror use fool's Guide
Last in the network technology version has been sent a simple introduction of the use, if I am wordy (o (>_<) o), you can refer to the article:
Http://bbs.et8.net/bbs/sho ...

The first time you run the network vividly, it will let you choose "Full mode" and "Boot Mode", to use site synchronization must choose "Full mode."


Into the main interface: you will find that the software is very similar to the flashget, to use the site synchronization function, click on the lower left corner of the software "FTP client."


Now all you have to do is add your mirrored parent station and the Web site you want to mirror: file-> site management, add several FTP servers that you need to sync, and I'll introduce you to the settings (primarily the settings for the mirrored parent station).


The most important place for parent station setup is "compare directory (Site sync)":


1. Drag transfer
is to manually drag the delivery mode, the non-drag mode is not affected, because the parent station update is basically from the local directory, so it is recommended to select the second item "Upload only different files", mirror site can be ignored here
2. Disk Comparison settings
This feature is useful for file differences between file lengths and file dates.


You can also set up local directory filtering and server directory filtering, very flexible!
OK, after adding the parent and mirror stations, let's set the synchronization:


If you are adding a mirrored station, you will jump out of the following parent station Selection window:


Once you have set up multiple mirror sites, you can synchronize your Web site by returning to the main interface:


Precautions:
Generally, do not change the "hook" flag in front of the mirror site, because the principle of mirror site synchronization is "update the mirror site based on synchronization information from the primary site", if the scene site is not updated then the mirror site must be synchronized separately.
Note Generally, you should not change the "Maximum number of connections" and "Maximum number of threads" in the "Options window"-〉 "FTP Client"-〉 "schedule" to remain "1".
If a site error synchronization is not fully completed while synchronizing, you can only perform a single site transfer by pressing the button in the Schedule window.

To introduce the site mirroring synchronization of the necessary functions, timing synchronization, so you just do a good job of the mother station update, the other let the network to help you achieve the vivid:
Click Settings:


Enter the network Vivid Options Settings window, select the FTP client timing synchronization


Timed Download: Downloads the file within a specified period of time.
Timed upload: In a specified period of time using the site synchronization method to upload.
Start time: If you want to use the timer method, you must set this item to automatically start all sites that are set to be scheduled in the set time.
Stop time: If this is set, all sites that are set to be scheduled are automatically stopped when the system time is greater than the stop time.

Note: Timed upload type does not have "stop time" option, Web site synchronization is completed will automatically stop, at the same time the site synchronization can only run once.

There are many of the network to facilitate the synchronization of the mirror set of functions, we actually use to experience it!

5. Web Synchronizer Web site synchronous mirroring using a dummy guide
The Web Synchronizer function is very powerful, not only is the website synchronization, but also can carry on the local directory synchronization, the local to the remote synchronization, the function aspect far surpasses the vivid, however, the domestic does not seem the Chinese version, for some to the English cold friend use up will not be very comfortable, Hope CCF in the Chinese master can be his culture!

Start running the program, the foreigner's software is intelligent, as soon as you open the software there will be a synchronization wizard to jump out!


Here, as you can see, this software only supports synchronization between two directories. However, we can create multiple projects to solve this problem! I'll introduce you! Here, let's look at how to create two remote directory synchronization!
Select the second item and go to the next selection box:


Site synchronization is certainly the first choice! Go to the next step, you choose to sync the first server, suggest here Servers list, first edit a good need to sync a few FTP


Sync Mode Setting Description:
The first one means: Upload and download all the changed files
The second item means: Do not change the contents of the folder, only download new files

Click Next to proceed to the second server setting, the same way as the first one!
When the settings are complete, you can also modify the task name!
I think this is a very good place for this software to be managed in a task way. Very convenient!


OK, now we can see his main interface!


There is also a point to mention, his planning task is also very strong, here I will not say more! Used to know his strength!

A friend will say, I want to do more than two Web site synchronization how to do?
Very simple, and then build a few more tasks on it, to mirror how many sites are no problem!
This software's website synchronization mirror can be very flexible, such as can be local-remote, and then remote-remote! or local-remote 1, local-remote 2, remote 1-Remote 2, any situation synchronization can be done!

Database Synchronization Operations Guide

Finish the file synchronization, let's explore the database synchronization!
Here I mainly teach you the two most commonly used database synchronization methods!

Mysql

There are three main methods of MySQL data synchronization:
1. Use MySQL's own database synchronization function
2. Using the characteristics of MySQL database (database exists headspace directory, and stored in file form), database directory synchronization to achieve data synchronization purposes
3. Using a dedicated MySQL database synchronization software

1. Use MySQL's own database synchronization function (the following reference from the online article, wrote a very detailed)
MySQL provides database replication functionality after version 3.23.15. The function of two database synchronization, master-slave mode and mutual backup mode can be realized by using this feature.

The settings for database synchronization replication are reflected in the MySQL settings file. MySQL's configuration file (typically MY.CNF), in UNIX environments in/etc/mysql/my.cnf or in MySQL user's home directory my.cnf.

In a Windows environment, the configuration file is taken if there is a my.cnf file under the C: root directory. When running MySQL's Winmysqladmin.exe tool, the tool will name the MY.CNF in the C: root directory as Mycnf.bak. and create the My.ini in the Winnt directory. The MySQL server reads this profile when it is started. So you can copy the contents of the MY.CNF to the My.ini file and use the My.ini file as the MySQL server configuration file.

To set the method:
Set up the sample environment:
Operating system: window2000 Professional
Mysql:4.0.4-beta-max-nt-log
A ip:10.10.10.22
B ip:10.10.10.53
A: Set
1. To increase the user's most synchronized user account:
GRANT FILE on *.* to backup@ ' 10.10.10.53 ' identified by ' 1234 '
2. Add a database as a synchronization database:
Create DATABASE backup
B: Set
1. To increase the user's most synchronized user account:
GRANT FILE on *.* to backup@ ' 10.10.10.22 ' identified by ' 1234 '
2. Add a database as a synchronization database:
Create DATABASE backup
Master-slave mode: a->b
A is master
Modify a MySQL My.ini file. In the MYSQLD configuration entry, add the following configuration:
Server-id=1
Log-bin
#设置需要记录log can set the directory of the Log-bin=c:mysqlbakmysqllog settings log file,
#其中mysqllog是日志文件的名称, MySQL will build several log files with different extensions and file names Mysqllog.
Binlog-do-db=backup #指定需要日志的数据库
Restart the database service.
Use the Show Master Status command to see the log.
b for Slave
Modify the My.ini file for b MySQL. In the MYSQLD configuration entry, add the following configuration:
server-id=2
master-host=10.10.10.22
Master-user=backup #同步用户帐号
master-password=1234
master-port=3306
MASTER-CONNECT-RETRY=60 Preset retry interval 60 seconds
Replicate-do-db=backup told slave to do only updates to the backup database
Restart database
Use show slave status to see synchronization configuration.
Note: Because the slave configuration information is set, MySQL generates master.info in the database directory
So if you have a configuration to modify the associated slave, delete the file first. Otherwise, the modified configuration does not take effect.
Dual-machine Standby mode.
If you add the slave setting in a and the master setting in B, you can do b->a synchronization.
Add the following settings to the MYSQLD configuration entry in A's configuration file:
master-host=10.10.10.53
Master-user=backup
master-password=1234
Replicate-do-db=backup
master-connect-retry=10
MYSQLD configuration entries in B's configuration file to add the following settings:
Log-bin=c:mysqllogmysqllog
Binlog-do-db=backup
Note: *.err log files when errors are generated. The synchronized thread exits, and when the error is corrected, let the synchronization mechanism work and run slave start
When the AB machine is reset, bidirectional hot standby can be achieved.
Test:
Bulk INSERT into b large data scale AA (1872000)
A database can update 2,500 data per second.


2. Database directory synchronization, the same method and file synchronization, set up the need to sync the two database directory is OK!
The disadvantage is obvious, data synchronization can only be one-way, as a backup scheme

3. Sync with a dedicated MySQL sync software
This software has sqlbalance and Myreplicator, the advantage is convenient and intuitive, there are many gaining functions!
Disadvantages like 2, only single sync!
Of course you can also modify the mirror site's program to submit data to the parent database, read the data under the current mirror, however, to modify the trouble! Ordinary user modification is also very difficult! Oh, we can understand it! Give everyone a thought! A friend with ability can try!


MSSQL

MSSQL data synchronization using database replication technology to achieve Data synchronization update (from the network, but also a very perfect tutorial)
The concept of replication
Replication is the technique of copying a set of data from one data source to multiple data sources, and is an effective way to publish a single piece of data to multiple storage sites. With replication technology, users can publish a single piece of data to multiple servers so that different server users can share the data within permission. Replication technology ensures data consistency by ensuring that data is automatically synchronized and updated at different locations.
The basic elements of SQL replication include
Publishing servers, Subscribers, distributors, publications, articles
How SQL Replication works
SQL SERVER handles replication primarily with publications, subscriptions. The server on which the source data resides is the publishing server, responsible for publishing the data. The publisher copies copies of all changes to the published data to the Distributor, which contains a distribution database that receives all changes to the data, saves the changes, and distributes the changes to subscribers
SQL Server replication Technology type
SQL Server provides three replication technologies, respectively:
1. Snapshot replication (we'll use this later)
2. Transactional replication
3. Merge replication
As long as the above concepts are clear, then there is a certain understanding of replication. Next, we'll step through the process of copying.
First to configure the publishing server
(1) Select the specified server node
(2) Select the Publish, subscribe to server and distribute command from the Copy submenu of the [Tools] dropdown Menu
(3) The system pops up a dialog point [next] then looks at the prompt to complete the operation.
(4) The system will add a replication Monitor to the server's tree structure when the publishing server is set up. Also generate a distribution database (distribution)
Second Create a publication
(1) Select the specified server
(2) Select the Create and manage publications command from the Copy submenu of the Tools menu. A dialog box pops up on the system.
(3) Select the database where you want to create the publication, and then click Create Publication
(4) A dialog box pops up when you click Next in the Prompt dialog box in the Create Publication Wizard. The contents of the dialog box are three types of replication. We now choose the first one is also the default snapshot release (the other two people can go to see Help)
(5) Click Next to ask the system to specify the type of database server that can subscribe to the publication, and SQL Server allows data replication between different databases, such as Oracle or access. But here we choose to run the SQL Server 2000 database server
(6) Click on [Next] system to pop up a definition of the article dialog box is to select the table to publish
(7) then [next] until the operation is complete. When you finish creating your publication, the database that creates the publication becomes a shared database.
Third Design Subscription
(1) Select the Subscriber specified
(2) Select [Pull subscriptions] from the [Tools] Pull-down Menu [Copy] submenu
(3) Click Next until you are prompted to check the running state of the SQL Server Agent service, which is a prerequisite for the replication operation if the SQL Server Agent service must already be started.
(4) Click [Finish]. completes the subscription operation.
Complete the above steps in fact, replication is successful. But how do you know if replication has succeeded? This is the way to quickly see if success is possible. Expand the copy below the publishing server--publish content--right key publish content--attribute-------------state and then point immediately run the agent then Point Agent property 0:00:00 scheduling to occur every day, every minute, between the and 23:59:59. The next step is to determine if replication succeeded by opening C:\Program Files\Microsoft SQL Server\mssql\repldata\unc\xiaowangzi_database_ The database below to see if there are some folders with time as the file name of a folder in almost one point. If you don't believe me, open your database. Look at the subscribed server's subscription database. See the table you just posted-
A manual synchronization scheme
--Data on a scheduled synchronization server
--Example:
--Test environment, SQL Server2000, remote server name: XZ, username: SA, no password, testing database: Test
--Tables on the server (Query Analyzer is connected to create on the server)
CREATE TABLE [user] (ID int primary key,number varchar (4), name varchar (10))
Go
--The following on the local area network (native operation)
--native table, state description: null indicates new record, 1 indicates modified record, 0 indicates unchanged record
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (n ' [user] ') and OBJECTPROPERTY (ID, n ' isusertable ') = 1)
drop table [user]
Go
CREATE TABLE [user] (id int identity (1,1), number varchar (4), name varchar (), state bit)
Go
--Create a trigger to maintain the value of the State field
Create trigger t_state on [user]
After update
As
Update [user] set state=1
From [user] A join inserted B on a.id=b.id
Where a.state is not null
Go
--To facilitate synchronization, create a linked server to the server to be synchronized
--Here's the remote server name: XZ, username: SA, no password
if exists (select 1 from Master. sysservers where srvname= ' Srv_lnk ')
exec sp_dropserver ' srv_lnk ', ' droplogins '
Go
exec sp_addlinkedserver ' srv_lnk ', ', ' SQLOLEDB ', ' XZ '
exec sp_addlinkedsrvlogin ' Srv_lnk ', ' false ', NULL, ' sa '
Go
--Creating a stored procedure for the synchronization process
if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ P_synchro] and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [P_synchro]
Go
Create proc P_synchro
As
--set Xact_abort on
--Start the MSDTC service for the remote server
--exec Master. xp_cmdshell ' isql/s ' xz '/u ' sa '/P ' '/q ' exec master. xp_cmdshell ' net start msdtc ', No_output ', no_output
--Start the MSDTC service for this machine
--exec Master. xp_cmdshell ' net start msdtc ', no_output
--For distributed transactions, if the table uses the Identity column key, use the following method
--begin Distributed TRANSACTION
--data that is deleted synchronously
Delete from srv_lnk.test.dbo. [User]
where ID not in (select ID from [user])
--Synchronizing the new data
INSERT INTO srv_lnk.test.dbo. [User]
Select Id,number,name from [user] where ' is null
--Synchronizing the modified data
Update srv_lnk.test.dbo. [User] Set
Number=b.number,name=b.name
From Srv_lnk.test.dbo. [User] A
Join [user] B on a.id=b.id
where b.state=1
--Update the local flag after synchronization
Update [user] set state=0 where IsNull (state,1) =1
--commit TRAN
Go
--Create a job to perform a stored procedure for data synchronization at timed intervals
if exists (Select 1 from msdb.. sysjobs where name= ' data processing ')
EXECUTE msdb.dbo.sp_delete_job @job_name = ' data processing '
EXEC msdb.. Sp_add_job @job_name = ' data processing '
--Create job step
DECLARE @sql varchar (@dbname), varchar (250)
Select @sql = ' EXEC p_synchro '--commands for data processing
, @dbname =db_name ()--database name to perform data processing
EXEC msdb.. Sp_add_jobstep @job_name = ' data processing ',
@step_name = ' data synchronization ',
@subsystem = ' TSQL ',
@database_name = @dbname,
@command = @sql,
@retry_attempts = 5--Number of retries
@retry_interval = 5--Retry interval
--Create a schedule
EXEC msdb.. sp_add_jobschedule @job_name = ' data processing ',
@name = ' time schedule ',
@freq_type = 4,--Every day
@freq_interval = 1--Once a day
@active_start_time = 00000--0-point execution
Go