MySQL database multi-source replication scheme

Overview

As the current production environment of the MySQL database distributed in two servers, from a single master and slave view, configuration is very simple, but need to synchronize two server database to one from the library above, need to do more configuration and considerations. There are two scenarios for multi-source replication, binlog+position and Gtid, and we are using multi-source replication for the first scenario binlog+position.

Install MySQL

When installing the mysql-5.7 version on a server from a library, multiple source replication is currently supported only for versions older than mysql5.7.

Configure MySQL

1. Configure MySQL for two master

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, configuration 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

New slave replication user in master:

Grant replication Slave,replication Client on * * to [e-mail protected] ' 192.168.2.92 ' identified by ' repl ';

Flush privileges;

Enter MySQL terminal two master to perform the following command to lock table operation

Flush tables with read lock;

Then the show master status is executed; Gets the file name and file_size of the Log_bin_file master.

The structure and data of the database to be backed up are exported by two master MySQL separately.

2. Set slave

Log in to the MySQL terminal of slave and create a new database to be backed up (example: Create db ' order_00 ' DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;)

The structure and data of the database into which the master backup is imported into each database (the length of time depends on the size of the data volume).

After slave executes the above steps, start the configuration from replication, first execute the stop slave; Then slave copy the master configuration as follows:

Change Master to master_host= ' 192.168.2.91 ', master_port=3507,master_user= ' repl ', master_password= ' repl ', Master_log _file= ' master_91.000001 ', master_log_pos=376 for CHANNEL ' master_91 ';

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

When the above configuration command completes, start the slave execution command: start slave;

Check that the slave has started successfully:

Slave_io_running:yes

Slave_sql_running:yes

The above two items are yes for configuring multi-source Master-slave replication success.

Finally, execute the command unlock tables in the Master library, and release the lock table respectively.

Test

respectively in the database of two master to check several databases for new data tables and data, the master of the new table data manually add and remove changes, and then query slave there is no synchronization, if and master data consistent, it means that the multi-source master-Slave replication success, if no synchronization is unsuccessful, Failed to start check from the first step.

Precautions

1, when encountering slave_io_running:connection, check the master and slave firewall, if the firewall is not a problem, check the change master configuration when Ip,port,user,pwd and so on.

2, encountered Slave_io_running:yes,slave_sql_running:yes is yes but the data is not synchronized when checking the my.cnf configuration file replicate-do-db parameters. You can write their comments or libraries separately.

3, must be in the Master Lock table before the data export, otherwise it may result in inconsistent master-slave data.

4, after all the steps to be sure to remove the Master lock table, or the master insert data will be error.

MySQL database multi-source replication scheme