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 profile (typically my.cnf), in the UNIX environment in /etc/mysql/my.cnf or my.cnf under 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: Settings Example 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: Setting 1. Add a user's account as a synchronization 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. Add a user as a 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
Main slave mode: A-> B A is MASTER to modify a MySQL's My.ini file. Add the following configuration in the MySQLD configuration item:
Server-id = 1 # settings require log file, you can set the log-bin = c: / mysql / bak / mysqllog # set the log file, # where mysqllog is the name of the log file, MySQL will establish different extensions, file names Several log files for MySQLLOG. 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 Modify b Mysql's My.ini file for slave. 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 to do only Backup database updates
The restart database uses Show Slave Status to see synchronization configuration. Note: Due to the setting information of the SLAVE, MySQL generates Master.info in the database directory, so if you want 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 a configure file in a configure file Add: master-host = 10.10.10.53master-user = backupmaster-password = 1234Replicate-do-db = backupmaster-connect-retry = 10
The mysqld configuration item is added to the B's configuration file Add:
Log-bin = c: / mysql / log / mysqllogbinlog-do-db = backup
Note: When there is an error, the * .rr log file is generated. The synchronous thread exits, when correcting the error, let the synchronization mechanism work, run the slave start. To restart the AB machine, two-way heat can be achieved.
Test: Insert the B mass data table AA (1872000), and the A database can update 2500 data per second.
