写点什么

Mycat 分库分表实时同步到 GreatSQL

作者:GreatSQL
  • 2024-01-04
    福建
  • 本文字数:8206 字

    阅读完需:约 27 分钟

Mycat 分库分表实时同步到 GreatSQL

这个事情怎么产生的

MyCat 作为经典的分库分表中间件,在长时间内被广泛认为是管理超大 MySQL 数据库集合的有效解决方案。近来接到客户需求,需要将 MyCat 集群迁移到 GreatSQL 中,并且在一段时间内需要实时从 MyCat 中同步数据到 GreatSQL 中,全量同步数据比较容易操作,增量同步有如下两个棘手的问题:


  1. 多个 server,不同的库名字,都要同步到 GreatSQL 一个库中,即同步关系如下


server1:db1.tab->gdb:db.tab;server2:db2.tab->gdb:db.tab;server3:db3.tab->gdb:db.tab;
复制代码


  1. ddl 同步多次执行会冲突。当 MyCat 的表中添加一个索引、添加一个字段时,实际上是后端所有 db 都会执行这个 DDL,同步到 GreatSQL 时,多次执行 DDL,复制会异常中断。


为了解决上面两个问题,经过查询资料,发现有两个不常用,官方也不建议使用的功能,刚好能够满足需求


  1. 为解决库名映射问题:需要在配置文件中添加参数


replicate_rewrite_db="channel_1:test_rep1->test_rep"replicate_rewrite_db="channel_2:test_rep2->test_rep"replicate_rewrite_db="channel_3:test_rep3->test_rep"
复制代码


  1. 为了解决 DDL 同步后重复执行导致复制中断问题,在配置文件中添加


slave-skip-errors=ddl_exist_errors
复制代码

验证一下

为了简化问题,MyCat 集群咱们就不搭建了,简化为多源同步复制问题。

1.初始化 4 个实例,同步关系如下

2.在 3309 的实例配置文件中,添加库映射关系配置和 DDL 冲突忽略参数

replicate_rewrite_db="channel_3306:test_rep1->test_rep"replicate_rewrite_db="channel_3307:test_rep2->test_rep"replicate_rewrite_db="channel_3308:test_rep3->test_rep"slave-skip-errors=ddl_exist_errors
复制代码

4.在 3309 实例中,配置三个 channel

greatsql> change master to MASTER_HOST = '172.17.137.91',MASTER_USER = 'greatsql',MASTER_PASSWORD = 'greatsql',MASTER_PORT = 3306,MASTER_AUTO_POSITION = 1 FOR CHANNEL 'channel_3306';
greatsql> change master to MASTER_HOST = '172.17.137.91',MASTER_USER = 'greatsql',MASTER_PASSWORD = 'greatsql',MASTER_PORT = 3307,MASTER_AUTO_POSITION = 1 FOR CHANNEL 'channel_3307';
greatsql> change master to MASTER_HOST = '172.17.137.91',MASTER_USER = 'greatsql',MASTER_PASSWORD = 'greatsql',MASTER_PORT = 3308,MASTER_AUTO_POSITION = 1 FOR CHANNEL 'channel_3308';
greatsql> start slave;
复制代码

3.检查 channel 配置状态

greatsql> show slave status \G*************************** 1. row ***************************               Slave_IO_State: Waiting for source to send event                  Master_Host: 172.17.137.91                  Master_User: greatsql                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: binlog.000002          Read_Master_Log_Pos: 1119               Relay_Log_File: relaylog-channel_3306.000007                Relay_Log_Pos: 397        Relay_Master_Log_File: binlog.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 1119              Relay_Log_Space: 606              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 3306                  Master_UUID: 5facacd7-9ed6-11ee-b76b-00163e5af5d6             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:3,5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:3,5facacd7-9ed6-11ee-b76b-00163e5af5d6:1-4,9a5f9c26-4262-11ee-85fd-00163e5af5d6:1-16571224                Auto_Position: 1         Replicate_Rewrite_DB: (test_rep1,test_rep)                 Channel_Name: channel_3306           Master_TLS_Version:        Master_public_key_path:         Get_master_public_key: 0            Network_Namespace: *************************** 2. row ***************************               Slave_IO_State: Waiting for source to send event                  Master_Host: 172.17.137.91                  Master_User: greatsql                  Master_Port: 3307                Connect_Retry: 60              Master_Log_File: binlog.000002          Read_Master_Log_Pos: 1119               Relay_Log_File: relaylog-channel_3307.000004                Relay_Log_Pos: 1034        Relay_Master_Log_File: binlog.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 1119              Relay_Log_Space: 1243              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 3307                  Master_UUID: 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:1-2:4            Executed_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:3,5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:3,5facacd7-9ed6-11ee-b76b-00163e5af5d6:1-4,9a5f9c26-4262-11ee-85fd-00163e5af5d6:1-16571224                Auto_Position: 1         Replicate_Rewrite_DB: (test_rep2,test_rep)                 Channel_Name: channel_3307           Master_TLS_Version:        Master_public_key_path:         Get_master_public_key: 0            Network_Namespace: *************************** 3. row ***************************               Slave_IO_State: Waiting for source to send event                  Master_Host: 172.17.137.91                  Master_User: greatsql                  Master_Port: 3308                Connect_Retry: 60              Master_Log_File: binlog.000002          Read_Master_Log_Pos: 1119               Relay_Log_File: relaylog-channel_3308.000004                Relay_Log_Pos: 1034        Relay_Master_Log_File: binlog.000002             Slave_IO_Running: Yes            Slave_SQL_Running: Yes              Replicate_Do_DB:           Replicate_Ignore_DB:            Replicate_Do_Table:        Replicate_Ignore_Table:       Replicate_Wild_Do_Table:   Replicate_Wild_Ignore_Table:                    Last_Errno: 0                   Last_Error:                  Skip_Counter: 0          Exec_Master_Log_Pos: 1119              Relay_Log_Space: 1243              Until_Condition: None               Until_Log_File:                 Until_Log_Pos: 0           Master_SSL_Allowed: No           Master_SSL_CA_File:            Master_SSL_CA_Path:               Master_SSL_Cert:             Master_SSL_Cipher:                Master_SSL_Key:         Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:   Replicate_Ignore_Server_Ids:              Master_Server_Id: 3308                  Master_UUID: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6             Master_Info_File: mysql.slave_master_info                    SQL_Delay: 0          SQL_Remaining_Delay: NULL      Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates           Master_Retry_Count: 86400                  Master_Bind:       Last_IO_Error_Timestamp:      Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:1-2:4            Executed_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:3,5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:3,5facacd7-9ed6-11ee-b76b-00163e5af5d6:1-4,9a5f9c26-4262-11ee-85fd-00163e5af5d6:1-16571224                Auto_Position: 1         Replicate_Rewrite_DB: (test_rep3,test_rep)                 Channel_Name: channel_3308           Master_TLS_Version:        Master_public_key_path:         Get_master_public_key: 0            Network_Namespace: 3 rows in set, 1 warning (0.00 sec)
复制代码


在上面的输出中,可以重点关注如下字段信息,说明 db 转换映射成功


$ MYSQL_PWD=greatsql mysql -ugreatsql -h127.0.0.1 -P3309 -e 'show replica status \G'| grep -wE 'Replica_IO_Running|Replica_SQL_Running|Replicate_Rewrite_DB|Channel_Name'           Replica_IO_Running: Yes          Replica_SQL_Running: Yes         Replicate_Rewrite_DB: (test_rep1,test_rep)                 Channel_Name: channel_3306           Replica_IO_Running: Yes          Replica_SQL_Running: Yes         Replicate_Rewrite_DB: (test_rep2,test_rep)                 Channel_Name: channel_3307           Replica_IO_Running: Yes          Replica_SQL_Running: Yes         Replicate_Rewrite_DB: (test_rep3,test_rep)                 Channel_Name: channel_3308
复制代码


在 3309 实例中,查询 replica_skip_errors,确认复制异常跳过的错误码,设置为 ddl_exist_errors 会自动转换为如下错误码


greatsql> select @@replica_skip_errors;+---------------------------------------------------+| @@replica_skip_errors                             |+---------------------------------------------------+| 1007,1008,1050,1051,1054,1060,1061,1068,1091,1146 |+---------------------------------------------------+1 row in set (0.00 sec)
复制代码

5.数据同步验证

  1. 在 3309 库中,创建 database test_rep


这个库需要手动创建,是测试发现映射关系只对库下面的表生效,库不会自动转换创建。


  1. 在 3306 库中,创建 database test_rep1,并且创建 tab1 表


$ mysql -ugreatsql -h127.0.0.1 -p -P3306 -s
greatsql> create database test_rep1;greatsql> use test_rep1;greatsql> create table tab1(id int primary key auto_increment, cname varchar(10), age int);
复制代码


  1. 在 3307 库中,创建 database test_rep2,并且创建 tab1 表


$ mysql -ugreatsql -h127.0.0.1 -p -P3307 -s
greatsql> create database test_rep2;greatsql> use test_rep2;greatsql> create table tab1(id int primary key auto_increment, cname varchar(10), age int);
复制代码


  1. 在 3308 库中,创建 database test_rep3,并且创建 tab1 表


$ mysql -ugreatsql -h127.0.0.1 -p -P3308 -s
greatsql> create database test_rep3;greatsql> use test_rep3;greatsql> create table tab1(id int primary key auto_increment, cname varchar(10), age int);
复制代码


  1. 在 3309 中确认 database 及表的同步


$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s
greatsql> show databases;Databaseinformation_schemamysqlperformance_schemasystest_dbtest_reptest_rep1test_rep2test_rep3
greatsql> show tables from test_rep;Tables_in_test_reptab1greatsql> show tables from test_rep1;greatsql> show tables from test_rep2;greatsql> show tables from test_rep3;
复制代码


从上面的信息可以看出,在 3309 中,3306、3307、3308 中创建的库均按照原有的名字进行了同步,但是表只同步在了 3309 映射的库 test_rep 中。


  1. 分别在 3306、3307、3308 中插入一条记录


3306 : insert into test_rep1.tab1 values(1,'a',10);3307 : insert into test_rep2.tab1 values(2,'b',20);3308 : insert into test_rep3.tab1 values(3,'c',30);
复制代码


然后在各自节点查询数据插入情况


$ mysql -ugreatsql -h127.0.0.1 -p -P3306 -s test_rep1 -e 'select * from tab1'id       cname    age1        a        10
$ mysql -ugreatsql -h127.0.0.1 -p -P3307 -s test_rep2 -e 'select * from tab1'id cname age2 b 20
$ mysql -ugreatsql -h127.0.0.1 -p -P3308 -s test_rep3 -e 'select * from tab1'id cname age3 c 30
$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep -e 'select * from tab1'id cname age1 a 102 b 203 c 30
$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep1 -e 'select * from tab1'ERROR 1146 (42S02) at line 1: Table 'test_rep1.tab1' doesn't exist
$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep2 -e 'select * from tab1'ERROR 1146 (42S02) at line 1: Table 'test_rep2.tab1' doesn't exist
$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep3 -e 'select * from tab1'ERROR 1146 (42S02) at line 1: Table 'test_rep3.tab1' doesn't exist
复制代码


从上面的查询情况可以看出,3306、3307、3308 节点中只有一条记录,并且记录都被同步到了 3309 的 test_rep.tab1 表中,而且在 3309 的 test_rep1、test_rep2、test_rep3 中是没有表存在的。


  1. 分别在 3306、3307、3308 给表 tab 创建一个索引


greatsql> alter table tab1 add index idx_cname(cname);
复制代码


  1. 观察 3309 中表的索引情况,可以看到索引 idx_cname 被同步过来了


$ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s
greatsql> 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)
复制代码


  1. 分别在 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;
复制代码


  1. 观察 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: Yes          Replica_SQL_Running: Yes    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates                 Channel_Name: channel_3306           Replica_IO_Running: Yes          Replica_SQL_Running: Yes    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates                 Channel_Name: channel_3307           Replica_IO_Running: Yes          Replica_SQL_Running: Yes    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates                 Channel_Name: channel_3308
复制代码


至此 dml、ddl 同步均验证。

方案缺陷

  1. 本方案中,业务访问 MyCat 的表名字,和 server 后端的表名字完全一致,只是库名字不相同,然后 MyCat 代理表名和实际 server 的表名字可以不相同,这种情况下,暂时无法映射处理

  2. MyCat 代理的实际上是多个单独的库,如果这些库之前没有做自增主键步长处理,或者其他一些主键不重复策略,同步过程中,会存在主键冲突导致数据同步中断的情况,需要提前准备处理方案。


最后附上参考资料


Enjoy GreatSQL :)



关于 GreatSQL

GreatSQL 数据库是一款开源免费数据库,可在普通硬件上满足金融级应用场景,具有高可用高性能高兼容高安全等特性,可作为 MySQL 或 Percona Server for MySQL 的理想可选替换。

相关链接

GreatSQL社区


Gitee


Github


Bilibili

技术交流群

微信:添加GreatSQL社区助手好友,微信号wanlidbc发送验证信息加群


QQ 群:533341697

发布于: 刚刚阅读数: 5
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
Mycat分库分表实时同步到GreatSQL_数据迁移_GreatSQL_InfoQ写作社区