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

This article mainly introduces a brief analysis of Mysql replication filter, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

Replication filter: replicates only one or more database-related data, not all; carried out by replication filter

There are two filtering methods, either on the primary cloud server or on the slave server

(1) filtering on the primary server

To achieve selective replication, copy only part of the master node, select several library master nodes to filter from the source, and have a choice when writing to the binary log, affecting the point-in-time restore of other libraries

The primary server only records writes related to a specific database to the binary log.

Problem: the time-point recovery of other libraries will not be implemented, affecting the point-in-time restore of other libraries

The following options are used on the main node, and the following is a blacklist and whitelist. You can choose either of the two from the same library.

Binlog_do_db= # whitelist, binary log of which libraries are written to binlog, and multiple library names are separated by commas

Binlog_ignore_db=# blacklist. The libraries written here do not record binary log, and multiple libraries are separated by commas.

But the problem here is that if a library fails, there will be a problem when you want to restore it, so it is generally not filtered on the primary node, which recommends that all libraries be written to the binary log.

(2) filter from the server

From the SQL THREAD of the server, only the database or table-related events of interest are replayed and applied locally

Problem: it consumes more network IO and disk IO

Replay the selection of the library:

Replicate_Do_DB=

Replicate_Ignore_DB=

Choose to replay the table:

Replicate_Do_Table=

Replicate_Ignore_Table=

These two are similar to the above two, and the following tables can use wildcards

Replicate_Wild_Do_Table=

Replicate_Wild_Ignore_Table=

If the same library is both allowed and rejected, the library will be copied

Examples

Suppose there is a library on the master server that is sunnydb, but the database is not replayed when the slave server is replayed, so set the following on slave server 73

Note that the slave process should be closed before setting.

MariaDB [test2] > stop slave

MariaDB [test2] > set @ @ global.Replicate_ignore_DB=sunnydb;# this parameter is recommended to write to the configuration file, otherwise the next restart will fail

Then create the database sunnydb on the master server 71, and all the binary information of the library will be copied to the slave_log of the slave server, but the data of the sunnydb library will not be replayed by the sql thread on the server 73.

Read the above about the Mysql replication filter brief analysis, hope to bring some help to everyone in the practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.