MySQL5.5 master-slave in-depth exploration of 'replication filter'

Filtering of MySQL master-slave replication, such as through binlog-ignore-db, replicate-do-db, and replicate-wild-do-table. If you have not studied these filters well

Filtering of MySQL master-slave replication, such as through binlog-ignore-db, replicate-do-db, and replicate-wild-do-table. If you have not studied these filters well

Filtering of MySQL master-slave replication, such as through binlog-ignore-db, replicate-do-db, and replicate-wild-do-table. If these filtering options are not well studied, they may cause master-slave data inconsistency. This article will refer to the MySQL-5.5 official documentation and combined with the experiment, and you will discuss the various settings here.

For the following content, refer to the 5.5 official documentation.

The setting of binlog_format (STATEMENT, ROW, MIXED, and 5.5 is STATEMENT by default) may lead to some differences in replication execution.

When the MIXED format is used, most binlogs are recorded in the STATEMENT format. Only the following situations can be switched to the ROW format:

1. When using the UUID () function

2. When one or more tables with AUTO_INCREMENT columns are updated and 'trigger' or 'stored function' is called

# MIXED always uses statement-based for 'trigger' and 'stored'

3. Execute INSERT DELAYED

4. When a part of a view needs row-based replication (for example, UUID (), the statement for creating the view is changed to row-based.

5. When using user-defined functions (udfs)

6. When a statement is determined to be row-based and its session needs to use a temporary table, all sub-statements in the session will be recorded in the ROW format.

7. When USER (), CURRENT_USER (), or CURRENT_USER is used

8. When the statement references one or more system variables.

9. When LOAD_FILE () is used ()

All DDL statements are based on statements, regardless of binlog_format.

Binlog_format must be consistent; otherwise, the replication fails.

The Binlog format affects the following 'Copy filter' configurations:

-- Binlog-do-db
-- Binlog-ignore-db = ljk
The behavior of this option depends on the binlog format.
Statement-based logging: When use ljk is used (that is, when the current database is ljk), all statements are not recorded in binlog
After logging on to mysql, if you do not use/or use a database other than ljk, executing update ljk. table will record near binlog and copy
Row-based format: indicates that the server does not record any changes to the following table in the ljk database, regardless of the current database (whether or not the use statement is available or not)

-- Replicate-do-db = ljk
The behavior of this option depends on the binlog format.
Statement-based replication: Only statements executed by the master database after use ljk are copied from the slave database.
None of the use statements or statements executed after the use of other libraries are copied.
Row-based replication: only changes to the ljk database will be replicated (no matter which database is used or whether it is used)
Changes outside the ljk library are not copied no matter which database is used or used
-- Replicate-ignore-db

Summary: Statement-based is related to the current database in use. Row-based is more direct and only cares about the specified database 'do or not '.

The following two parameters can be used to filter out copies'
The following two options only affect table changes, and database replication is not affected by these parameters (however, similar to ljk. %, it also takes effect for the database)

-- Replicate-do-table

These two options are different from the descriptions in my experiment. For more information, see the experiment results.

-- Replicate-ignore-table

-- Replicate-wild-do-table = ljk. %
No matter whether you use ljk or use other libraries or do not use it, updates to the ljk library can be copied, and other libraries will not be copied under any circumstances (including creating databases and tables)
-- Replicate-wild-ignore-table

Based on the above, we recommend that you use replicate-wild-do-table/replicate-wild-ignore-table for replication filtering, Which is strict and clear.

Below is the experiment process (MySQL-5.5.39)
1. Add "binlog-ignore-db = mysql" to the master database without filtering the slave database.

Database Operations:
1. If the master database is not used, the database creation statement is executed.
Mysql> create database kai;
Slave Database Replication
2. use mysql in the master database and execute
Mysql> create database kai;
Slave Database Replication

Create a table in the kai database:
1. Do not use
Create table kai. li (id int, name char (15 ));
Slave Database Replication
2. use mysql;
Create table kai. li (id int, name char (15 ));
Slave database does not copy
3. use other libraries;
Create table kai. li (id int, name char (15 ));
Slave Database Replication

Modify Table content:
1. Add data to the li table if the master database does not use
Insert into kai. li values ('1', 'ljk ');
Slave Database Replication
2. The master database uses mysql to add data to the li table.
Slave database does not copy
3. The master database uses other libraries to update the li Library (that is, cross-database updates)
Mysql> use picture;
Mysql> insert into kai. li values ('2', 'lhy ');
Slave Database Replication

Modify the mysql database:
1. Do not use
Mysql> create table mysql. ljk (id int, name varchar (15 ));
Slave Database Replication
2. Use other libraries
Mysql> drop table mysql. ljk;
Slave Database Replication

2. Add "replicate-ignore-db = mysql" to the slave database without filtering.