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导致。

发表评论