Implement MySQL Database Synchronization drills

Today, we will introduce the actual operation steps for implementing MySQL Database Synchronization, as well as descriptions of the actual operations worth your attention, I checked the actual operation steps on the Internet to synchronize MySQL databases. what everyone said is as follows:

"MySQL versions later than 3.23.15 provide the database replication function. This feature enables two MySQL databases to be synchronized, master-slave mode, and mutual backup mode.

The settings of the MySQL Database Synchronization replication function are reflected in the MySQL settings file. MySQL configuration file is generally my. cnf)

In the unix environment, either in the/etc/MySQL/my. cnf or in the MySQL user's home directory my. cnf.

In the window environment, if the c: root directory contains the my. cnf file, the configuration file is used. When you run the MySQL \ bin \ winMySQLadmin.exe tool, this tool will name my. cnf in the c: root directory as mycnf. bak. Create my. ini in the winnt directory. The configuration file is read when the MySQL server is started. Therefore, you can copy the content in my. cnf to the my. ini file and use the my. ini file as the MySQL

The configuration file of the server.

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: Set

1. Add the most synchronous User Account:

 
 

  
  
GRANT FILE ON *.* TO backup@'10.10.10.53' IDENTIFIED BY ‘1234’  
 
 

2. Add a database as the synchronization database:

 
 

  
  
create database backup  
 
 

B: Set

1. Add the most synchronous User Account:

 
 

  
  
GRANT FILE ON *.* TO backup@'10.10.10.22' IDENTIFIED BY ‘1234’  
 
 

2. Add a database for MySQL Database Synchronization:

 
 

  
  
create database backup 
 
 

Master/Slave Mode: A-> B

A is the master

Modify the my. ini file of MySQL. Add the following configuration to the MySQLd configuration item:

Server-id = 1

Log-bin

# Set the log to be recorded. You can set log-bin = c: \ MySQLbak \ MySQLlog to set the directory of the log file,

# MySQLlog is the name of the log file. MySQL creates several log files with Different Extensions named MySQLlog.

Binlog-do-db = backup # specify the database for logs

Restart Database Service

Use the show master status Command to view the logs.

B is slave

Modify the my. ini file of MySQL B. Add the following configuration to the MySQLd configuration item:

Server-id = 2

Master-host = 10.10.10.22

Master-user = backup # synchronize user accounts

Masters-password = 1234

Master-port = 3306

Master-connect-retry = 60 default retry Interval 60 seconds

Replicate-do-db = backup tells slave to only update the backup database.

Restart Database

Use show slave status to check the synchronization configuration.

Note: Because slave configuration information is set, MySQL generates master.info in the database directory.

Therefore, if you want to modify the slave configuration, you must first delete the file. Otherwise, the modified configuration does not take effect.

Dual-host mutual standby mode

If you add slave settings to aand master settings to B, you can synchronize data between B and.

Add the following settings to the MySQLd configuration item in the configuration file of:

Master-host = 10.10.10.53

Master-user = backup

Masters-password = 1234

Replicate-do-db = backup

Master-connect-retry = 10

Add the following settings to the MySQLd configuration item in the configuration file of B:

Log-bin = c: \ MySQLlog

Binlog-do-db = backup

Note: *. err log file is generated when an error occurs. The synchronization thread exits. When an error is corrected, the synchronization mechanism should be executed to run slave start.

Restarting the AB machine enables two-way hot standby.

Test:

Insert large data table AA1872000 to B in batches

The AMySQL database can update 2500 data records per second during synchronization ."

However, my MySQL is 5.1.30-win32 and installed in C: \ Program Files \ MySQL Server 5.1this Folder does not have my.cnf, but my.ini. If you want to press winmysqladmin.exe to run this file, there will be a mycnf under the root directory of drive C. bak file, and generate a my. INI file.

However, after I installed and run winmysqladmin.exe, there was no mycnf. bak file in the root directory of drive C. I did not find this file on the entire computer. I don't know where I am not doing the right thing. I forget to give some advice from experts. High score after help.

Original article title: MySQL Database Synchronization

Connection: http://space.cnblogs.com/question/6586/