MySQL复制过滤器参数对比

MySQL复制过滤器参数对比

今天分享一个线上的MySQL复制过滤参数问题。

背景介绍

线上业务同学通常都会自己先搭建一套MySQL服务，自己维护，自己折腾，等到项目要上线，或者遇到某种性能瓶颈的时候，就会想到托管给DBA，这几天我们就遇到了这样一个场景。

某个业务同学自建的MySQL数据库数据量达到了1.6T，运维起来有困难，于是托管给我们。这个数据库的基本情况如下：

1、容量：1.6T

2、数据库：10 个

3、每个数据库中的表：200 个

由于业务的MySQL服务是单点的，而且容量比较大，所以这个托管流程需要分为下面几个步骤：

1、假设业务数据库实例是A，首先利用热备份工具xtrabackup备份数据库实例A

2、利用数据库实例A的备份数据，搭建线上数据库实例B，并和数据库实例A做主从复制

3、利用数据库实例A的备份数据，搭建线上数据库实例C，并和数据库实例B做主从复制

4、最终形成A-->B--->C的级联复制关系，然后将业务切换到数据库实例B上，这样就有了一套线上的主从架构的MySQL数据库。

由于数据量比较大，我们给业务同学提了几个建议：

1、先清理部分业务数据，删除表数据，释放表空间，减少总的数据量。

2、只备份那些需要托管的数据库，让备份搭建从库的过程更顺利。

复制关系搭建上述流程中，由于备份数据，只有部分数据库（这个通过xtrabackup的--database参数实现），所以搭建复制关系的时候，需要用到复制过滤器。

假设数据库实例A有a、b、c、d 4个DB，但是备份的时候，只备份了a、b两个数据库，那么在利用备份数据搭建实例B的时候，就会使用到复制过滤器，常见的复制过滤器选项包含：

库级别：

replicate-do-db

replicate-ignore-db

表级别；

replicate-do-table

replicate-ignore-table

replicate-wild-do-table

replicate-wild-ignore-table

这里第一反应会想到库级别的复制过滤器replicate-ignore-db。实际中也是这么操作的，将c、d 两个数据库写入到复制过滤器中，但是遇到了一个问题。

问题：

这个参数replicate-ignore-db有个特性（摘自官方文档）

Statement-based replication. Tells the replication SQL thread not to replicate any statement where the default database (that is, the one selected by USE) is db_name.

Row-based replication. Tells the replication SQL thread not to update any tables in the database db_name. The default database has no effect.

statement模式的binlog下，它只过滤默认的数据库，也就是你使用use db命令指定的那个数据库。

这也就意味着，如果你使用：

use a;

update c.table xxxx

这个时候，它认为这是在操作数据库a，而实际上c这个数据库的update操作，是不能被过滤的。由于上述实例B上没有c这个数据库，所以会报错：库表不存在

简言之，statement模式下不允许跨库操作。

row格式下，只会按照binlog内容来，过滤所有的包含这个db的语句。默认的数据库名字没有影响。

简言之，row模式下允许跨库操作。

现状：

恰好，我们的业务的binlog格式就是statement的。由于业务一直在运行，不能停，那么这个问题怎么解决呢？

方案：

使用下面两个过滤器来代替即可。

replicate-wild-ignore-table

这两个参数，从名称中的wild就可以看出来，比较"狂野"。他们没有跨库的限制，官方文档如下（重点看最后一句）：

Creates a replication filter by telling the replication SQL (applier) thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the % and _ wildcard characters, which have the same meaning as for the LIKE pattern-matching operator. To specify more than one table, use this option multiple times, once for each table. This works for cross-database updates.

简单看，就是他们支持百分号%和下划线_通配符，然后支持跨库操作。

我们只需要将参数配置成下面的通配符模式即可：

replicate-wild-ignore-table=c.%

replicate-wild-ignore-table=d.%

这样，就能将数据库c和d的所有表的操作都过滤。

如何修改复制过滤器？

在MySQL 5.7 之前，不允许手工在线修改复制过滤器，必须修改配置文件，然后重启服务，在MySQL5.7之后，可以手工在线修改。

设想这么一种场景，如果一开始过滤了数据库c和d，后续我们需要将数据库a的操作也过滤掉，那么是不是需要重启实例B呢？MySQL其实为我们考虑了这样的场景，所以有一个语法：

CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ('db1.new%', 'db2.new%');

这个特性，之前还有一个文章有些，有兴趣可以去关注下：

MySQL动态修改复制过滤器

总结：