开发者社区> 耿江涛> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Mysql5.7使用DTS增量同步数据到MaxCompute

简介: 本文介绍了使用适应增量同步Mysql5.7数据同步到MaxCompute同步方式,主要过程为设置mysql5.7的binlog,以及使用DTS同步Mysql同步所有数据之前的全量数据操作,以及增量数据同步的配置,以及最后整合最后数据之前的全部数据。
+关注继续查看

背景:一些客户反馈,增量同步数据到MaxCompute按照全天的数据做增量数据同步数据量太大,且不使用按天的增量同步数据,进行在MaxCompute上进行数据处理得出增量数据对于delete的相关数据不能做到很好的支持,在次给大家一个对增量数据同步的方案使用DTS做增量同步数据到MaxCompute,数据源为ECS上自建的mysql5.7。

一、为自建MySQL创建账号并设置

1.1登陆自建Mysql数据库

image.png

1.2创建mysql数据库中用于数据迁移/同步的账号

CREATE USER 'dtsmigration'@'%' IDENTIFIED BY 'Dts123456';

说明:

  • username:待创建的账号。
  • host:允许该账号登录的主机,如果允许该账号从任意主机登录数据库,可以使用百分号(%)。
  • password:账号的密码。

1.3对账号进行授权操作

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;




说明:

  • privileges:授予该账号的操作权限,如SELECT、INSERT、UPDATE等,如果要授予该账号所有权限,则使用ALL。
  • databasename:数据库名。如果要授予该账号具备所有数据库的操作权限,则使用星号(*)。
  • tablename:表名。如果要授予该账号具备所有表的操作权限,则使用星号(*)。
  • username:待授权的账号。
  • host:允许该账号登录的主机,如果允许该账号从任意主机登录,则使用百分号(%)。
  • WITH GRANT OPTION:授予该账号使用GRANT命令的权限,该参数为可选。


如果要给账户赋予所有数据库和表的权限,并容许从任意主机登陆数据库

GRANT ALL ON *.* TO 'dtsmigration'@'%';

1.4开启并设置自建Mysql数据库binlog

到指定目录下找到该文件
image.png

a.使用vim命令,修改配置文件my.cnf中的如下参数

log_bin=mysql_bin
binlog_format=row
server_id=2 //设置大于1的整数
binlog_row_image=full //当自建MySQL的版本大于5.6时,则必须设置该项。


image.png

b.修改完成后,重启Mysql进程。

image.png

service mysqld restart

image.png

二、同步过程介绍

2.1结构初始化

DTS将源库中待同步表的结构定义信息同步至MaxCompute中,初始化时DTS会为表名增加_base后缀。例如源表为customer,那么MaxCompute中的表即为customer_base。

2.2全量数据初始化

DTS将源库中待同步表的存量数据,全部同步至MaxCompute中的目标表名_base表中(例如从源库的customer表同步至MaxCompute的customer_base表),作为后续增量同步数据的基线数据。

2.3增量数据同步

DTS在MaxCompute中创建一个增量日志表,表名为同步的目标表名_log,例如customer_log,然后将源库产生的增量数据实时同步到该表中。

三、增量同步实践

3.1购买DTS同步


image.png

3.2查看购买的DTS同步,点击配置同步链路

image.png

3.3配置对应的数据源和相应的MaxCompute项目


image.png

3.4点击授予权限的同步账号操作

image.png

3.5选择对应的增量同步数据的同步实践,并选择需要同步的表

image.png


3.6同步配置预检查

image.png

3.7查询同步的全量数据

image.png

3.8查看同步成功的增量数据分区user_log

image.png

3.9查看增量数据同步的数据

image.png


元数据的字段介绍

字段 说明
record_id 增量日志的记录id,为该日志唯一标识。
说明
- id的值唯一且递增。
- 如果增量日志的操作类型为UPDATE,那么增量更新会被拆分成两条记录,且record_id的值相同。
operation_flag 操作类型,取值:
- I:INSERT操作。
- D:DELETE操作。
- U:UPDATE操作。
utc_timestamp 操作时间戳,即binlog的时间戳(UTC 时间)。
before_flag 所有列的值是否为更新前的值,取值:Y或N。
after_flag 所有列的值是否为更新后的值,取值:Y或N。

四、根据时间点位,整合该时间点位之前的全量数据

4.1建立全量数据表

CREATE TABLE IF NOT EXISTS maxcomputeone_dev.user_all(uid BIGINT,uname STRING,deptno BIGINT,gender STRING,optime DATETIME,record_id BIGINT,operation_flag STRING,utc_timestamp BIGINT,before_flag STRING,after_flag STRING);

4.2查看增量数据最后同步的点位,最后整合全量数据到user_all

image.png


合并语句

set odps.sql.allow.fullscan=true;
insert overwrite table user_all
select uid,
       uname,
       deptno,
       gender,
       optime
  from(
select row_number() over(partition by t.uid
 order by record_id desc, after_flag desc) as record_num, record_id, operation_flag, after_flag, uid, uname, deptno,gender,optime
  from(
select incr.record_id, incr.operation_flag, incr.after_flag, incr.uid, incr.uname,incr.deptno,incr.gender,incr.optime
  from user_log incr
 where utc_timestamp <= 1585107804
 union all
select 0 as record_id, 'I' as operation_flag, 'Y' as after_flag, base.uid, base.uname,base.deptno,base.gender,base.optime
  from user_base base) t) gt
where record_num=1 
  and after_flag='Y';

欢迎加入“MaxCompute开发者社区2群”,点击链接申请加入或扫描二维码
https://h5.dingtalk.com/invite-page/index.html?bizSource=____source____&corpId=dingb682fb31ec15e09f35c2f4657eb6378f&inviterUid=E3F28CD2308408A8&encodeDeptId=0054DC2B53AFE745
image

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云 MaxCompute 2020-8 月刊
MaxCompute商业化发布数据备份恢复和数据科学Mars两项重要功能,同时公测发布MaxCompute查询加速。秒级恢复误删除数据,分布式加速 Python 数据科学栈,自动识别短查询作业并加速处理,满足报表分析、即席查询场景的使用要求尽在8月刊。
9060 0
阿里云 MaxCompute 2020-6 月刊
MaxCompute备份与恢复功能(公测)发布,提供持续备份用户修改/删除历史数据,支持快速恢复,持续保护数据安全。 该功能适用于广泛的企业级客户,尤其适用于对数据保护有强需求、担心数据误删除以及担心数据被恶意删除的客户。
9321 0
阿里云 MaxCompute 2020-5 月刊
MaxCompute迁移工具MMA2.0新版本发布,MMA2.0不仅在技术架构上做了重构,在功能上也有重大升级,提升了迁移效率和运行稳定性的同时,在操作体验上也更加自动化和智能化。更多5月产品动态,欢迎阅读MaxCompute5月刊。
550 0
阿里云 MaxCompute 2020-4 月刊
4月MaxCompute审计日志发布,可通过历史事件及明细查询、实时行为事件分析,满足您实时审计、问题回溯分析等需求。同时,MaxCompute在支持实时消费监控告警的基础上新发布支持对按量付费单个SQL作业的消费进行控制,帮您更好的监控消费。更多4月的新功能与新解决方案,欢迎阅读4月刊。
1067 0
阿里云 MaxCompute 2020-3 月刊
MaxCompute3月新发功能,新增文档,精彩直播回放,尽在 MaxCompute 3月刊。
4639 0
阿里云 MaxCompute 2020-2 月刊
MaxCompute2020年2月刊为您带来Python UDF 支持Python 3 ,MaxCompute存储格式全面升级AliORC等九项最新发布功能。欢迎阅读。
367 0
阿里云 MaxCompute 2020-1 月刊
您好,MaxCompute 2020.1月刊如期而至,在疫情肆虐的日子里,祝福每一位开发者,平安健康,一切顺利。
1338 0
阿里云 MaxCompute 2019-12 月刊
2019年12月刊,您将了解到 MaxCompute推出更加灵活的预付费资源模型-非预留计算资源,让您在成本不变的情况下使用更多弹性的计算资源;同时,MaxCompute 支持使用开源地理空间函数,更多详情请阅读本月刊。
2604 0
阿里云 MaxCompute 2019-12 月刊
2019年12月刊中,您将了解到 MaxCompute推出更加灵活的预付费资源模型-非预留计算资源,让您在成本不变的情况下使用更多弹性的计算资源;同时,MaxCompute 支持使用开源地理空间函数,更多详情请阅读本月刊。
8052 0
阿里云 MaxCompute 2019-11 月刊
Tableau官方支持MaxCompute数据源,MaxCompute提供了对SQL查询任务结果的动态脱敏功能, 数据迁移工具全新发布等精彩内容尽在 11月刊,欢迎阅读。
10666 0
+关注
14
文章
0
问答
来源圈子
更多
MaxCompute(原ODPS)是一项面向分析的大数据计算服务,它以Serverless架构提供快速、全托管的在线数据仓库服务,消除传统数据平台在资源扩展性和弹性方面的限制,最小化用户运维投入,使您经济并高效的分析处理海量数据。
+ 订阅
相关文档: MaxCompute
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载