CentOS 7 MySQL 5.7 master slave only synchronizes partial libraries or tables

There are two ways to synchronize partial data

• The master only sends the required data;
  • Advantages: the relay log is small; if there are multiple slave databases, it only needs to be controlled in the master database
  • Disadvantages: it’s troublesome to modify midway, and you can’t control the synchronized table
• Slave only receives what you want
  • Advantages: it is convenient to modify the synchronized table or library in the middle of the process; it can control the required table and library
  • Disadvantages: the relay log is large; if there are many slave libraries, you need to configure one by one;

Master side

Binlog do DB binary log database (multiple databases are separated by commas)

Ignore databases in binlog ignore DB binary log (multiple databases are separated by commas)

Example 1:

Binlog do DB = Zn, Zn1, Zn2 ᦇ the databases that need to be synchronized are not synchronized if they are not included. (if you don't add this line, you will synchronize all)

 

binlog-do-db=zn

binlog-do-db=zn1

binlog-do-db=zn2

To configure synchronized tables:

Some version operations failed, and the MySQL service could not be started

Binlog do table binary log data

Example 2

Binlog ignore DB = MySQL # this means that binlog is not recorded and all slave libraries are synchronized to ensure their respective permissions

binlog-ignore-db = performance_schema

binlog-ignore-db = information_schema

Slave side

Replicate do DB set the databases to be replicated (multiple databases are separated by commas)

Replicate ignore DB sets the replication databases to be ignored (multiple databases are separated by commas)

Replicate do table sets the table to be replicated

Replicate ignore table sets the replication table to be ignored

The function of replicate wild do table is the same as that of replication do table, but wildcards can be used

The function of replicate wild ignore table is the same as that of replication ignore table, but wildcards can be added

Example 1:

The slave database ignores the replication of test3. However, it should be noted that in fact, the relay log of the slave database is from the relevant logs about test3, but the slave database is not used.

Two configurations for adding wildcards

replicate-wild-do-table=db_ Name.% # only copy which table of which library

Replicate wild ignore table = mysql.%? Which table of which library is ignored