Teleport 分库分表最佳实践

如果你想把上游多个数据库实例合并迁移到下游的同一个 ProtonBase 数据库中，可以使用数据同步工具 Teleport 进行分库分表的合并迁移。本文介绍了合并迁移的操作步骤、注意事项、故障排查等内容，适用于以下场景：

分库分表数据全量合并迁移
基于数据库的增量、持续分库分表合并迁移

在本文的示例中，将使用 MySQL 作为数据源，计划将 store_database 中 sale 开头的表合并导入下游 store_table.sale 表。其中 sale_all 代表未拆分表的剩余所有数据的集合。

MySQL		ProtonBase
Schema	Tables	Schema	Tables
store_database	sale_01	sotre	sale
store_database	sale_02....	sotre	sale
store_database	sale_all	sotre	sale

分表数据冲突检查

迁移中如果涉及合库合表，来自多张分表的数据可能引发主键或唯一索引的数据冲突。因此在迁移之前，需要检查各分表数据的业务特点。

在本示例中：sale_01，sale_02，sale_all 具有相同的表结构如下：

CREATE TABLE `sale_01` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sale_id` varchar(36) NOT NULL,
  `bu_id` bigint NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sid` (`sale_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1;

其中 id 列为主键，sale_id 列为分片键，具有全局唯一性的 UUID。id 列具有自增属性，多个分表范围重复会引发数据冲突。sale_id 可以保证全局满足唯一索引，因此可以本文介绍的操作绕过 id 列。在下游创建 sale 表时移除 id 列的唯一键属性：

BEGIN;
CREATE TABLE public.sale (
  id bigint NOT NULL DEFAULT nextval('"public"."sale_id_seq"'::regclass),
  sale_id character varying(36) NOT NULL,
  bu_id bigint NOT NULL,
  comment character varying(255) NULL,
  CONSTRAINT sale_pkey PRIMARY KEY (sale_id)
) USING hybrid;
COMMIT;

步骤 1：准备数据源

在三个 MySQL 表 sale_01、sale_02、sale_all 中都插入 1000 条类似下面的记录：

INSERT INTO store_database.sale_01 (sale_id, bu_id, comment) VALUES ('87448905-171d-4086-b3a6-c00fc152e591', 9, 'kgtjanwQSgwAEZgCPDZ0');

由于目标表需要提前把 primary key 设置为 sale_id，可以提前在目标库上创建，避免重复数据。（如果表数量太多，也可以由同步任务创建后，暂停任务修改 primary key，然后再进行重新同步。）

BEGIN;
CREATE TABLE public.sale (
  id bigint NOT NULL DEFAULT nextval('"public"."sale_id_seq"'::regclass),
  sale_id character varying(36) NOT NULL,![alt text](../../Downloads/1.png)
  bu_id bigint NOT NULL,
  comment character varying(255) NULL,
  CONSTRAINT sale_pkey PRIMARY KEY (sale_id)
) USING hybrid;
COMMIT;

步骤 2：创建迁移任务

创建同步任务可以参考数据同步快速入门。其中，在映射规则中，输入如下的规则：

case
    -- 设置与 xxx_01 类似的表的规则
    when regexp_match(SOURCE_OBJECT, '^(.*)_[0-9]{2}) then concat('store.', SOURCE_DATABASE,'.', $1 )
    -- 设置与 xxx_all 类似的表的规则
    when regexp_match(SOURCE_OBJECT, '^(.*)_all) then concat('store.', SOURCE_DATABASE,'.', $1 )
else
    -- 设置不匹配的表的规则
    concat('store', '.', SOURCE_DATABASE, '.', SOURCE_OBJECT)
end

Create Teleport Job

步骤 3：启动任务，查看任务状态

启动任务后，观察同步情况。

Initialize Teleport Job

步骤 4：查看任务状态

等待任务完成了补齐全量操作在进行增量的时候，查看数据表的记录数。记录数 300 与源三个表数量一致。此时分库分表的数据同步已经完成。

Check Teleport Job Status

步骤 5：查看增量情况

往源写入如下数据，每个表写入 10 条新数据：

INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('b04feba9-6b8d-4dea-81f9-7f510f53f854', 8, 'HKoslgn1ZN3MgaWiyfPM');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('7340b93b-fa78-433a-9480-e96a9301a8af', 10, 'JF6tkqmHPeTfD3HFi0Qv');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('0cec1058-636f-463b-bf54-7059d2977d4c', 4, 'mn6dteKsbi4wPCm2Ncjd');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('e6ba7789-3932-4c7e-88c2-51afb1f4767e', 6, '91A0vO6s9cU9rz7PcOoh');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('4506567a-b9ea-4679-8330-4e6e728abc5e', 9, 'gsVgmqwkihBvNIG2jUGQ');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('7fd69a81-56d1-4236-ab32-d3e227f46b28', 10, 'G9GpwVpcVob3ASN5qhTC');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('447e0caf-fdcf-47ef-82fe-35cdc676122d', 8, 'GEcsTzKvXsMyHvyEtzke');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('063ad13d-f9eb-470f-94c6-a111a9ae0536', 6, 'KYKoqvrBkje2OxS0vWDd');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('35cfff4c-951c-4733-925d-1b1f5c3d5347', 7, '1lMVwEsQ2eQiFtENs3pu');
INSERT INTO sale_01 (sale_id, bu_id, comment) VALUES ('da212598-7e06-4c21-b761-adb23e866a56', 4, '6ribeeVZoToT88Ta1Mia');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('28aea6e5-e2ed-4bfb-8e32-2f42953d3a38', 9, 'HRB8xNBqJ5eTlWPxluxg');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('1d7fc4c3-48dd-4d4b-baa2-0cca5f272f74', 1, 'FNwN6yrsc6Xe4cGPEpXI');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('4891ee07-d22f-428f-9746-c2199ed5acf3', 10, 'SmWwekZEczqWgaLsi8oC');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('ec0bc160-4057-433b-8b06-885b06e513e0', 10, 'TlofIPIjVk5MexPM5fko');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('e2d93753-2f2e-4b24-8e23-fb27908216e6', 8, 'NHXvATe9nI159OicSnaD');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('ca0c6310-e72d-4fed-8ce4-2e2d960a01f0', 1, 'rfZfNVA2dZ5gtoVmDlPA');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('6e2a37a3-2d01-4415-868d-af493c628f19', 8, 'g75M5eY8Sw1Lvvk9jXNX');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('2ff937e5-f4e3-4098-8d8c-3b831ebec43d', 6, 'eGwZMNZiGXtRmLZAkHy6');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('5d22520c-46c4-418c-8820-0d192ff469a5', 7, 'kvYsqWbQ17cxyzQBe42E');
INSERT INTO sale_02 (sale_id, bu_id, comment) VALUES ('0bcb6412-30c4-4a41-8323-b1d8337c66a7', 2, 'YaDdQp0LXtrSlBybTEZd');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('b5802426-611e-4031-b8a4-8f4aa174444f', 8, 'LYA5iqbvEj1rB2bYN1Xa');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('dee83a85-054c-45ed-b61e-a0def42221d0', 1, 'HngVA9WguibJKaJb29eO');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('27ec5530-c795-4530-b895-15309ebaf9a4', 4, 'lzbmDJPmWDvBomkwF5Xs');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('eebb9a1c-6f52-4452-a79d-9a17eee4721c', 3, 'pW19GxIowFbgHsRNPIDM');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('93bf8fcc-6fbe-446b-9a52-7e579664fa51', 5, 'QG99uwaoqwjPkQ76fuOC');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('753e989b-f557-4557-8ad0-fe563cbb4445', 7, 'foWXXKhFOhSOmyKhV57T');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('4f55e4b4-f573-4429-8c6b-5cf211396b27', 3, 'tNsg4YbFYARjl32wvS66');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('6e11a78d-aab0-4ebc-b129-302680f1b469', 5, 'gIlzCIthmHErzvtrc00Q');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('5237339c-37ad-4742-a344-92243cd655a1', 8, 'BCXLSZj6mMkFYj8bCtku');
INSERT INTO sale_all (sale_id, bu_id, comment) VALUES ('d01ba075-f6de-4ea6-8a68-d405dbd058d7', 6, 'jMPtEl6yYycU8WncRWQT');

Check Teleport Job Result

最佳实践

本文阐述了使用 Teleport 对分库分表进行合并迁移的场景中，Teleport 相关功能的支持和限制，旨在给出一个业务的最佳实践。

Teleport 同步顺序及逻辑

DDL 顺序：Teleport 会在有多个分表同步到 ProtonBase 时，DDL 将会在第一个表进行执行。后续如果有相同名字的表将会直接跳过。
数据写入：Teleport 在 ProtonBase 会使用 insert on conflict 进行操作，根据 pk，如果已经存在 pk，将会进行 update，如果没有存在 pk，那么将会进行 insert。
drop，truncate 等操作：在源的分表上操作，那么同步到目标端，可能会造成全部数据表丢失。delete from 如果条件指定不对或者没有指定，那么也有可能删除所有数据。

跨分表数据在主键或唯一索引冲突处理

来自多张分表的数据可能会引发主键或者唯一索引的数据冲突，这需要结合分表逻辑对每个主键或唯一索引进行检查。我们在此列举主键或唯一索引的三种情况：

分片键：通常来讲，相同的分片键始终会划分到同一张分表之中，因此分片键不会产生数据冲突。
自增主键：每个分表的自增主键会单独计数，因此会出现范围重叠的情况，这需要参照下一节自增主键冲突处理来解决。
其他主键或唯一索引：需要根据业务逻辑判断。如果出现数据冲突，也可预先在下游创建表结构。

自增主键冲突处理

推荐使用以下两种处理方式：

去掉自增主键的主键属性

假设上游分表的表结构如下：

CREATE TABLE `sale_01` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `sale_id` varchar(36) NOT NULL,
  `bu_id` bigint NOT NULL,
  `comment` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sid` (`sale_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1;

如果满足下列条件：

id 列对业务无意义，且不依赖该列的 PRIMARY KEY 属性。
sale_id 列有 UNIQUE KEY 属性，且能保证在所有上游分表间全局唯一。

则可以用以下步骤处理合表时可能由 id 导致的 Duplicate entry '***' for key 'PRIMARY' 问题：

在开始执行全量数据迁移前，在下游数据库创建用于合表迁移的表，但将 id 的 PRIMARY KEY 属性修改为普通索引或者删除该索引。然后保留 sale_id 的唯一索引或者改为 PRIMARY KEY，这样将会有 sale_id 的全局唯一索引。

BEGIN;
CREATE TABLE public.sale (
  id bigint NOT NULL DEFAULT nextval('"public"."sale_id_seq"'::regclass),
  sale_id character varying(36) NOT NULL,
  bu_id bigint NOT NULL,
  comment character varying(255) NULL,
  CONSTRAINT sale_pkey PRIMARY KEY (sale_id)
) USING hybrid;
COMMIT;

使用联合主键

假设上游分表的表结构如下：

CREATE TABLE tbl_multi_pk (
  id bigint(20) NOT NULL,
  groupid bigint(20) NOT NULL,
  content_c3 text,
  PRIMARY KEY (id, groupid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

如果满足下列条件：

业务不依赖 id 的 PRIMARY KEY 属性。
id 与 groupid 的组合能确保全局唯一。
业务能接受将 id 与 groupid 组成联合 PRIMARY KEY。

则可以用以下步骤处理合表时可能由 id 导致的 Duplicate entry '***' for key 'PRIMARY' 问题：

在开始执行全量数据迁移前，在下游数据库创建用于合表迁移的表，但不为 id 指定 PRIMARY KEY 属性，而是将 id 与 groupid 一起组成 PRIMARY KEY。

CREATE TABLE tbl_multi_pk (
  id bigint NOT NULL,
  groupid bigint NOT NULL,
  content_c3 text,
  PRIMARY KEY (id, groupid)
) USING hybrid;

上游 RDS 封装分库分表的处理

上游数据源为 RDS 且使用了其分库分表功能的情况下，MySQL binlog 中的表名在 SQL client 连接时可能并不可见。例如在 UCloud 分布式数据库 UDDB 中，其 binlog 表名可能会多出 _0001 的后缀。这需要根据 binlog 中的表名规律，而不是 SQL client 所见的表名，来配置表的映射规则。

上游分库分表修改

上游全新拆表

上游的现有表 salenew，现有数据1000条，需要将 bu_id 为 1 的数据拆分到 salenew_01 表，bu_id 为 2 的数据拆分到 salenew_02 表，剩下的数据保持在 salenew_all 表中。

 CREATE TABLE salenew(
   id bigint NOT NULL AUTO_INCREMENT,
   sale_id varchar(36) NOT NULL,
   bu_id bigint NOT NULL,
   comment varchar(255) DEFAULT NULL,
   PRIMARY KEY (id),
   UNIQUE KEY sid (sale_id)
 ) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1

目标表中的状态如下：

Destination Table

拆表步骤

修改目标表的索引，保证索引的唯一性

ALTER TABLE salenew DROP CONSTRAINT salenew_pkey;
ALTER TABLE salenew ADD PRIMARY KEY (sale_id);

将 salenew 修改为 salenew_all

ALTER TABLE store_database.salenew RENAME TO store_database.salenew_all;

创建 salenew_01 和 salenew_02

CREATE TABLE store_database.salenew_01 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  sale_id VARCHAR(36) NOT NULL,
  bu_id BIGINT NOT NULL,
  comment VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY sid (sale_id)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1;
 
CREATE TABLE store_database.salenew_02 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  sale_id VARCHAR(36) NOT NULL,
  bu_id BIGINT NOT NULL,
  comment VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY sid (sale_id)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1;

将数据从 salenew_all 复制到 salenew_01 和 salenew_02

INSERT INTO salenew_01 SELECT * FROM salenew_all WHERE bu_id = 1;
INSERT INTO salenew_02 SELECT * FROM salenew_all WHERE bu_id = 2;

将拆分后的数据从 salenew_all 中删除

DELETE FROM salenew_all WHERE bu_id IN (1, 2);

将 salenew_all 重命名回 salenew

-- 确认拆分后的数据
SELECT COUNT(1) FROM salenew_all;
-- 结果: 1000
 
SELECT COUNT(*) FROM salenew;
-- 结果: 0
 
-- 删除原来的 `salenew` 表
DROP TABLE salenew;
 
-- 重命名 `salenew_all` 为 `salenew`
ALTER TABLE salenew_all RENAME TO salenew;

在上游增加分表

如果需要在上游增加新的分表，例如将 bu_id = 3 的数据创建一个 salenew_03 表，推荐按以下顺序执行操作：

创建目标表 salenew_03

CREATE TABLE store_database.salenew_03 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  sale_id VARCHAR(36) NOT NULL,
  bu_id BIGINT NOT NULL,
  comment VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY sid (sale_id)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1;

创建临时表 bak_salenew_03

CREATE TABLE store_database.bak_salenew_03 (
  id BIGINT NOT NULL AUTO_INCREMENT,
  sale_id VARCHAR(36) NOT NULL,
  bu_id BIGINT NOT NULL,
  comment VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY sid (sale_id)
) ENGINE=InnoDB AUTO_INCREMENT=102 DEFAULT CHARSET=latin1;

将 bu_id = 3 的数据从 salenew_all 同步到 bak_salenew_03

INSERT INTO bak_salenew_03 SELECT * FROM salenew_all WHERE bu_id = 3;

从 salenew_all 中删除 bu_id = 3 的数据

DELETE FROM salenew_all WHERE bu_id = 3;

将数据从 bak_salenew_03 同步到 salenew_03

INSERT INTO salenew_03 SELECT * FROM bak_salenew_03;

在上游删除分表

目前如果需要在上游删除分表，需要联系 ProtonBase 技术支持。

以上内容为上游分库分表修改的详细步骤。通过这些步骤，可以有效地对现有表进行拆分、合并以及增删分表操作，确保数据的一致性和完整性。