mysql主从必备条件_MySQL主从复制条件过滤

MySQL主从复制是逻辑复制，基于Binlog进行主从数据传输，MySQL提供了丰富的参数来配置主从复制过滤条件。在主库可以设置对于库的数据写入是否写入binlog，在从库可以设置对某些库或者表的binlog是否忽略。本文将简单描述MySQL主从复制过滤的相关参数和常见用法。

MySQL版本：5.7.19

binlog_format：ROW

1. 是否写入binlog

MySQL通过以下两个参数来控制某些库是否写入binlog。

–binlog-do-db=db_name

–binlog-ignore-db=db_name

–binlog-do-db 指定的库名，其内部表数据修改都会写入binlog。

–binlog-ignore-db 指定的库名，其内部表数据修改将不会写入binlog。

这两个参数修改需要重启MySQL，无法动态修改，另外在show global variables 的结果中也无法显示这两个参数。

2. SQL线程过滤日志

从库复制的SQL线程从relay log里读日志，并应用日志，通过设置如下参数，可以控制SQL线程根据库名或表名过滤日志。

–replicate-ignore-db=db_name

–replicate-ignore-table=db_name.tbl_name

–replicate-do-db=db_name

–replicate-do-table=db_name.tbl_name

–replicate-wild-do-table=db_name.tbl_name

–replicate-wild-ignore-table=db_name.tbl_name

–replicate-rewrite-db=from_name->to_name

–replicate-ignore-db 忽略某些库的binlog 日志应用。

–replicate-ignore-table 忽略某些表的binlog 日志应用。

–replicate-do-db 应用某些库的binlog 日志。

–replicate-do-table 应用某些表的binlog 日志。

–replicate-wild-do-table 使用通配符来匹配那些应用binlog日志的表。

–replicate-wild-ignore-table 使用通配符来匹配那些不能应用binlog日志的表。

–replicate-rewrite-db，能够实现主库与从库数据库名称不同的复制，比如主库数据库名为A，从库数据库名为B，实现主库A到从库B的复制。

以上这些参数同样不能动态修改，需要重启MySQL生效。在show global variables 的结果中也无法显示这些参数。

动态设置过滤条件：

MySQL提供了另外一种动态设置从库的复制过滤条件的方式，CHANGE REPLICATION FILTER 语法，设置前先停止复制的SQL线程，设置完成后，再开启SQL线程。具体语法如下：

CHANGE REPLICATION FILTER filter [, filter][, ...]

filter:

REPLICATE_DO_DB = (db_list)

| REPLICATE_IGNORE_DB = (db_list)

| REPLICATE_DO_TABLE = (tbl_list)

| REPLICATE_IGNORE_TABLE = (tbl_list)

| REPLICATE_WILD_DO_TABLE = (wild_tbl_list)

| REPLICATE_WILD_IGNORE_TABLE = (wild_tbl_list)

| REPLICATE_REWRITE_DB = (db_pair_list)

db_list:

db_name[, db_name][, ...]

tbl_list:

db_name.table_name[, db_table_name][, ...]

wild_tbl_list:

'db_pattern.table_pattern'[, 'db_pattern.table_pattern'][, ...]

db_pair_list:

(db_pair)[, (db_pair)][, ...]

db_pair:

from_db, to_db

比如，下面只复制库名为db1和db2中的表：

CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

比如，下面同步db1.t1开头的表，过滤db1.t2开头的表

CHANGE REPLICATION FILTER

REPLICATE_WILD_DO_TABLE = (‘db1.t1%’),

REPLICATE_WILD_IGNORE_TABLE = (‘db1.t2%’);

关于复制过滤，有几点需要额外注意：

(1)如果一个binlog事务里有多个SQL，其中涉及到过滤库名或者表名的SQL将被过滤，不涉及到的则仍然执行。举例如下：

# 主库执行：

begin;

insert into db1.tb1 values(1);

insert into db2.tb2 values(2);

commit;

# 从库设置

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1);

# 从库执行的事务变成如下：

begin;

insert into db2.tb2 values(2);

commit;

(2)如果事务中的所有SQL都是被过滤的，那么从库就会变成执行一个空事务。

# 主库执行：

begin;

insert into db1.tb1 values(1);

insert into db1.tb2 values(2);

commit;

# 从库设置

CHANGE REPLICATION FILTER REPLICATE_IGNORE_DB = (db1);

# 从库执行的事务变成如下：

begin;

commit;

(3)过滤是全局设置的，如果使用channel多通道复制，那么所有通道的复制过滤都是一样的。MySQL 8.0 对这个问题进行了改进，能够对某个通道单独指定复制过滤规则。

3. binlog_format为statement

对于binlog格式为statement时，过滤db的逻辑与row格式不太一样，判断sql是否属于某个db，与use db相关。比如下面这个例子，目的是想要将涉及db1的SQL不写入binlog，结果却与预想不一样。

# 设置：

--binlog-ignore-db=db1

# 执行SQL：

use db2;

update db1.tb set name='123' where id=1;

对db1中的表做了修改，该修改本来是想忽略，不记录binlog，但结果仍然记录在binlog里，原因就是use db2导致。