Detailed steps in the mysql synchronous replication setup method guide

1. Take advantage of MySQL's own database synchronization (see the article below for more details) MySQL has provided database replication since version 3.23.15. Use this function to achieve two databases synchronization, master and slave mode, backup mode function.

The Settings of the database synchronous replication function are reflected in the Settings file of mysql. mysql configuration file (typically my. cnf), under the environment of unix in/etc mysql/my cnf or in home mysql users directory my. cnf.

In an windows environment, if there is an my.cnf file in the c: root directory, take the configuration file. When running mysql's winmysqladmin.exe tool,   will name my.cnf in   c: root as mycnf.bak. And create my.ini in the winnt directory. The configuration file is read when the mysql server starts. So you can copy the contents of   my.cnf into the my.ini file and use the my.ini file as the configuration file for the mysql server.

Setting method:
Set the sample environment:
(every day the network prohibited characters) system: window2000professional
mysql: 4.0.4 - beta - max - nt - log
Aip:10.10.10.22
Bip:10.10.10.53

A: Settings 1. Add the most synchronized user account:
GRANTFILEON *. * TObackup @ '10.10.10.53 IDENTIFIEDBY' 1234 '
2. Add a database as a synchronous database:
createdatabasebackup

B: Settings 1. Add the most synchronized user account:
GRANTFILEON *. * TObackup @ '10.10.10.22 IDENTIFIEDBY' 1234 '
2. Add a database as a synchronous database:
createdatabasebackup
Master-slave mode: A-> B
A for master
Modify the my.ini file for Amysql. Add the following configuration to the mysqld configuration item:
server-id=1
log-bin
log can be set to log-bin =c:mysqlbakmysqllog to set the directory of log files,
Where mysqllog is the name of the log file, mysql will create several log files with different extensions and the file name mysqllog.
Es114en-do-db =backup# specifies the database
that requires logging Restart the database service.
See the log using the showmasterstatus command.
B is slave     start   slave
Modify my.ini file for Bmysql. Add the following configuration to the mysqld configuration item:
server-id=2
master-host=10.10.10.22
Es139en-user =backup# sync user account
master-password=1234
master-port=3306
Es147en-connect-retry =60 preset retry interval of 60 seconds
Es151en-do-db =backup tells slave to only do backup database updates
Restart database
See the synchronization configuration with showslavestatus.
Note: because the configuration information for slave is set, mysql generates master.info
in the database directory Therefore, if you want to modify the configuration of slave, you should delete this file first. Otherwise the modified configuration does not take effect.
Dual standby mode.
If you add slave Settings to A and master Settings to B, you can do B-> A synchronization.
Add the following Settings to the mysqld configuration entry in the A configuration file:
master-host=10.10.10.53
master-user=backup
master-password=1234
replicate-do-db=backup
master-connect-retry=10
Add the following Settings to the mysqld configuration entry in the B configuration file:
log-bin=c:mysqllogmysqllog
binlog-do-db=backup
Note: when an error occurs *.err log file. The synchronized thread exits. To get the synchronization mechanism working when the error is corrected, run slavestart
Rebooting the AB machine allows for two-way hot standby.
Test:
Batch insert large data scale AA (1872000)
into B The A database can update 2,500 pieces of data per second.


2. Database directory synchronization, method and file synchronization, set up the need to synchronize the two database directory can!
The downside is obvious, data synchronization can only be done in one direction, and can be used as a backup solution

3. Synchronize
with special MySQL synchronization software This aspect of the software has SQLBalance and MyReplicator, the advantage is convenient and intuitive, there are many competitive functions!
Disadvantage and 2, can only single synchronization!
Of course, you can also modify the mirror site procedures to submit data to the mother database, read the data under the current mirror, however, to modify the trouble! Ordinary users are also very difficult to modify! Ha ha, everybody understands can! Give us an idea! The friend that has ability can try!

4. Data synchronization on MySQL forum
Due to the uncontrollable data source (difficult to express), the forum data is real-time, and also consider the data from the mirror forum, how to achieve the mirror forum and the parent forum data synchronization?
With the introduction of MySQL database synchronization function backup mode can be achieved!
However, I did not test the specific application! Stability is not guaranteed!
The friend that has ability recommends to use the following kind of train of thought to synchronize, reduce efficiency relatively speaking, but can reduce the error that produces!
For example, mirror forum data synchronization:
1. The data of the mother forum and the mirror forum are all written in the mother forum database, master and slave mode, read only in the local read, this needs to modify the program!

2. Every time data is written, it is submitted to two databases at the same time. It is safe, but very inefficient, and the program
has to be modified