Shulou(Shulou.com)06/02 Report--

This article mainly explains "MYSQL multi-source replication, filter replication and application scenario introduction". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "MYSQL multi-source replication, filter replication and application scenario introduction".

The use of Mysql will be accompanied by a problem rarely mentioned in other databases, data fusion. ORACLE, SQL SERVER, PG you can go to partition tables, MYSQL encountered such problems mostly go to sub-database sub-table to solve, although now there is a TIDB for MYSQL subsequent data fusion, but if the amount of data is not large, or some MYSQL 8 new support syntax requirements, etc., multi-source replication is still a good choice.

The disadvantage is also obvious that multi-source replication will not solve the replication conflicts that may occur in your replication due to your carelessness. For example, the database with the same name, the conflict of deployment system data. Let's take a look at how to do multi-source replication, and some of these "pits".

First of all, the MYSQL of most units has basically enabled GTID, here the following replication, all use the GTID way to connect.

The machines tested are

Master

192.168.192.200

192.168.192.202

From

192.168.192.201

First of all, back up the databases on 200 and 202. When backing up, only the databases that need to be synchronized are backed up, but not all of them are backed up. It is also necessary to do this in multi-source replication, otherwise it is good to do the first replication. Later replication is not easy to do, there will be some problems.

The backup is made through mydumper, while the latest 0.95 of mydumper is maintained by TIDB's team and should be reliable in mysql version 5.7 or below (please correct if there is any error)

On the 200 machine.

On the machine of 202.

Restore the related database on the machine of 201

Restore the database

Similarly, restore the second database to the winner database

The restored database 201 looks like this.

Let's set up a copy with two MYSQL masters

CHANGE MASTER TO MASTER_HOST='192.168.198.200', MASTER_USER='admin', MASTER_PORT=3306, MASTER_PASSWORD='XXXX', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'C200'

CHANGE MASTER TO MASTER_HOST='192.168.198.202', MASTER_USER='admin', MASTER_PORT=3306, MASTER_PASSWORD='XXXX', MASTER_AUTO_POSITION = 1 FOR CHANNEL 'C202'

Through the above two statements, 201 has established a replication relationship with 200201 machines.

Start replication start slave; to view replication status on 201

We can see that the replication for the two hosts is OK. But the pit is not far away.

1 for example, in order to manage mysql conveniently, we set up a management library monitor on each MYSQL

So the immediate problem is that there is a problem on the data aggregation database 201.1. I set up the monitor database on 200,202machines successively. Logically, the replication of 202should stop, but due to special settings, the replication did not stop. But the error log of 201 will record this error mercilessly.

The following error log reports that the 202 host's command to create the database failed

In fact, this is the result of being configured to ignore errors in the DDL statement in replication, but if we continue to operate on some non-DDL operations, replication will not continue to work.

We create a table, test1 sub-table on 200,202machines, and then insert data, 2011replication reported an error. The reason is that the primary key is repeated.

What I want to illustrate through this example

1 if multi-source replication, it is recommended that the error of DDL is more tolerant on multi-source replication machines.

2 it is not recommended to have a database such as every database and have the same table and primary key settings in this database

What if there is a similar problem? there must be the same named database on each MYSQL's physical server, and the library can not be replicated.

Then sacrifice our replication filtering to solve the problem, the principle is that we filter the passed logs, and those that are not in the database list that we allow to replicate are not replicated.

Let's stop the replication on 2011and then delete the monitor database on stop slave;

Execute the statement in the following figure on 201

Start replication, and then we operate on the tables in the monitor database on the original 200,202

Table on 202

The watch on 200

On the other hand, the monitor database can be deleted, and replication will filter all data that is not outside the employees winner database.

In the figure below, there is no monitor database

And the replication on 201 is working properly.

In fact, multi-source replication of MYSQL is a relatively good function, and it is also a function used for data re-fusion and simple data joint query after some sub-database operations. of course, there are many pits, so if you don't pay attention to it, there will be all kinds of replication problems.

Thank you for your reading. the above is the content of "MYSQL multi-source replication, filtering replication and application scenario introduction". After the study of this article, I believe you have a deeper understanding of MYSQL multi-source replication, filtering replication and application scenario introduction, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.