同源不同库名同表名同表结构TO同一个目标源同库名不同表名同表结构

数据源A有两个库a,b（都有数据表test_x,test_y,test_z），两个库之间相同表名的表结构完全一致, 目标源一个库车c( 有表名test_x,test_y,test_z，b_test_x,b_test_y,b_test_z，对应到a.b两个库刚好6个表) test_x 与b_test，test_y与b_test_y,test_z与b_test_z的表结构一致

单向同步, 配置了两个channel: 一个channel，对应一个pipeline，对应一个独立的canal（server a），一个映射关系 即想让serverA上a.xxx的数据同步至server B 上c.xxx，让serverA上b.xxx的数据同步至server B 上c.b_xxx,

最终结果发现serverA上a.xxx, b.xxx两个库的数据都同步至server B的 c.xxx了。c.b_xxx没有任何数据

channel 1 <--pipiline 1 <-->canal1(server a)<--> 映射关系（a.(test_x|test_y|test_z)----c.(test_x|test_y|test_z)）

channel2<-->pipeline2<--->canal2(server a) <-->映射关系（b.(test_x|test_y|test_z)----c.(b_test_x|b_test_y|b_test_z)）

serverA+database a

create database a;

CREATE TABLE test_x ( ID int(11) NOT NULL AUTO_INCREMENT, X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE test_y ( ID int(11) NOT NULL AUTO_INCREMENT, Y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE test_z ( ID int(11) NOT NULL AUTO_INCREMENT, Z timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

serverA+database b

create database b;

CREATE TABLE test_x ( ID int(11) NOT NULL AUTO_INCREMENT, X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert into test_x(id,x) values(null,now());

CREATE TABLE test_y ( ID int(11) NOT NULL AUTO_INCREMENT, Y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE test_z ( ID int(11) NOT NULL AUTO_INCREMENT, Z timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; serverB database c

create database c;

CREATE TABLE test_x ( ID int(11) NOT NULL AUTO_INCREMENT, X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE test_y ( ID int(11) NOT NULL AUTO_INCREMENT, Y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE test_z ( ID int(11) NOT NULL AUTO_INCREMENT, Z timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE b_test_x ( ID int(11) NOT NULL AUTO_INCREMENT, X timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE b_test_y ( ID int(11) NOT NULL AUTO_INCREMENT, Y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

CREATE TABLE b_test_z ( ID int(11) NOT NULL AUTO_INCREMENT, Z timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (ID) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; 单向同步一个channel，对应一个pipeline，对应一个独立的canal（server a），一个映射关系

channel 1 <--pipiline 1 <-->canal1(server a)<--> 映射关系（a.(test_x|test_y|test_z)----c.(test_x|test_y|test_z)）

channel2<-->pipeline2<--->canal2(server a) <-->映射关系（b.(test_x|test_y|test_z)----c.(b_test_x|b_test_y|b_test_z)）

server a 操作

mysql> use a; Database changed mysql> show tables; +--------------------+ | Tables_in_a | +--------------------+ | test_x | | test_y | | test_z | +--------------------+ 3 rows in set (0.00 sec)

mysql> insert into test_z(id,z) values(null,now()); Query OK, 1 row affected (0.01 sec)

mysql> select * from test_z; +----+---------------------+ | ID | Z | +----+---------------------+ | 3 | 2017-09-07 22:05:19 | +----+---------------------+ 1 row in set (0.00 sec) 此时server b上c库test_z可以看到数据

mysql> use c; Database changed mysql> show tables; +---------------------+ | Tables_in_c | +---------------------+ | b_test_x | | b_test_y | | b_test_z | | test_x | | test_y | | test_z | +---------------------+ 6 rows in set (0.00 sec)

mysql> select * from test_z; +----+---------------------+ | ID | Z | +----+---------------------+ | 3 | 2017-09-07 22:05:19 | +----+---------------------+ 1 row in set (0.00 sec)

切换库到b

mysql> use b; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A

Database changed mysql> insert into test_z(id,z) values(null,now()); Query OK, 1 row affected (0.00 sec)

mysql> select * from test_z; +----+---------------------+ | ID | Z | +----+---------------------+ | 3 | 2017-09-07 22:06:01 | +----+---------------------+ 1 row in set (0.00 sec) 此时serverb上c库

mysql> select * from test_z; +----+---------------------+ | ID | Z | +----+---------------------+ | 3 | 2017-09-07 22:06:01 | +----+---------------------+ 1 row in set (0.00 sec) 继续往servera的b.test_z插入数据

mysql> insert into test_z(id,z) values(null,now()); Query OK, 1 row affected (0.01 sec)

mysql> select * from test_z; +----+---------------------+ | ID | Z | +----+---------------------+ | 3 | 2017-09-07 22:06:01 | | 4 | 2017-09-07 22:06:36 | +----+---------------------+ 2 rows in set (0.00 sec) server b上c.test_z的最后数据为，这个与server a上库b 的数据一致了

mysql> select * from test_z; +----+---------------------+ | ID | Z | +----+---------------------+ | 3 | 2017-09-07 22:06:01 | | 4 | 2017-09-07 22:06:36 | +----+---------------------+ 2 rows in set (0.00 sec)

原提问者GitHub用户 luyee