MyCat分库分表实时同步到GreatSQL( 三 ) _MyCat

$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -sgreatsql> use test_repgreatsql> show create table tab1 G*************************** 1. row ***************************Table: tab1Create Table: CREATE TABLE `tab1` (`id` int NOT NULL AUTO_INCREMENT,`cname` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,`age` int DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_cname` (`cname`)) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci1 row in set (0.00 sec)

分别在3306、3307、3308做update、delete操作

greatsql> update test_rep1.tab1 set age=110 where id=1;greatsql> update test_rep2.tab1 set age=120 where id=2;greatsql> update test_rep3.tab1 set age=130 where id=3;greatsql> delete from test_rep1.tab1 where id=1;greatsql> delete from test_rep2.tab1 where id=1;greatsql> delete from test_rep3.tab1 where id=1;

查看3309的数据同步情况，确认数据被清理

$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -sWelcome to the MySQL monitor.Commands end with ; or g.greatsql> select * from test_rep.tab1;

观察3个channel的同步情况，可以确认三个复制同步均正常

$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -e 'show replica status G'| grep -E 'Replica_IO_Running|Replica_SQL_Running|Channel_Name'greatsql: [Warning] Using a password on the command line interface can be insecure.Replica_IO_Running: YesReplica_SQL_Running: YesReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesChannel_Name: channel_3306Replica_IO_Running: YesReplica_SQL_Running: YesReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesChannel_Name: channel_3307Replica_IO_Running: YesReplica_SQL_Running: YesReplica_SQL_Running_State: Replica has read all relay log; waiting for more updatesChannel_Name: channel_3308

至此dml、ddl同步均验证。
方案缺陷

本方案中，业务访问MyCat的表名字，和server后端的表名字完全一致，只是库名字不相同，然后MyCat代理表名和实际server的表名字可以不相同，这种情况下，暂时无法映射处理
MyCat代理的实际上是多个单独的库，如果这些库之前没有做自增主键步长处理，或者其他一些主键不重复策略，同步过程中，会存在主键冲突导致数据同步中断的情况，需要提前准备处理方案。

最后附上参考资料

https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_replicate-rewrite-db
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#option_mysqld_slave-skip-errors