MySQL主从复制REPLICATE_WILD_IGNORE_TABLE参数排除复制表

项目需要清理主库历史表数据，从库中的历史数据需要保留，对REPLICATE_WILD_IGNORE_TABLE的使用进行了测试

• 主从库环境

主库：

mysql> show slave hosts;
+-----------+------------+------+-----------+--------------------------------------+
| Server_id | Host       | Port | Master_id | Slave_UUID                           |
+-----------+------------+------+-----------+--------------------------------------+
| 210126002 | 2.46.7.214 | 9901 | 210126001 | 4e1288fa-5f84-11eb-9cc3-fa163e6df9e2 |
| 210126003 | 2.46.7.215 | 9901 | 210126001 | 710f3b34-5f84-11eb-af05-fa163eabac76 |
+-----------+------------+------+-----------+--------------------------------------+
2 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_test1        |
| t_test2        |
| tb3            |
| tbs1           |
| tbs2           |
+----------------+
5 rows in set (0.00 sec)


从库：

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_test1        |
| t_test2        |
| tb2            |
| tb3            |
| tbs1           |
| tbs2           |
+----------------+
6 rows in set (0.00 sec)

• 测试目标为删除主库test.tbs1表后，从库test.tbs1表被保留

1、从库设置需要忽略的tbs1表

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =('tbs1');
ERROR 3067 (HY000): Supplied filter list contains a value which is not in the required format 'db_pattern.table_pattern'

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =('test.tbs1');
ERROR 3017 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD first

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =('test.tbs1');
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

2、主库删除tbs1表并查看从库是否保留

主库：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_test1        |
| t_test2        |
| tb3            |
| tbs1           |
| tbs2           |
+----------------+
5 rows in set (0.00 sec)

mysql> drop table tbs1;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_test1        |
| t_test2        |
| tb3            |
| tbs2           |
+----------------+
4 rows in set (0.00 sec)

主库binlog日志：

#220728 15:44:03 server id 210126001  end_log_pos 1517 CRC32 0xc2341c92 	Query	thread_id=23556447	exec_time=0	error_code=0
SET TIMESTAMP=1658994243/*!*/;
SET @@session.pseudo_thread_id=23556447/*!*/;
DROP TABLE `tbs1` /* generated by server */
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

从库：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t_test1        |
| t_test2        |
| tb2            |
| tb3            |
| tbs1           |
| tbs2           |
+----------------+
6 rows in set (0.00 sec)

从库binlog日志中没有同步drop语句：

#220728 15:44:03 server id 210126001  end_log_pos 1574 CRC32 0x5b67c129 	Query	thread_id=23556447	exec_time=0	error_code=0
SET TIMESTAMP=1658994243/*!*/;
COMMIT
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;


上述操作可以发现主库test.tbs1表删除成功而从库该表未被删除，说明上述参数操作生效！！！

• 清除REPLICATE_WILD_IGNORE_TABLE

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE =();
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

CHANGE REPLICATION FILTER 语法参考

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

参数说明：

REPLICATE_DO_DB:包括基于数据库名称的更新。
REPLICATE_IGNORE_DB:排除基于数据库名称的更新。
REPLICATE_DO_TABLE:包括基于表名的更新。
REPLICATE_IGNORE_TABLE:排除基于表名的更新。
REPLICATE_WILD_DO_TABLE:包括基于通配符模式匹配表名的更新。
REPLICATE_WILD_IGNORE_TABLE:排除基于通配符模式匹配表名的更新。
REPLICATE_REWRITE_DB:将从上的新名称替换为主上的指定数据库后，在从上执行更新。