麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
691
2023-07-01
为什么将数据从MySQL复制到Redshift
为什么将数据从 MySQL 复制到 Redshift
许多使用 MySQL 支持其 Web 应用程序的公司选择 Redshift 进行数据分析。您也应该这样做的原因有几个:
保持应用程序性能。正如我们已经提到的,在生产 MySQL 数据库上运行分析查询可能对其性能产生严重影响。它甚至可能导致它崩溃。分析查询非常耗费资源,需要专用的计算能力。分析您的所有数据。MySQL 的设计重点在于交易数据,因为它是一种 OLTP(在线交易处理)数据库,它的用途包括客户记录和财务数据等。但是,您希望从整个数据集(包括非交易类型)中获得洞察力。您可以使用 Redshift 在一处捕获和分析您的所有数据。更快的分析。Redshift 是一个大规模并行处理 (MPP) 数据仓库,这意味着它可以在很短的时间内处理大量数据。另一方面,MySQL在满足大规模现代分析查询所需的计算能力时存在困难。即使是 MySQL 副本数据库也很难达到与 Redshift 相同的速度。可扩展性。MySQL是为单节点实例而设计,而不是为现代分布式云基础架构而设计。因此,超出单个节点的扩展需要耗费大量时间和资源,需要使用像分片或主节点设置等技术。所有这些都会进一步减慢数据库的速度。
将 MySQL 复制到 Redshift 的四种方法
为了满足分析需求,许多公司将数据从 MySQL 复制到 Redshift,因为 MySQL 存在固有的弱点。有4种方法可以实现这一点:
进出口增量选择和复制使用 binlog 更改数据捕获 (CDC)ETL
1. 进出口
复制到 Redshift 的最简单方法是导出整个 MySQL 数据。然而,这也是效率最低的方法。共有三个步骤:
出口转变进口
出口
首先,使用 MySQL 的mysqldump命令导出数据。一个典型的mysqldump命令如下所示:
java:
$ mysqldump -h yourmysqlhost -u user mydatabase mytable1 mytable2 --result-file dump.sql登录后复制
此命令的输出是您的 MySQL SQL 语句。您不能按原样在 Redshift 上运行 SQL — 您必须将语句转换为适合 Redshift 导入的格式。
转变
为了使上传性能达到最佳状态,请将您的 SQL 语句转换成TSV(即以制表符分隔的值)格式。您可以使用 Redshift COPY 命令执行此操作。
COPY 命令将您的 SQL 语句转换为 TSV 格式。接下来,批量将文件上传到 Redshift 表中的 Amazon S3 中。例如,MySQL 转储中的一行数据如下所示:
java:
mysql> INSERT INTO `users` (`id`, `firstname`, `lastname`, `age`) VALUES (1923, ‘John’, ‘Smith’, 34),(1925,’Tommy’,’King’);登录后复制
使用COPY,它会变成这样:
1923年 | JOHN | SMITH |
---|---|---|
1925年 | Tmmy | King |
请注意,值由制表符分隔(\t)。
您可能还必须将数据值转换为与 Redshift 兼容。这是因为 MySQL 和 Redshift 支持不同的列和数据类型。
例如,DATE 值“0000-00-00”在 MySQL 中是有效的,但在 Redshift 中会抛出错误。您必须将该值转换为可接受的 Redshift 格式,例如“0001-01-01”。
进口
将 MySQL 语句转换后,最终步骤是将其从 S3 导入到 Redshift。为此,只需运行 COPY 命令:
java:
COPY usersFROM 's3://my_s3_bucket/unload-folder/users_' credentials 'aws_access_key_id=your_access_key;aws_secret_access_key=your_secret_key';登录后复制
进出口的弊端
尽管导入和导出是复制到 Redshift 的最简单方法,但它并不适合频繁更新。
大约需要30分钟,就能够通过100 Mbps的网络从MySQL导出18GB的数据。将该数据导入 Redshift 还需要 30 分钟。这假设您在导入或导出期间遇到零连接问题,这将迫使您重新开始该过程。
将 MySQL 复制到 Redshift 的更有效方法是增量 SELECT 和 COPY。
2.增量SELECT和COPY
如果导入和导出对于您的需求来说太慢,增量 SELECT 和 COPY 可能是您的答案。
SELECT 和 COPY 方法仅更新自上次更新以来已更改的记录。与导入和导出整个数据集相比,这花费的时间和带宽要少得多。SELECT 和 COPY 使您能够更频繁地同步 MySQL 和 Redshift。
要使用增量 SELECT 和 COPY,您的 MySQL 表必须满足几个条件:
表必须有一个updated_at列,每次更改行时都会更新其时间戳。 表必须有一个或多个唯一键。
和导入导出一样,这个方法也分三步:
1. 出口
增量 SELECT 仅导出自上次更新以来已更改的行。您在 MySQL 上运行的 SELECT 查询如下所示:
java:
SELECT * FROM users WHERE updated_at >= ‘2016-08-12 20:00:00’;登录后复制
将结果保存到文件以进行转换。
2. 转型
此转换步骤与导入导出方法相同。将 MySQL 数据转换为 Redshift 的 TSV 格式。
3. 进口
您的MySQL TSV文件现在包括更新过的行和新插入的行。对于目标 Redshift 表,您必须采取其他措施而不能直接运行COPY命令。这将导致更新的行被复制。
为避免重复行,请使用 DELSERT(删除 + 插入)技术:
在 Redshift 上创建一个与目标表具有相同定义的临时表。运行 COPY 命令将数据上传到临时表。从目标表中删除临时表中也存在的行。它看起来像这样:java:DELETE FROM users USING users_staging s WHERE users.id = s.id;登录后复制id表的唯一键在哪里。最后,将行从临时表插入到目标表:
java:
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 中删除的行会在数据分析过程中引起严重的头痛。
这种方法的另一个缺点是它不复制表模式更改。如果在MySQL表中添加或删除列,则需要手动更新Redshift表以进行相应更改。
最后,用于从 MySQL 表中提取更新行的查询会影响 MySQL 数据库的性能。
如果这些缺点中的任何一个是破坏者,那么下一个方法适合您。
3. 使用 Binlog 更改数据捕获
更改数据捕获 (CDC) 是一种技术,可捕获对 MySQL 中的数据所做的更改并将其应用于目标 Redshift 表。类似增量选择和复制,它只导入已更改数据,而非整个数据库。
然而,与增量 SELECT 和 COPY 不同,CDC 允许您实现 MySQL 到 Redshift 的真正复制。
为了使用CDC方法对MySQL数据库进行操作,必须启用二进制日志(binlog)。使用Binlog,您可以以流的形式记录数据的更改,从而实现接近实时的复制。
除了能捕获数据的变化(插入、更新和删除),Binlog 还能够记录表结构的变化,例如添加或删除列。它确保 Redshift 中已经删除的行也在 MySQL 中删除。
Binlog 入门
当您将 CDC 与 binlog 结合使用时,您实际上是在编写一个应用程序,该应用程序将流数据从 MySQL 读取、转换和导入到 Redshift。
要执行此操作,您可使用开源库 mysql-replication-listener。通过使用这个 C++ 库,我们可以实时从 MySQL binlog 中读取数据,而且支持流式 API。高级 API 也可用于多种语言:kodama (Ruby) 和python-mysql-replication (Python)。
1. 设置
首先,设置 MySQL 配置参数以启用 binlog。以下是binlog相关参数列表:
java:
log_bin = /file_path/mysql-bin.log登录后复制
Binlog_format参数的设置影响着Binlog事件以何种方式存储在Binlog文件中的格式。支持 3 种格式:语句、混合和行。
语句格式将查询按原样保存在 binlog 文件中(例如UPDATE SET firstname=’Tom’ WHERE id=293;)。尽管它减小了 binlog 文件的大小,但在用于复制时却存在潜在问题。
要复制到 Redshift,请使用行格式。
行格式将更改的值保存在 binlog 文件中。它增加了 binlog 文件大小,但可确保 MySQL 和 Amazon Redshift 之间的数据一致性。log_bin设置存储binlog文件的路径。binlog file retention period is determined by expire_logs_days.。
指定要复制的表需要在replicate-wild-do-table参数中设置。只有那些指定的表才能进入 binlog 文件。
我们建议将 binlog 文件保留几天。这可确保您有时间解决复制过程中出现的任何问题。
如果您使用 MySQL 复制从服务器作为源,则将 指定log-slave-updates为 TRUE很重要。否则,在复制主服务器上所做的数据更改将不会记录在 binlog 中。
此外,您的 MySQL 帐户需要具有以下权限才能执行复制相关任务:
复制从站选择重新加载复制客户端锁表
2. 导出和转换
使用 binlog 时,实际上“export”的是 MySQL binlog 文件的实时数据流。binlog 数据的交付方式取决于您使用的 API。
例如,对于 Kodama,binlog 数据以 binlog 事件流的形式交付。
Kodama 允许您为不同的事件类型(插入、更新、删除、更改表、创建表等)注册事件处理程序。您的应用程序将接收二进制日志事件。输出将被生成,以便用于针对 Redshift 导入的数据更改或者表结构更改。
数据更改导入类似于我们其他复制方法的转换步骤。然而,与其他的不同,binlog 允许您处理已删除的事件。您需要专门处理已删除的事件以维护Redshift 上传性能。
3. 进口
最后,是时候导入您的 binlog 数据流了。
问题是 Redshift 没有蒸汽上传功能。使用我们在增量 SELECT 和 COPY 方法中概述的 DELSERT 导入技术。
Binlog 的缺点
虽然Binlog是从MySQL复制到Redshift的理想方法,但它仍存在一些不足之处。构建您的 CDC 应用程序需要认真的开发工作。
除了我们上面描述的数据流之外,您还必须构建:
交易管理。您应该跟踪数据流性能,以避免错误导致您的应用程序停止读取二进制日志数据。事务管理确保您可以从上次中断的地方继续。数据缓冲和重试。当数据被发送时,Redshift 可能会出现不可用的情况。您的应用程序需要缓冲未发送的数据,直到 Redshift 集群重新联机。如果此步骤操作不当,可能会导致数据丢失或重复数据。表模式更改支持。表模式更改二进制日志事件(更改/添加/删除表)作为本机 MySQL SQL 语句出现,它不会按原样在 Redshift 上运行。为了使表架构更改得到支持,你需要将 MySQL语句转换成对应的Amazon Redshift语句。
4. 使用 ETL 即服务
借助 ETL 工具,您可以近乎实时地将数据复制到 Redshift。
与 CDC 方法不同,此类工具可以管理整个复制过程并自动将 MySQL 数据类型映射为 Redshift 使用的格式,因此您不必这样做。您可以将多个 MySQL 数据库(以及其他类型的数据库)同时同步到 Redshift。
此外,设置过程简单而简短。
使用 Amazon Redshift 充分利用 MySQL
即使您将 MySQL 用作业务的基础,但它在数据分析方面的限制是公认的。Redshift 为您的 BI 需求提供了一个简单、强大的解决方案。MySQL 和 Redshift 可以将您的业务推向新的高度。
多种方法可用于从 MySQL 复制数据到 Redshift,如您所见。方法从简单到复杂,从非常缓慢到接近实时。您选择的方法取决于几个因素:
复制频率MySQL 数据集的大小可用的开发者资源
记住:使用变更数据捕获(CDC)是最快、最真实的复制方法,它利用 MySQL 的 binlog。缺点是需要开发人员数小时来构建和维护应用程序。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。