Mycat分库分表实时同步到GreatSQL

邓力波

这个事情怎么产生的

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

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

1. server1:db1.tab->gdb:db.tab;
2. server2:db2.tab->gdb:db.tab;
3. server3:db3.tab->gdb:db.tab;

复制代码

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

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

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

1. replicate_rewrite_db="channel_1:test_rep1->test_rep"
2. replicate_rewrite_db="channel_2:test_rep2->test_rep"
3. replicate_rewrite_db="channel_3:test_rep3->test_rep"

复制代码

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

1. slave-skip-errors=ddl_exist_errors

复制代码

验证一下

为了简化问题，MyCat集群咱们就不搭建了，简化为多源同步复制问题。
1.初始化4个实例，同步关系如下

源端口源DB_NAME目标端口目标映射DBchannel_name3306test_rep13309test_repchannel_33063307test_rep23309test_repchannel_33073308test_rep33309test_repchannel_33082.在3309的实例配置文件中，添加库映射关系配置和DDL冲突忽略参数

1. replicate_rewrite_db="channel_3306:test_rep1->test_rep"
2. replicate_rewrite_db="channel_3307:test_rep2->test_rep"
3. replicate_rewrite_db="channel_3308:test_rep3->test_rep"
4. slave-skip-errors=ddl_exist_errors

复制代码

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

1. 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';
3. 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';
5. 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';
7. greatsql> start slave;

复制代码

3.检查channel配置状态

1. greatsql> show slave status \G
2. *************************** 1. row ***************************
3.                Slave_IO_State: Waiting for source to send event
4.                   Master_Host: 172.17.137.91
5.                   Master_User: greatsql
6.                   Master_Port: 3306
7.                 Connect_Retry: 60
8.               Master_Log_File: binlog.000002
9.           Read_Master_Log_Pos: 1119
10.                Relay_Log_File: relaylog-channel_3306.000007
11.                 Relay_Log_Pos: 397
12.         Relay_Master_Log_File: binlog.000002
13.              Slave_IO_Running: Yes
14.             Slave_SQL_Running: Yes
15.               Replicate_Do_DB:
16.           Replicate_Ignore_DB:
17.            Replicate_Do_Table:
18.        Replicate_Ignore_Table:
19.       Replicate_Wild_Do_Table:
20.   Replicate_Wild_Ignore_Table:
21.                    Last_Errno: 0
22.                    Last_Error:
23.                  Skip_Counter: 0
24.           Exec_Master_Log_Pos: 1119
25.               Relay_Log_Space: 606
26.               Until_Condition: None
27.                Until_Log_File:
28.                 Until_Log_Pos: 0
29.            Master_SSL_Allowed: No
30.            Master_SSL_CA_File:
31.            Master_SSL_CA_Path:
32.               Master_SSL_Cert:
33.             Master_SSL_Cipher:
34.                Master_SSL_Key:
35.         Seconds_Behind_Master: 0
36. Master_SSL_Verify_Server_Cert: No
37.                 Last_IO_Errno: 0
38.                 Last_IO_Error:
39.                Last_SQL_Errno: 0
40.                Last_SQL_Error:
41.   Replicate_Ignore_Server_Ids:
42.              Master_Server_Id: 3306
43.                   Master_UUID: 5facacd7-9ed6-11ee-b76b-00163e5af5d6
44.              Master_Info_File: mysql.slave_master_info
45.                     SQL_Delay: 0
46.           SQL_Remaining_Delay: NULL
47.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
48.            Master_Retry_Count: 86400
49.                   Master_Bind:
50.       Last_IO_Error_Timestamp:
51.      Last_SQL_Error_Timestamp:
52.                Master_SSL_Crl:
53.            Master_SSL_Crlpath:
54.            Retrieved_Gtid_Set:
55.             Executed_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:3,
56. 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:3,
57. 5facacd7-9ed6-11ee-b76b-00163e5af5d6:1-4,
58. 9a5f9c26-4262-11ee-85fd-00163e5af5d6:1-16571224
59.                 Auto_Position: 1
60.          Replicate_Rewrite_DB: (test_rep1,test_rep)
61.                  Channel_Name: channel_3306
62.            Master_TLS_Version:
63.        Master_public_key_path:
64.         Get_master_public_key: 0
65.             Network_Namespace:
66. *************************** 2. row ***************************
67.                Slave_IO_State: Waiting for source to send event
68.                   Master_Host: 172.17.137.91
69.                   Master_User: greatsql
70.                   Master_Port: 3307
71.                 Connect_Retry: 60
72.               Master_Log_File: binlog.000002
73.           Read_Master_Log_Pos: 1119
74.                Relay_Log_File: relaylog-channel_3307.000004
75.                 Relay_Log_Pos: 1034
76.         Relay_Master_Log_File: binlog.000002
77.              Slave_IO_Running: Yes
78.             Slave_SQL_Running: Yes
79.               Replicate_Do_DB:
80.           Replicate_Ignore_DB:
81.            Replicate_Do_Table:
82.        Replicate_Ignore_Table:
83.       Replicate_Wild_Do_Table:
84.   Replicate_Wild_Ignore_Table:
85.                    Last_Errno: 0
86.                    Last_Error:
87.                  Skip_Counter: 0
88.           Exec_Master_Log_Pos: 1119
89.               Relay_Log_Space: 1243
90.               Until_Condition: None
91.                Until_Log_File:
92.                 Until_Log_Pos: 0
93.            Master_SSL_Allowed: No
94.            Master_SSL_CA_File:
95.            Master_SSL_CA_Path:
96.               Master_SSL_Cert:
97.             Master_SSL_Cipher:
98.                Master_SSL_Key:
99.         Seconds_Behind_Master: 0
100. Master_SSL_Verify_Server_Cert: No
101.                 Last_IO_Errno: 0
102.                 Last_IO_Error:
103.                Last_SQL_Errno: 0
104.                Last_SQL_Error:
105.   Replicate_Ignore_Server_Ids:
106.              Master_Server_Id: 3307
107.                   Master_UUID: 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6
108.              Master_Info_File: mysql.slave_master_info
109.                     SQL_Delay: 0
110.           SQL_Remaining_Delay: NULL
111.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
112.            Master_Retry_Count: 86400
113.                   Master_Bind:
114.       Last_IO_Error_Timestamp:
115.      Last_SQL_Error_Timestamp:
116.                Master_SSL_Crl:
117.            Master_SSL_Crlpath:
118.            Retrieved_Gtid_Set: 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:1-2:4
119.             Executed_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:3,
120. 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:3,
121. 5facacd7-9ed6-11ee-b76b-00163e5af5d6:1-4,
122. 9a5f9c26-4262-11ee-85fd-00163e5af5d6:1-16571224
123.                 Auto_Position: 1
124.          Replicate_Rewrite_DB: (test_rep2,test_rep)
125.                  Channel_Name: channel_3307
126.            Master_TLS_Version:
127.        Master_public_key_path:
128.         Get_master_public_key: 0
129.             Network_Namespace:
130. *************************** 3. row ***************************
131.                Slave_IO_State: Waiting for source to send event
132.                   Master_Host: 172.17.137.91
133.                   Master_User: greatsql
134.                   Master_Port: 3308
135.                 Connect_Retry: 60
136.               Master_Log_File: binlog.000002
137.           Read_Master_Log_Pos: 1119
138.                Relay_Log_File: relaylog-channel_3308.000004
139.                 Relay_Log_Pos: 1034
140.         Relay_Master_Log_File: binlog.000002
141.              Slave_IO_Running: Yes
142.             Slave_SQL_Running: Yes
143.               Replicate_Do_DB:
144.           Replicate_Ignore_DB:
145.            Replicate_Do_Table:
146.        Replicate_Ignore_Table:
147.       Replicate_Wild_Do_Table:
148.   Replicate_Wild_Ignore_Table:
149.                    Last_Errno: 0
150.                    Last_Error:
151.                  Skip_Counter: 0
152.           Exec_Master_Log_Pos: 1119
153.               Relay_Log_Space: 1243
154.               Until_Condition: None
155.                Until_Log_File:
156.                 Until_Log_Pos: 0
157.            Master_SSL_Allowed: No
158.            Master_SSL_CA_File:
159.            Master_SSL_CA_Path:
160.               Master_SSL_Cert:
161.             Master_SSL_Cipher:
162.                Master_SSL_Key:
163.         Seconds_Behind_Master: 0
164. Master_SSL_Verify_Server_Cert: No
165.                 Last_IO_Errno: 0
166.                 Last_IO_Error:
167.                Last_SQL_Errno: 0
168.                Last_SQL_Error:
169.   Replicate_Ignore_Server_Ids:
170.              Master_Server_Id: 3308
171.                   Master_UUID: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6
172.              Master_Info_File: mysql.slave_master_info
173.                     SQL_Delay: 0
174.           SQL_Remaining_Delay: NULL
175.       Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
176.            Master_Retry_Count: 86400
177.                   Master_Bind:
178.       Last_IO_Error_Timestamp:
179.      Last_SQL_Error_Timestamp:
180.                Master_SSL_Crl:
181.            Master_SSL_Crlpath:
182.            Retrieved_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:1-2:4
183.             Executed_Gtid_Set: 49b66af0-9ed6-11ee-ae4f-00163e5af5d6:3,
184. 5a369d0b-9ed6-11ee-b3d1-00163e5af5d6:3,
185. 5facacd7-9ed6-11ee-b76b-00163e5af5d6:1-4,
186. 9a5f9c26-4262-11ee-85fd-00163e5af5d6:1-16571224
187.                 Auto_Position: 1
188.          Replicate_Rewrite_DB: (test_rep3,test_rep)
189.                  Channel_Name: channel_3308
190.            Master_TLS_Version:
191.        Master_public_key_path:
192.         Get_master_public_key: 0
193.             Network_Namespace:
194. 3 rows in set, 1 warning (0.00 sec)

复制代码

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

1. $ 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'
2.            Replica_IO_Running: Yes
3.           Replica_SQL_Running: Yes
4.          Replicate_Rewrite_DB: (test_rep1,test_rep)
5.                  Channel_Name: channel_3306
6.            Replica_IO_Running: Yes
7.           Replica_SQL_Running: Yes
8.          Replicate_Rewrite_DB: (test_rep2,test_rep)
9.                  Channel_Name: channel_3307
10.            Replica_IO_Running: Yes
11.           Replica_SQL_Running: Yes
12.          Replicate_Rewrite_DB: (test_rep3,test_rep)
13.                  Channel_Name: channel_3308

复制代码

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

1. greatsql> select @@replica_skip_errors;
2. +---------------------------------------------------+
3. | @@replica_skip_errors                             |
4. +---------------------------------------------------+
5. | 1007,1008,1050,1051,1054,1060,1061,1068,1091,1146 |
6. +---------------------------------------------------+
7. 1 row in set (0.00 sec)

复制代码

5.数据同步验证

• 在3309库中，创建database test_rep
  

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

• 在3306库中，创建database test_rep1，并且创建tab1表
  

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3306 -s
3. greatsql> create database test_rep1;
4. greatsql> use test_rep1;
5. greatsql> create table tab1(id int primary key auto_increment, cname varchar(10), age int);

复制代码

• 在3307库中，创建database test_rep2，并且创建tab1表
  

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3307 -s
3. greatsql> create database test_rep2;
4. greatsql> use test_rep2;
5. greatsql> create table tab1(id int primary key auto_increment, cname varchar(10), age int);

复制代码

• 在3308库中，创建database test_rep3，并且创建tab1表
  

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3308 -s
3. greatsql> create database test_rep3;
4. greatsql> use test_rep3;
5. greatsql> create table tab1(id int primary key auto_increment, cname varchar(10), age int);

复制代码

• 在3309中确认database及表的同步
  

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s
3. greatsql> show databases;
4. Database
5. information_schema
6. mysql
7. performance_schema
8. sys
9. test_db
10. test_rep
11. test_rep1
12. test_rep2
13. test_rep3
15. greatsql> show tables from test_rep;
16. Tables_in_test_rep
17. tab1
18. greatsql> show tables from test_rep1;
19. greatsql> show tables from test_rep2;
20. greatsql> show tables from test_rep3;

复制代码

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

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

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

复制代码

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

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3306 -s test_rep1 -e 'select * from tab1'
2. id       cname    age
3. 1        a        10
5. $ mysql -ugreatsql -h127.0.0.1 -p -P3307 -s test_rep2 -e 'select * from tab1'
6. id       cname    age
7. 2        b        20
9. $ mysql -ugreatsql -h127.0.0.1 -p -P3308 -s test_rep3 -e 'select * from tab1'
10. id       cname    age
11. 3        c        30
13. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep -e 'select * from tab1'
14. id       cname    age
15. 1        a        10
16. 2        b        20
17. 3        c        30
19. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep1 -e 'select * from tab1'
20. ERROR 1146 (42S02) at line 1: Table 'test_rep1.tab1' doesn't exist
22. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep2 -e 'select * from tab1'
23. ERROR 1146 (42S02) at line 1: Table 'test_rep2.tab1' doesn't exist
25. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s test_rep3 -e 'select * from tab1'
26. 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中是没有表存在的。

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

1. greatsql> alter table tab1 add index idx_cname(cname);

复制代码

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

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s
3. greatsql> use test_rep
4. greatsql> show create table tab1 \G
5. *************************** 1. row ***************************
6.        Table: tab1
7. Create Table: CREATE TABLE `tab1` (
8.   `id` int NOT NULL AUTO_INCREMENT,
9.   `cname` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
10.   `age` int DEFAULT NULL,
11.   PRIMARY KEY (`id`),
12.   KEY `idx_cname` (`cname`)
13. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
14. 1 row in set (0.00 sec)

复制代码

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

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

复制代码

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

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -s
2. Welcome to the MySQL monitor.  Commands end with ; or \g.
3. greatsql> select * from test_rep.tab1;

复制代码

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

1. $ mysql -ugreatsql -h127.0.0.1 -p -P3309 -e 'show replica status \G'| grep -E 'Replica_IO_Running|Replica_SQL_Running|Channel_Name'
2. greatsql: [Warning] Using a password on the command line interface can be insecure.
3.            Replica_IO_Running: Yes
4.           Replica_SQL_Running: Yes
5.     Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
6.                  Channel_Name: channel_3306
7.            Replica_IO_Running: Yes
8.           Replica_SQL_Running: Yes
9.     Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
10.                  Channel_Name: channel_3307
11.            Replica_IO_Running: Yes
12.           Replica_SQL_Running: Yes
13.     Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
14.                  Channel_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
  

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/17944591
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！