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: 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.53A: Setting 1. Add a 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 BACKUPB: Setting 1. Add a user's most synchronized user account: Grant File ON * To backup@ '11.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 = 1log-bin # Setup to log-bin = c: mysqlbakmysqllog Set the directory of the log file, # where mysqllog is the name of the log file, MySQL will establish different extensions Name, file name is a few 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.22master-user = backup # Synchronize user account master-password = 1234master-port = 3306master-connection = 60 Preset retry interval 60 Second replicate-do-db = backup tells slave only updates for Backup databases
The restart database uses Show Slave Status to see synchronization configuration.
Note: Due to the setting of the SLAVE configuration information, MySQL generates Master.info in the database directory so that the file is to be modified to modify the relevant SLAVE. 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 Add the following settings: master-host = 10.10.10.53master-user = backupmaster-password = 1234Replicate-do-db = backupmaster-connection = 10 In b configuration file mysqld configuration item 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 B Batch Big Data Sheet AA (1872000) Stage A Database You can update 2500 data per second.
