Multi-source replication solution for MySQL Databases

Multi-source replication solution for MySQL Databases

Overview

Currently, mysql databases in the production environment are distributed on two servers. If the configuration is simple from a single master-slave perspective, the databases of the two servers must be synchronized to one slave database, more configurations and precautions are required. Multi-source replication has two solutions: Binlog + Position and GTID. We use the first solution Binlog + Position for multi-source replication.

Install mysql

Only MySQL or later versions support multi-source replication.

Configure mysql

1. Configure two master mysql instances

Add or modify the following parameters in the my. cnf configuration file:

Server_id = 90

Log_bin = master_1

Log-bin-index = master_1.index

2. Configure slave

Add or modify the following parameters in the my. cnf configuration file:

Server_id = 92

Relay_log = slave

Relay_log_index = slave. index

Master-info-repository = TABLE

Relay-log-info-repository = TABLE

Replicate_ignore_db = mysql

Start configuring multi-source master-slave Replication

1. Set master

Create a Server Load balancer copy user on the master:

Grant replication slave, replication client on *. * to repl @ '192. 168.2.92 'identified by 'repl ';

Flush privileges;

Log on to the mysql terminal of the two masters and run the following command to lock the mysql table.

Flush tables with read lock;

Run show master status to obtain the file name and file_size of the log_bin_file of the master.

Export the structure and data of the databases to be backed up by the two master databases respectively.

2. Set slave

Log on to the mysql terminal of slave and CREATE the DATABASE to be backed up (for example, create database 'order _ 00' default character set utf8 COLLATE utf8_general_ci ;)

Enter the structure and data of the database to which each database is imported into the master backup (the duration depends on the size of the data volume ).

After slave completes the preceding steps, it starts to configure slave replication. First, it executes stop slave. Then, slave replicates the master configuration as follows:

Change master to master_host = '2017. 168.2.91 ', master_port = 3507, master_user = 'repl', master_password = 'repl', master_log_file = 'master _ 100', master_log_pos = 91.000001 for channel 'master _ 91 ';

Note: You need to configure two masters and then execute them separately.

After the preceding configuration command is executed, start slave to execute the command: start slave;

Check whether slave is successfully started:

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

If yes, the multi-source master-slave replication is successfully configured.

Finally, execute the unlock tables command in the master database to unlock the table.

Test

Check several databases in the two master databases to create new data tables and data, manually add, delete, modify, and query the new table data of the master, and then check whether slave has been synchronized, if the data is the same as that of the master node, the multi-source master-slave replication is successful. If the data is not synchronized, the synchronization fails. The check starts from the first step.

Notes

1. When Slave_IO_Running: connection is encountered, check the firewall of the master and slave. If there is no problem with the firewall, check the IP address, port, user, pwd, and so on when the master configuration is changed.

2. When Slave_IO_Running: Yes and Slave_ SQL _Running: Yes Are all yes, but the data is not synchronized, check the replicate-do-db parameter in the my. cnf configuration file. You can comment it or write it separately in the database shard.

3. Data export must be performed only after the master locks the table. Otherwise, the master/Slave Data may be inconsistent.

4. After completing all the steps, you must remove the master lock table. Otherwise, an error will be reported when the master inserts data.