MySQL provides database replication function after version 3.23.15. With this feature, you can implement two database synchronization, master slave mode, and mutual backup mode.
The settings of the database synchronous replication feature are reflected in the MySQL settings file. MySQL configuration file (generally my.cnf)
In the UNIX environment in /etc/mysql/my.cnf or My.cnf below the Home directory of MySQL users.
In the Window environment, if you have a My.cnf file in the root directory, you take this profile. When running MySQL's Winmysqladmin.exe tool, the tool names the My.cnf in the root directory 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 in my.cnf to the My.ini file, use the My.ini file as the MYSQL server configuration file.
Setting method:
Set 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: Settings
1. Add a user account with the most synchronized user:
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. Add a user account with the most synchronized user:
Grant File on *. * To backup@'10.10.10.22 'identified by' 1234 '
2. Add a database as a synchronization database:
CREATE DATABASE Backup
Main from mode: A-> B
A is Master
Modify a MySQL's My.ini file. Add the following configuration in the MySQLD configuration item:
Server-id = 1log-bin # Setup to log logs to set log-bin = c: mysqlbakmysqllog Set the directory of the log file, # where mysqllog is the name of the log file, MySQL will establish a different extension, file named mysqllog Log file. BINLOG-DO-DB = BACKUP # Specifies the database that needs a log
Ratify the database service.
Use the show master status command to see the log situation.
B is Slave
Modify the My.ini file of b mysql. Add the following configuration in the MySQLD configuration item:
Server-id = 2master-host = 10.10.10.22master-user = backup # Sync User account Master-password = 1234master-port = 3306master-connection = 60 preset retry interval 60 second replicate-do-db = backup tell Slave only makes updates for Backup databases
Restart database
Use the Show Slave Status to see synchronous configuration.
Note: Due to the setting of the SLAVE configuration information, mysql generates Master.info in the database directory.
So if you have a configuration to modify the configuration of the relevant slave, you should first remove the file. Otherwise the modified configuration cannot take effect.
Double machine hierarchy mode.
If you join the SLAVE settings in A, add the master settings in B, you can do the synchronization of B-> A.
In the configuration file in A, the mysqld configuration item adds to the following settings:
Master-host = 10.10.10.53master-user = backupmaster-password = 1234Replicate-do-db = backupmaster-connect-retry = 10 In b configuration files Add the following settings:
Log-bin = c: mysqllogmysqllogbinlog-do-db = backup
Note: When there is an error, the * .rr log file is generated. Synchronous thread exits, when correcting errors, let the synchronization mechanism work, run slave start
You can realize two-way hot standby.
test:
Insert a large data scale AA (1872000) to b batch
A database can update 2500 data per second.
