MYSQL Database Synchronization

Recently to do database synchronization, if the Internet to find examples, success, record down, see next time.

This is an article found on the Internet.

The following configuration has been successful on this machine:

Implementation function: A main server, B is from the server, the initial state, A and B in the same data, when the data in a change, B also follow the corresponding changes, so that a and B data information synchronization, to achieve the purpose of backup.

Environment:

A, b MySQL database version of the same as 4.1.20

A:

Operating system: Windows 2003 Server

IP Address: 192.168.100.1

B:

Operating system: Windows 2003 Server

IP Address: 192.168.100.2

Configuration process:

1. Set up a backup account in the database of a, the command is as follows:

GRANT REPLICATION Slave,reload,super on *.*

To backup@ ' 192.168.100.2 '

Identified by ' 1234 ';

Set up an account backup and only allow landing from 192.168.100.2 this address, the password is 1234.

2, because the MySQL version of the new password algorithm is different, so enter the MySQL, input: Set password for ' backup ' @ ' 192.168.100.2 ' =old_password (' 1234 ');

3, shut down a server, the data in a copy to the B server, so that the data in A and B synchronization, and ensure that all settings before the end of the operation of A and B to prohibit the write operation, so that the data in both databases must be the same!

4, modify the configuration of a server, open the Mysql/my.ini file, add the following content under [Mysqld]:

Server-id=1

Log-bin=c:log-bin.log

Server-id: ID value for primary server A

Log-bin: Binary Change Day value

5, restart a server, from now on, it will be the customer heap of database modification records to the binary change log.

6, shut down B server, to B server Kam-hee configuration, so that it knows its own mirror ID, where to Shing the server and how to connect to the server. The simplest scenario is that the master, from the server, runs on separate hosts and uses the default TCP/IP port, as long as the following lines are added to the Mysql/my.ini file that is read from the server startup.

[Mysqld]

server-id=2

master-host=192.168.100.1

Master-user=backup

master-password=1234

The following items are optional

Replicate-do-db=backup

Server-id: The ID value from Server B. Note You cannot have the same ID value as the primary server.

Master-host: The IP address of the primary server.

Master-user: The account number that connects the primary server from the server.

Master-password: Connect the primary server's account password from the server.

Replicate-do-db: Tells the master server to synchronize mirrors only for the specified database.

7, reboot from Server B. All settings are complete. Update the data in a, and the update will be synchronized immediately in B. If you do not synchronize updates from the server, you can check the Mysql_error.log log files from the server for errors.

8, because the configuration of the slave, MySQL in the database data directory to generate Master.info, so if you want to modify the related slave configuration to delete the file, otherwise the modified configuration can not be effective.

If you want two simultaneous sync, as long as server-id different on the line. If the synchronization fails, you can view it with the following statement.

1.show Master STATUSG; View the host situation.

2.show slave statusg; From the machine situation.

If both IDs and files are different, you can correct them with the following statement.

Change MASTER to Master_def [, Master_def] ...

Master_def:

Master_host = ' host_name '

| Master_user = ' user_name '

| Master_password = ' PASSWORD '

| Master_port = Port_num

| Master_connect_retry = Count

| Master_log_file = ' Master_log_name '

| Master_log_pos = Master_log_pos

| Relay_log_file = ' Relay_log_name '

| Relay_log_pos = Relay_log_pos

Source: http://blog.csdn.net/liq330