江苏龙网

  1. 主页 > 生活百科 > >

MyCat分库分表实时同步到GreatSQL

MyCAT

这个事情怎么产生的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个实例，同步关系如下 源端口
源DB_NAME
目标端口
目标映射DB
channel_name
3306
test_rep1
3309
test_rep
channel_3306
3307
test_rep2
3309
test_rep
channel_3307
3308
test_rep3
3309
test_rep
channel_3308
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_errors4.在3309实例中，配置三个channelgreatsql> 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 eventMaster_Host: 172.17.137.91Master_User: greatsqlMaster_Port: 3306Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 1119Relay_Log_File: relaylog-channel_3306.000007Relay_Log_Pos: 397Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1119Relay_Log_Space: 606Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 3306Master_UUID: 5facacd7-9ed6-11ee-b76b-00163e5af5d6Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_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-16571224Auto_Position: 1Replicate_Rewrite_DB: (test_rep1,test_rep)Channel_Name: channel_3306Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0.NETwork_Namespace: *************************** 2. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.17.137.91Master_User: greatsqlMaster_Port: 3307Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 1119Relay_Log_File: relaylog-channel_3307.000004Relay_Log_Pos: 1034Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1119Relay_Log_Space: 1243Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 3307Master_UUID: 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_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:4Executed_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-16571224Auto_Position: 1Replicate_Rewrite_DB: (test_rep2,test_rep)Channel_Name: channel_3307Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace: *************************** 3. row ***************************Slave_IO_State: Waiting for source to send eventMaster_Host: 172.17.137.91Master_User: greatsqlMaster_Port: 3308Connect_Retry: 60Master_Log_File: binlog.000002Read_Master_Log_Pos: 1119Relay_Log_File: relaylog-channel_3308.000004Relay_Log_Pos: 1034Relay_Master_Log_File: binlog.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1119Relay_Log_Space: 1243Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 3308Master_UUID: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6Master_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_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:4Executed_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-16571224Auto_Position: 1Replicate_Rewrite_DB: (test_rep3,test_rep)Channel_Name: channel_3308Master_TLS_Version:Master_public_key_path:Get_master_public_key: 0Network_Namespace: 3 rows in set, 1 warning (0.00 sec)


推荐阅读


上一篇：Redis 实现多规则限流的思考与实践

下一篇：Kubernetes 100个常用命令！