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

发表评论