|
|
|
|
公众号矩阵

将数据从MySQL复制到Redshift的四种方式

如今,许多公司都转为使用Amazon的Redshift,来补齐MySQL在分析查询上的短板。为此,本文将和您讨论将数据从MySQL复制到Redshift的四种实用方法。

作者:陈峻来源:51CTO|2021-07-15 09:00

【51CTO.com快译】目前,MySQL已凭借着其强大的功能、灵活且可靠的服务,成为了世界上最受欢迎的开源云端数据库之一。每天都有数以万计的公司,使用MySQL来为其基于Web的应用和服务提供支持。

但是当涉及到数据分析时,情况就不同了。即使是最小的分析查询,MySQL也会很快陷入困境,甚至会让您的整个应用程序面临崩溃的风险。而Redshift则能够处理PB级的数据,并在较短的时间内提供各种分析。这就是为什么如今许多公司都转为使用Amazon的Redshift,来补齐MySQL短板的原因。也就是说,它们通过将MySQL和Redshift配合使用,以消除在运行查询时产生导致数据库崩溃的风险。对此,我们需要将MySQL里的数据设法复制到Redshift中。下面让我们来具体讨论四种实用的实现方法。

为何要将数据从MySQL复制到Redshift?

对于提供Web应用的公司而言,选择将数据从MySQL复制到Redshift,不但是为了便于数据分析,而且可以获得如下方面的优势:

  • 保持应用的性能。正如我们已经提到的,在生产环境的MySQL数据库上运行分析查询,不但会对其性能产生严重影响,而且可能导致其崩溃。鉴于分析查询非常耗费资源,我们需要为其分配专用的计算力。
  • 分析所有数据。作为OLTP数据库,MySQL是专为诸如:客户记录和财务数据等事务性数据而设计的。如果您希望从整个数据集(包括非事务类型)中获得有关数据的洞见,则需要使用Redshift从同一处捕获和分析您的所有数据。
  • 更快的分析。Redshift属于大规模并行处理 (massively parallel processing,MPP)类数据仓库。这意味着,它可以在较短的时间内处理大量的数据。而作为对比,MySQL则难以为大型分析查询扩展出足够的计算力。即便是MySQL的副本数据库,也很难达到与Redshift同等的速度。
  • 可扩展性。MySQL主要是在单个节点实例上运行,而并非分布式的云基础架构处。因此,超出单个节点的扩展往往需要花费时间,并且需要用到诸如分片、或主节点设置等资源密集型的技术。这些都会进一步减慢数据库的速度与效率。

将MySQL复制到Redshift的四种方法

许多公司都会通过如下四种方法,来实现从MySQL到Redshift的数据复制:

一、导入与导出

将数据复制到Redshift的最简单方法,莫过于导出整个MySQL的数据。不过,这也是效率最低的方法。它包含了:导出、转换、导入三个步骤。

导出

我们可以使用MySQL的经典MySQLdump命令,按照如下格式导出数据:

  1. Java 
  2. $ MySQLdump -h yourmysqlhost -u user mydatabase mytable1 mytable2 --result-file dump.sql 

由于该命令的输出是MySQL的SQL语句,而无法运行在Redshift上,因此您必须将该语句转换为适合Redshift导入的格式。

转换

为获得最佳的上传性能,您需要将SQL语句转换为TSV(tab-separated values,制表符分隔值)的格式。为此,您可以使用Redshift的COPY命令,将数据文件批量上传到Amazon S3中的Redshift表中。下面展示了MySQLdump中的一行数据:

  1. Java 
  2. mysql> INSERT INTO `users` (`id`, `firstname`, `lastname`, `age`) VALUES (1923, ‘John’, ‘Smith’, 34),(1925,’Tommy’,’King’); 

请注意,这些值都是被制表符(\t)分隔开来的。而且,如果MySQL和Redshift支持不同的数据列和类型,您可能还需要将数据值转换为与Redshift相兼容的类型。例如,DATE值“0000-00-00”在MySQL中是有效的,但是在Redshift中会被抛出错误。那么,您就必须将该值转换为可被Redshift接受的格式--“0001-01-01”。

导入

您只需要运行如下COPY命令,便可完成向Redshift的导入工作:

  1. Java 
  2. COPY users 
  3. FROM 's3://my_s3_bucket/unload-folder/users_' credentials   
  4. 'aws_access_key_id=your_access_key;aws_secret_access_key=your_secret_key'

导入导出的缺点

导入导出的数据复制方法虽然简单,但是它并不适合频繁更新的场景。例如,如果通过100 Mbps的网络从MySQL导出18 GB的数据,则需要大约30分钟;而将这些数据导入Redshift也还需要30分钟。一旦网络连接出现了中断,则该过程还需重做一遍。

二、增量SELECT和COPY

SELECT和COPY方法仅作用于更新那些自上次更新以来,已变更的记录。与导入导出整个数据集相比,该方法花费的时间和带宽要少得多,因此能够更频繁地将MySQL与Redshift进行同步。不过,您的MySQL表必须满足如下两个条件,方可使用该增量方法:

  • 数据表必须有一个updated_at列,而且在每次变更行时,都会更新其时间戳。
  • 数据表必须有一到多个唯一键。

和导入导出一样,该增量方法也分三个步骤:

1. 导出

由于增量SELECT仅导出自上次更新以来已变更的行,因此您需要在MySQL上运行如下SELECT查询命令:

  1. Java 
  2. SELECT * FROM users WHERE updated_at >= ‘2016-08-12 20:00:00’; 

然后将结果保存到文件中,以备后续的转换。

2. 转换

与导入导出方法相同,该步骤是将MySQL数据转换为Redshift支持的TSV格式。

3. 导入

至此,MySQL的TSV文件中已包含了被更新的行和新插入的行。不过,为了避免更新的行被直接复制过去,而出现重复行,您不可简单、直接地运行COPY命令,而应当使用如下DELSERT(delete + insert)的方式:

  • 在Redshift上创建一个与目标表具有相同定义的临时表。
  • 运行COPY命令将数据上传到临时表中。
  • 使用如下命令,从目标表中删除那些已在临时表中存在的行。
  1. Java 
  2. DELETE FROM users USING users_staging s WHERE users.id = s.id; 
  • 最后,将剩下的数据行,从临​​时表插入到目标表中:
  1. Java 
  2. INSERT INTO users (id, firstname, lastname, updated_at) SELECT id, firstname, lastname, updated_at FROM users_staging s; 

SELECT和COPY的缺点

虽然增量SELECT和COPY比导入导出更为有效,但它也有着自身的局限性。其主要问题在于:从MySQL表中删除的数据行,会无限期地保留在Redshift中。因此,如果您想在从MySQL中清除旧数据的同时,保留Redshift上的历史数据,那么就无所谓。否则,您就需要在数据分析的过程中,去额外删除Redshift中的数据行。

此方法的另一个缺点是,它不会去复制数据表在结构模式上(schema)的变更。也就是说,当您在MySQL表中添加或删除数据列时,则需要手动对Redshift数据表进行相应的变更。

此外,那些被用于从MySQL表中提取更新数据行的查询,也会影响MySQL数据库的整体性能。

三、使用Binlog变更数据的捕获

变更数据捕获 (Change data capture,CDC)技术,可以捕获任何在MySQL中发生的数据变更,并将其应用到目标Redshift表上。与增量SELECT和COPY方法的类似,它只导入变更的数据,而非整个数据库;而与增量方法不同的是,CDC允许您实现从MySQL到Redshift的真正复制。

要对MySQL数据库使用CDC方法,您必须使用binlog(二进制变更日志)。Binlog允许您以数据流的形式捕获发生了变更的数据,进而实现近乎实时的复制。

Binlog不仅能够捕获数据的变更(如:插入、更新、删除),而且可以捕获数据表在结构模式上的变更(例如:添加、删除某列)。此外,它还能确保从MySQL删除的数据行也在Redshift中被删除。

当您将CDC与binlog结合使用时,您实际上是通过编写一个应用程序,实现了流数据从MySQL读取、转换和导入至Redshift的过程。具体而言,您可以使用一个名为mysql-replication-listener的开源库来实现。这个C++库提供了一个流式API,在MySQL的binlog处实时读取数据。当然,其他高级语言,包括Ruby的kodama和Python的python-mysql-replication也提供了类似的高级API。其具体实现过程为:

1. 设置

首先,请参照MySQL的如下配置参数设置,来启用binlog:

  1. Java 
  2. log_bin = /file_path/mysql-bin.log 

其中,参数binlog_format设置了binlog事件存储在binlog文件中的格式。它支持:语句、混合和行,三种格式。其中,语句格式会将查询按照原样保存到binlog文件中(例如:UPDATE SET firstname=’Tom’ WHERE id=293;)。这样虽然节省了binlog文件的大小,但是在复制过程中,可能会出现问题。

因此,对Redshift的复制场景而言,请使用行的格式。该格式会将变更的值,保存在binlog文件中。它虽然增加了binlog文件的大小,但是可以确保MySQL与Redshift之间数据的一致性。

log_bin设置了存储binlog文件的路径。expire_logs_days确定了binlog文件被保留的天数。我们建议将binlog文件保留数天,以确保有时间解决复制期间出现的任何问题。而replicate-wild-do-table参数则指定了待复制的表。也就是说,只有那些被指定的表才能进入binlog文件。

如果您使用MySQL的从服务器(slave server)作为复制源的话,则需要将log-slave-updates设置为TRUE。否则,在主复制服务器上所做的数据变更,将不会被记录在binlog中。

此外,您的MySQL帐户还需要具有以下权限,方可执行与复制相关任务:

  • 复制从站
  • 选择
  • 重新加载
  • 复制客户端
  • 锁表

2. 导出和转换

当您使用binlog时,需要导出的实际上是MySQL的各个binlog文件的实时数据流。而binlog数据的具体交付方式,则取决于您使用到的API。例如,对于Kodama而言,binlog数据会根据binlog事件流的形式予以交付。也就是说,Kodama允许您为不同的事件类型(插入、更新、删除、变更表、创建表等)注册事件处理句柄(handlers)。应用程序在接收到binlog事件后,会生成一个用于将数据变更导入Redshift,或包含表结构模式变更的输出。

与其他复制方法不同,binlog变更方式需要您专门处理那些已被删除的事件,以维持Redshift的上传性能

3. 导入

为了将binlog数据流上传并导入Redshift,我们需要借用在增量SELECT和COPY方法中提及的DELSERT导入技术。

Binlog的缺点

Binlog方法虽然能够满足从MySQL到Redshift的数据复制需求,但是它需要您花时间去开发CDC应用。也就是说,除了上面提到的数据流之外,您还必须构建:

  • 事务管理。跟踪数据流的性能,以免应用程序在读取binlog的数据时出现报错和中止,并能够从上一次中断处继续。
  • 数据缓冲和重试。为了避免Redshift在应用程序发送数据时不可用,进而导致数据丢失或重复,您应当设法让应用程序缓冲未发送的数据,直到Redshift集群重新上线。
  • 支持表结构模式的变更。Binlog中的表结构模式的变更事件(如:变更、添加、删除表)往往是作为MySQL原生的SQL语句出现的。不过,它不能直接运行在Redshift上,而需要设法将此类变更从MySQL语句,转换为相应的Redshift语句。

四、使用ETL即服务

借助ETL工具,您可以近乎实时地将数据复制到Redshift中。与CDC方法不同,此类工具可以管理整个复制过程,并自动将MySQL数据的类型,映射为Redshift使用的格式。您甚至可以同时将多个MySQL数据库(以及其他类型的数据库)同步到Redshift上。由于其设置过程非常简便,此处就不再赘述了。

小结

综上所述,MySQL和Redshift的联合使用,为您的BI需求提供了简单却又强大的解决方案。上文列举的四种将数据从MySQL复制到Redshift的方法,既从简单到复杂,又从非常缓慢到接近实时。具体该如何选择,请您参考如下三方面因素:

  • 复制频率
  • MySQL数据集的大小
  • 可用的开发资源

其中,最快、最可靠的复制方法当属:利用了MySQL binlog的变更数据捕获 (CDC)。不过其缺点是需要开发人员花时间去构建和维护应用程序。因此,您需要根据实际业务目标和数据分析需求,来做出明智的决定。

原文标题:MySQL to Redshift: 4 Ways to Replicate Your Data,作者:Ben Putano

【51CTO译稿,合作站点转载请注明原文译者和出处为51CTO.com】

【编辑推荐】

  1. 鸿蒙官方战略合作共建——HarmonyOS技术社区
  2. MySQL中的SQL Mode及其作用
  3. 数据库中间件MyCat分库分表教程
  4. VB6.0连接Access、Sql-Server数据库制作多权限用户的管理系统
  5. 像查询数据库一样查询你的 Linux 操作系统信息
  6. MySQL 中的共享表空间与独立表空间,用哪个好呢?
【责任编辑:华轩 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

带你轻松入门 RabbitMQ

带你轻松入门 RabbitMQ

轻松入门RabbitMQ
共4章 | loong576

21人订阅学习

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

12人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

41人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微