MySQL3.23.15 version Set data synchronization

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.

Window environment, if there is a my.cnf file under the C: root directory, the configuration file is taken. 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.

Set 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. Add a user account with the most synchronization 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 synchronization User: 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 for master, modify a MySQL My.ini file. In the MYSQLD configuration entry, add the following configuration: server-id=1log-bin# setting requires log log can be set Log-bin=c:mysqlbakmysqllog set the directory #其中mysqllog是日志文件的名称, MySQL will create a different extension with several log files named Mysqllog. Binlog-do-db=backup #指定需要日志的数据库

To restart the database service

Use the Show Master Status command to see the log.

b for Slave, modify the b mysql my.ini file. Add the following configuration to the Mysqld configuration entry: Server-id=2master-host=10.10.10.22master-user=backup #同步用户帐号master-password=1234master-port= 3306MASTER-CONNECT-RETRY=60 Preset retry interval 60 seconds Replicate-do-db=backup tells 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 want to modify the related slave configuration, you must first delete the file. 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.53master-user=backupmaster-password=1234replicate-do-db=backupmaster-connect-retry=10

MYSQLD configuration entries in B's configuration file add the following settings: Log-bin=c:mysqllogmysqllogbinlog-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: Insert large Data meter AA (1872000) into B batch, a database can update 2,500 data per second