为什么将数据从MySQL复制到Redshift

为什么将数据从 MySQL 复制到 Redshift

许多使用 MySQL 为其 Web 应用程序提供支持的公司选择 Redshift 进行数据分析。您也应该这样做的原因有几个：

1. 保持应用程序性能。正如我们已经提到的，在生产 MySQL 数据库上运行分析查询可能对其性能产生严重影响。它甚至可能导致它崩溃。分析查询非常耗费资源，需要专用的计算能力。

2. 分析您的所有数据。作为 OLTP 数据库，MySQL 专为交易数据而设计，例如客户记录和财务数据。但是，您希望从整个数据集（包括非交易类型）中获得洞察力。您可以使用 Redshift 在一处捕获和分析您的所有数据。

3. 更快的分析。Redshift 是一个大规模并行处理 (MPP) 数据仓库，这意味着它可以在很短的时间内处理大量数据。另一方面，MySQL 难以扩展到大型现代分析查询所需的计算能力。即使是 MySQL 副本数据库也很难达到与 Redshift 相同的速度。

4. 可扩展性。MySQL 旨在在单节点实例上运行，而不是现代分布式云基础架构。因此，超出单个节点的扩展需要时间和资源密集型技术，例如分片或主节点设置。所有这些都会进一步减慢数据库的速度。

将 MySQL 复制到 Redshift 的四种方法

由于 MySQL 的固有弱点，许多公司将数据复制到 Redshift 以满足其分析需求。有4种方法可以实现这一点：

1. 进出口

2. 增量选择和复制

3. 使用 binlog 更改数据捕获 (CDC)

4. 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 格式。然后将文件批量上传到 Amazon S3 中的 Redshift 表中。例如，MySQL 转储中的一行数据如下所示：

java:

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

使用COPY，它会变成这样：

请注意，值由制表符分隔(\t)。

您可能还必须将数据值转换为与 Redshift 兼容。这是因为 MySQL 和 Redshift 支持不同的列和数据类型。

例如，DATE 值“0000-00-00”在 MySQL 中是有效的，但在 Redshift 中会抛出错误。您必须将该值转换为可接受的 Redshift 格式，例如“0001-01-01”。

进口

转换 MySQL 语句后，最后一步是将它从 S3 导入到 Redshift。为此，只需运行 COPY 命令：

java:

COPY users
FROM 's3://my_s3_bucket/unload-folder/users_' credentials  
'aws_access_key_id=your_access_key;aws_secret_access_key=your_secret_key';

进出口的弊端

尽管导入和导出是复制到 Redshift 的最简单方法，但它并不适合频繁更新。

例如，通过 100 Mbps 网络从 MySQL 导出 18 GB 数据大约需要 30 分钟。将该数据导入 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（删除 + 插入）技术：

1. 在 Redshift 上创建一个与目标表具有相同定义的临时表。

2. 运行 COPY 命令将数据上传到临时表。

3. 从目标表中删除临时表中也存在的行。它看起来像这样：

4. java:

DELETE FROM users USING users_staging s WHERE users.id = s.id;

5. id表的唯一键在哪里。

6. 最后，将行从临时表插入到目标表：

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，因为它只导入更改的数据，而不是整个数据库。

然而，与增量 SELECT 和 COPY 不同，CDC 允许您实现 MySQL 到 Redshift 的真正复制。

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

Binlog 不仅捕获数据更改（插入、更新、删除），还捕获表架构更改，例如添加/删除列。它还确保从 MySQL 删除的行也在 Redshift 中删除。

Binlog 入门

当您将 CDC 与 binlog 结合使用时，您实际上是在编写一个应用程序，该应用程序将流数据从 MySQL 读取、转换和导入到 Redshift。

您可以使用一个名为mysql-replication-listener 的开源库来执行此操作。这个 C++ 库提供了一个流式 API 来实时从 MySQL binlog 读取数据。高级 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文件的路径。expire_logs_days确定 binlog 文件保留的天数。

在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。缺点是需要开发人员数小时来构建和维护应用程序。