异地多活是一项系统性工作，包含 web 层、应用服务层、数据层的流量分配和同步。
数据层的双向同步是整个方案基础，CloudCanal 在 MySQL
技术点
双向同步中, 暂时无法完全通过数据层解决的是数据冲突问题，如一个订单同时在两地被修改价格，到底哪个为准，这个具有外部依赖性。
针对这个问题，最有效的解决方案是通过业务流量分配(比如user_id),将同一条数据(或相关数据)写入放在其中一边，更加简单暴力的方式是将具备冲突条件的数据写入完全放在一地。
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:031ac3ea-0169-4442-8c75-ee28fd8cb81a
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:183181e6-d4b4-4df7-867d-6e24b369b070
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:ebf7e61c-95c8-4368-bb45-18f8922c821a
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:9673b112-30c2-483f-bc7d-8eb0cf800146
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:6445ddef-9dd1-458a-8016-9c8c389725bc
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:c66573f2-d7ac-4ad8-86d7-da0c8626dbd9
- 修改数据库引擎，将同步过来的日志写入打上特殊标记(如 MySQL relay_log apply),对向链路识别该标记并决定是否同步
- 同步数据写到对端时, 同一个事务带上一个特定操作，对向链路识别这个操作，决定整个事务是否同步
- 依赖数据库自身提供的防回环机制(比如 MySQL GTID),同步工具做相应动作
CloudCanal 目前在 MySQL 到 MySQL 链路采用了 GTID 方案，一是 MySQL 自带这个防重能力，二是尽量避免做更多操作。
举个 “栗子”
- 本案例 MySQL 数据库放在 上海和 杭州, CloudCanal社区版运行于 上海 ECS
- 登录 CloudCanal 平台
- 数据源管理 -> 添加数据源
- 选择 自建 MySQL ，阿里云 RDS 账号权限不支持链接上设置 GTID_NEXT
- 自建 MySQL 开 binlog 和 GTID
server-id = 2
log-bin = mysql-bin
gtid_mode = on
enforce_gtid_consistency = on
-
确认 MySQL GTID 已经生效(请写几条测试数据)
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:2c1d9686-8dea-4046-879b-efe951a74f30[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:df815aa8-8ddd-4619-a370-e0968414a2ec
-
任务管理-> 新建任务
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:28b4935c-c5b9-4fc6-acb4-d9bf320b3924[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:6c950d16-0948-4ffb-9757-65c4471de530
- 源端和目标端选择数据源, 并分别点击 测试连接 按钮以测试数据库连通性和获取 schema 级别元信息
- 选择源端和目标端 schema
- 点击 下一步
- 选择 数据同步，并且勾选 全量数据初始化
- 规格可以根据任务重要度以及部署机器的内存容量合理选择，一般 2GB 内存规格即可
- 勾选 DDL 同步
- 点击 下一步
- 表、列映射裁剪…此处省略
- 对任务内容进行确认 ，置灰 自动启动任务 按钮以便调参数
- 点击 确认创建
- 任务详情 -> 参数设置
- 设置目标数据源配置 deCycle , enableTransaction , 源端数据源配置 gtidMode 参数为 true
-
生效配置并启动
-
任务管理-> 新建任务
- 源端和目标端选择数据源（ 请和正向任务所选数据源对调）, 并分别点击 测试连接 按钮以测试数据库连通性和获取 schema 级别元信息
- 选择源端和目标端 schema
- 点击 下一步
- 选择 数据同步，并去除 全量数据初始化
- 勾选 DDL 同步
- 点击 下一步
- 表、列映射裁剪…此处省略
- 对任务内容进行确认 ，置灰 自动启动任务 按钮以便调参数
- 点击 确认创建
- 任务详情 -> 参数设置
- 设置目标数据源配置 deCycle , enableTransaction , 源端数据源配置 gtidMode 参数为 true
- 生效配置并启动
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:58478977-4bbb-4811-997e-0ad87bd7a87c
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:1b40a3ee-c0ae-4ea2-b62d-04b7cdf6eb1c
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:ed349068-f0ae-4778-ac0c-99e83cb3da87
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:a1d476bf-032e-4bc4-9bd9-14322e8440e2
* 给源端数据库中 kbs_question 表和 目标数据库 kbs_article 表造 IUD 负载 。
* 停止测试负载
* 正向和反向任务状况
- 重跑校验任务，数据一致
FAQ
如果对数据不做映射、裁剪、转换、冲突策略(比如略过冲突数据)等动作，使用 MySQL 双 Master 复制是一个选择。
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:2c1d0b63-c017-4cb6-813b-78553124c0d3
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:b8fe96d7-e42d-4942-ab0f-6596516a26a5
综合这两方面，第三方有商业支持或开源软件可能会表现出更好的应变性。毕竟改 MySQL 代码不容易。
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:d918b104-cf5c-4563-a677-27025d984a59
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:b1d5d062-2013-46cd-9a0d-29f9e1a3027f
对于单 Leader 写入机制，连业务层面设计不冲突写入策略的机会可能都没有。Multi-Master 涉及更加复杂的算法。
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:40d37ad0-b94c-45f6-a674-60cc08d7b7f0
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:aa6d7be4-de97-45c2-a357-e8b582f3f57a
目前 CloudCanal 方案只实现了 MySQL->MySQL ,并且方案上依赖 GTID ,对于 ORACLE ,PG ,SQLSERVER 等关系型数据库，可能需要实现通用方案(主要是事务方案), 将他们同构或者异构链接起来。
本文简单介绍了如何使用 CloudCanal 构建 MySQL->MySQL 双向同步链路，如果各位有需求，可以尝试使用下我们的免费社区版体验。
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:e648132c-b687-4b09-bdd6-626fa8c526f0
[En]
[TencentCloudSDKException] code:FailedOperation.ServiceIsolate message:service is stopped due to arrears, please recharge your account in Tencent Cloud requestId:75757904-f14d-4cf2-921c-3193cca8ee57
更多精彩
社区快讯
- 我们创建 CloudCanal 微信粉丝群啦，在里面，你可以得到最新版本发布信息和资源链接，你能看到其他用户一手评测、使用情况，你更能得到热情的问题解答，当然你还可以给我们提需求和问题。快快加入吧。
- 扫描下方二维码，添加我们小助手微信suhuayue001拉您进群，接头语(“CloudCanal yyds”)
Original: https://www.cnblogs.com/clougence/p/15223589.html
Author: clougence
Title: Mysql异地多活数据双向同步-CloudCanal实战
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/561983/
转载文章受原作者版权保护。转载请注明原作者出处！