MYSQL Database Synchronization

I want to perform Database Synchronization recently. If I find an example online, I will record it and check it again later. this is an online article. the following configuration has been successfully implemented on the local machine: A is the master server, B is the slave server, and the data information in the initial state is the same in A and B. When the data in A changes, B Also changes accordingly, making the numbers of A and B

I want to perform Database Synchronization recently. If I find an example online, I will record it and check it again later. this is an online article. the following configuration has been successfully implemented on the local machine: A is the master server, B is the slave server, and the data information in the initial state is the same in A and B. When the data in A changes, B Also changes accordingly, making the numbers of A and B

I want to perform Database Synchronization recently. If I find an example online, I will record it and check it again later.

This is an online article.

The following configurations have been successfully configured on the local machine:

Function: A is the master server and B is the slave server. In the initial state, the data information in A and B is the same. When the data in A changes, B also changes accordingly, synchronize the data information of A and B to achieve the purpose of backup.

Environment:

MySQL database versions A and B are 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. Create A backup account in database A. The command is as follows:

Grant replication slave, RELOAD, super on *.*

TO backup @ '192. 168.100.2'

Identified by '20140901 ';

Create an account backup and only allow login from 192.168.100.2. The password is 1234.

2. Because the new password algorithm of mysql version is different, enter: set password for 'backup '@ '192. 168.100.2' = old_password ('123') in mysql ');

3. Shut down server A, copy the data in server A to server B, synchronize the data in server A and server B, and ensure that before all the settings are completed, do not perform write operations on server A and server B so that the data in the two databases must be the same!

4. modify the configuration of server A, open the mysql/my. ini file, and add the following content under [mysqld:

Server-id = 1

Log-bin = c: log-bin.log

Server-id: ID value of master server

Log-bin: Binary Change Daily Value

5. Restart server A. From now on, it will record the changes to the database related to the customer heap into the binary change log.

6. Shut down server B and configure Server B's Jinxi so that it can know its image ID, where to find the master server, and how to connect to the server. The simplest case is that the master and slave servers run on different hosts respectively and use the default TCP/IP ports as long as the mysql/my. add the following commands to the INI file.

[Mysqld]

Server-id = 2

Master-host = 192.168.100.1

Master-user = backup

Masters-password = 1234

// Optional

Replicate-do-db = backup

Server-id: ID of slave server B. Note that it cannot be the same as the ID of the master server.

Master-host: the IP address of the master server.

Master-user: The account connecting the slave server to the master server.

Master-password: the account and password used to connect the slave server to the master server.

Replicate-do-db: tells the master server to only synchronize images for the specified database.

7. Restart slave server B. Now all settings are complete. Update the data in A, and B will also perform synchronization updates immediately. If the slave server is not synchronously updated, you can view the mysql_error.log file on the slave server to troubleshoot the error.

8. Because the slave configuration information is set, mysql generates master.info in the database data directory. Therefore, to modify the slave configuration, delete the file first, otherwise, the modified configuration does not take effect.

If you want to synchronize the two instances at the same time, as long as the server-id is different, if the synchronization fails, you can use the following statement to view it.

1. show master statusG; view the host information.

2. show slave statusG; slave status.

If the two IDs and files are different, use the following statement to correct them.

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: