MySQL 过滤复制+复制映射配置方法

场景

node1 和 node2 为两台不同业务的MySQL服务器。
业务方有个需求，需要将node1上的 employees库的departments 、dept_manager 这2张表同步到 node2 的 hellodb 库下面。

这里涉及到3个方面问题，

版本升级，5.5 -> 5.7
复制过滤，只复制 departments 、dept_manager 2张表
重写库名，主库名为 employees ，备库名为 hellodb

接下来，我们一步步解决上述的 3 个问题。

架构图1：

架构图2：

架构图3：

一、版本升级

原主库版本是 5.5.57，新库版本是 5.7.21（开启了gtid，master_auto_position=1）。

首先要把新库的复制改为传统复制：

# node2 && node3

stop slave;
CHANGE MASTER TO master_auto_position=0;
start slave;

然后关掉 GTID：

注意：关闭/开启GTID时，需要从库没有延迟，且SHOW STATUS LIKE 'ONGOING_ANONYMOUS_TRANSACTION_COUNT'; 的值为0

# node2 && node3

# node2: set global gtid_mode=ON_PERMISSIVE;
# node3: set global gtid_mode=ON_PERMISSIVE;
# node2: set global gtid_mode=OFF_PERMISSIVE;
# node3: set global gtid_mode=OFF_PERMISSIVE;
# node2: set global gtid_mode=OFF; 
# node3: set global gtid_mode=OFF; 
# node2: set global enforce_gtid_consistency=OFF;
# node3: set global enforce_gtid_consistency=OFF;

# 注意 GTID 的在线开启和关闭，主从需要依次逐步开启

这样，5.5 到 5.7 的复制就不会有GTID的硬性问题了（如果你碰到其他不兼容，比如datetime默认值问题，谷歌解决即可）

然后把 node1 的employees库的departments 、dept_manager 这2张表同步到 node3 的 hellodb 库下面。

# node3

stop slave;
change master to ...

附：如何从传统复制改为基于GTID的复制？

# 开启 GTID
主从： set global gtid_mode=OFF_PERMISSIVE;
主从： set global gtid_mode=ON_PERMISSIVE;
主从： set global enforce_gtid_consistency=ON;
主从： set global gtid_mode=ON;

# 改 auto_increment = 1
stop slave;
CHANGE MASTER TO master_auto_position=1;
start slave;

二、复制过滤及重写库名

接下来设置复制过滤，我采用了笨重的重启方法（5.7 已经支持在线设置复制过滤了），这也是为什么业务迁到 node2，我却用node3 作为 node1的从库，因为需要重启。

修改node3的配置文件：

replicate-rewrite-db = employees -> hellodb
replicate-wild-do-table=hellodb.departments
replicate-wild-do-table=hellodb.dept_manager

注意，如果有 replicate-rewrite-db ， replicate-wild-do-table 后面的库名要写 rewrite 之后的库名，而不是原来的库名。

然后重启 node3，现在，node1 employees 库下面的 departments 、dept_manager 表，就会实时同步到 node3和node2 。

node1 -> node3 -> node2

show slave status\G
Replicate_Wild_Do_Table: hellodb.departments,hellodb.dept_manager
Replicate_Rewrite_DB: (employees,hellodb)

附：在线修改复制过滤

Syntax:
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这2个库：（要先停止SQL线程）
使用命令很简单：CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);

# 要是又要全部的库都要同步该如何操作呢，也很简单。
STOP SLAVE SQL_THREAD;
CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();
start SLAVE SQL_THREAD;

# 置空多个过滤设置
CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB=(),REPLICATE_WILD_DO_TABLE=();

# 同样可以可以设置只同步某个库下面的某张表，或者不同步某个库下面的某张表。看命令。
mysql> CHANGE REPLICATION FILTER
-> REPLICATE_WILD_DO_TABLE = ('db1.t1%'),
-> REPLICATE_WILD_IGNORE_TABLE = ('db1.t2%');

# 设置同时同步1个库下面的某些表可以这样写：
mysql> CHANGE REPLICATION FILTER
-> REPLICATE_WILD_DO_TABLE = ('db2.t1%','db2.t2%');

# 设置重写DB：（注意需要加2个括号，否则语法报错，2个括号是因为可以同时设置多对重写规则）
CHANGE REPLICATION FILTER REPLICATE_REWRITE_DB = ((db_activity,db_webadmin),(db_a,db_b)); 

# 设置重写 8.0才支持 for channel 语法
CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%'), REPLICATE_REWRITE_DB = ((db0,db1)) FOR CHANNEL 'channel_db1';

附2：MySQL复制过滤流程

参考官网：https://dev.mysql.com/doc/refman/5.7/en/replication-rules-db-options.html

在复制过滤中，有这样一条规则：

Note：
Only DML statements can be logged using the row format. DDL statements are always logged as statements, even when binlog_format=ROW. 
All DDL statements are therefore always filtered according to the rules for statement-based replication. 
This means that you must select the default database explicitly with a USE statement in order for a DDL statement to be applied.

意思是说，DDL语句永远是以 statement-based 的格式记录在binlog中的（即使 binlog_format=ROW）。在复制过滤中，要使 DDL 在设置复制过滤的从库生效，必须显示指定数据库（使用 USE db 语句）。

由此，小编做了一个测试，A B C 三个实例，B是A的从库，C是设置了复制过滤的A的从库。在A执行语句：

ALTER TABLE `db1`.`tb1` DROP INDEX `idx_abc`, ADD INDEX `idx_abc` (`a`,`b`,`c`);

A的binglog如下：
use `test`/*!*/;
SET TIMESTAMP=1566374584/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=45/*!*/;
ALTER TABLE `db1`.`tb1`
DROP INDEX `idx_abc`,
ADD INDEX `idx_abc` (`a`,`b`,`c`)
/*!*/;

可以看到，在未显示 USE db 的情况下，MySQL使用了默认的test库。结果是：B节点正确复制了该语句，C节点却没有。在C的error log中找到如下错误信息：

2019-08-21T16:03:04.376974+08:00 144172 [Note] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.616178, end_log_pos 31881792; 
Could not execute Query event. Detailed error: Slave SQL thread ignored the query because of replicate-*-table rules; Error log throttle is enabled. 
This error will not be displayed for next 60 secs. It will be suppressed, Error_code: 1237

（一）对于 Database-Level Replication，MySQL的处理流程如下：

1. Which logging format is used?

STATEMENT. Test the default database.
ROW. Test the database affected by the changes.

2. Are there any --replicate-do-db options?

Yes. Does the database match any of them?
- 　　Yes. Continue to Step 4.
- 　　No. Ignore the update and exit.
No. Continue to step 3.

3. Are there any --replicate-ignore-db options?

Yes. Does the database match any of them?
- 　　Yes. Ignore the update and exit.
- 　　No. Continue to step 4.
No. Continue to step 4.

4. Proceed to checking the table-level replication options, if there are any. For a description of how these options are checked, see Section 16.2.5.2, “Evaluation of Table-Level Replication Options”.

（二）对于 Table-Level Replication，MySQL的处理流程如下：

1. Are there any table replication options?

Yes. Continue to step 2.
No. Execute the update and exit.

2. Which logging format is used?

STATEMENT. Carry out the remaining steps for each statement that performs an update.
ROW. Carry out the remaining steps for each update of a table row.

3. Are there any --replicate-do-table options?

Yes. Does the table match any of them?
- 　　Yes. Execute the update and exit.
- 　　No. Continue to step 4.
No. Continue to step 4.

4. Are there any --replicate-ignore-table options?

Yes. Does the table match any of them?
- 　　Yes. Ignore the update and exit.
- 　　No. Continue to step 5.
No. Continue to step 5.

5. Are there any --replicate-wild-do-table options?

Yes. Does the table match any of them?
- 　　Yes. Execute the update and exit.
- 　　No. Continue to step 6.
No. Continue to step 6.

6. Are there any --replicate-wild-ignore-table options?

Yes. Does the table match any of them?
- 　　Yes. Ignore the update and exit.
- 　　No. Continue to step 7.
No. Continue to step 7.

7. Is there another table to be tested?

Yes. Go back to step 3.
No. Continue to step 8.

8. Are there any --replicate-do-table or --replicate-wild-do-table options?

Yes. Ignore the update and exit.
No. Execute the update and exit.

注意：如果一张表同时被设置了 --replicate-do-table and --replicate-wild-do-table option, 或 --replicate-ignore-table and --replicate-wild-ignore-table ，那么只有 binlog_format=ROW 的 DML 语句可以被复制到从库。

posted @ 2019-07-18 18:27 LeRoi 阅读(2384) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

LEROI

Let the world a little bit different because of me !

MySQL 过滤复制+复制映射配置方法

场景

一、版本升级

二、复制过滤及重写库名

附：在线修改复制过滤

附2：MySQL复制过滤流程

公告

LEROI

Let the world a little bit different because of me !

MySQL 过滤复制+复制映射 配置方法

场景

一、版本升级

二、复制过滤及重写库名

附：在线修改复制过滤

附2：MySQL复制过滤流程

公告

MySQL 过滤复制+复制映射配置方法