MySQL database bidirectional Synchronization

The project applied for two database servers. In order to maintain data synchronization, we made a data synchronization and used this document for future use. First, we declared that the operating system on the server is Cent.

The project applied for two database servers. In order to maintain data synchronization, we made a data synchronization and used this document for future use. First, we declared that the operating system on the server is Cent.

The project has applied for two database servers. In order to maintain data synchronization, we have implemented a data synchronization, which is recorded in this document for future use:

First, declare:

The server operating systems are CentOS6.1 and the mysql version numbers are consistent. To reduce the possibility of other problems, this environment is used together.

Database Server A, IP Address: 192.168.6.108

Database Server B, IP Address: 192.168.6.200

Of course, you must first back up a database with the latest data, and then restore it to the same data after the synchronization settings are complete!

First, perform B's data synchronization function for database:

Temporarily called A as the Master and B as the Slave server Slave

Host A Master

1. Modify the mysql database conf file and/etc/my. conf

In

# Replication Master Server (default)

# Binary logging is required for replication

Add

Server-id = 1 # If it comes with it, you don't need to add it. Check the context of my. conf.

Binlog-do-db = mydb_name # Name of the database to be synchronized

2. Restart mysql

(CentOS is the restart of serice mysqld restart for other systems. If you do not know how to ask Google or duniang)

3. Create a MySQL account for synchronization

Log on to mysql as a root user

Mysql> grant replication slave, reload, super on *. * to backup@192.168.5.200 identified by 'backup ';

Mysql> flush privileges;

Ps: I created a backup user password and a mysql database user for backup, which is used for synchronization. @ Specify the ip address that the account allows remote logon to, that is, the ip address of B's slave machine

B Slave server Slave

1. Modify the conf file of the mysql database and/etc/my. conf.

In

# Replication Master Server (default)

# Binary logging is required for replication

Add

Server-id = 2 # If it comes with it, you don't need to add it. Check the context of my. conf.

Master-host = 192.168.5.108 # the ip address of the host to be backed up. Enter the ip address of host.

Master-port = 3306 # enter the default 3306 value if the database port number is not changed.

Replicate-do-db = mydb_name # Name of the database to be synchronized

Start Synchronization

On master server A, run the mysql Command (log on to mysq as the root user)

Mysql> show master status;

Eg:

+ --

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ --

|Mysql-bin.000012|106| Mydb_name | mysql |

+ --

Run the mysql Command on server B (log on to mysq as the root user)

Mysql> slave stop;

Mysql> change master to master_log_file ='Mysql-bin.000012', Master_log_pos =106;

Mysql> slave start;

Ps:

Master_log_file is the value of the File column displayed in the master server mysql show master status;

Master_log_pos is the value of the Position column displayed in the show master status under the master server mysql.

Still in the mysql command of slave server B

Use show slave status \ G; to check synchronization from the server

Mysql> show slave status \ G;

If the values of the following two items are YES, they indicate that they are already being synchronized.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Otherwise, the synchronization fails. Of course, there are N reasons for the failure. I am Google and Du Niang. Here I will only record my operation steps.

Set MySQL bidirectional Synchronization

In fact, setting two-way synchronization is to repeat the above steps, but the operations on server A and server B are reversed. server A changes from server B to master server!

1. modify my. conf of server B and add

Binlog-do-db = mydb_name # Name of the database to be synchronized

2. restart mysql (service mysqld restart) of server B)

3. log on to mysql,

Mysql> grant replication slave, reload, super on *. * to backup@192.168.5.108 identified by 'backup ';

Mysql> flush privileges;

Ps: create a backup user. The password is also the mysql database user of backup for synchronization. @ Specify the ip address that the account allows remote logon to, that is, the ip address of the slave machine.

4. modify my. cnf of server A and add

Master-host = 192.168.5.200 # the ip address of the host to be backed up. Enter the ip address of host.

Master-port = 3306 # enter the default 3306 value if the database port number is not changed.

Replicate-do-db = mydb_name # Name of the database to be synchronized

5. restart mysql (service mysqld restart) of server)

6. Run the following command on master server B MySQL:

Mysql> show master status;

Eg:

+ --

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ --

| Mydb_name | mysql |

+ --

7. Run the MySQL Command on server:

Mysql> slave stop;

Mysql> change master to master_log_file ='Mysql-bin.000014', Master_log_pos =188;

Mysql> slave start;

8. It is still in the mysql command of slave server.

Use show slave status \ G; to check synchronization from the server

Mysql> show slave status \ G;

If the values of the following two items are YES, they indicate that they are already being synchronized.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Restore the data to the latest database!

Now the bidirectional synchronization configuration is complete!

Ps: because it is based on the Operation Log backup, please back up the database before setting synchronization, and then use a backup to restore the database, then the data can be consistent!