關(guān)于MySQL到Redshift的4 種復(fù)制數(shù)據(jù)的方法分享！干貨！

MySQL 是世界上最受歡迎的開(kāi)源云數(shù)據(jù)庫(kù)，這是有充分理由的。它功能強(qiáng)大、靈活且極其可靠。每天都有數(shù)以萬(wàn)計(jì)的公司使用 MySQL 來(lái)為其基于 Web 的應(yīng)用程序和服務(wù)提供支持。

但是當(dāng)涉及到數(shù)據(jù)分析時(shí)，情況就不同了。即使是最小的分析查詢，MySQL 也會(huì)很快陷入困境，使您的整個(gè)應(yīng)用程序面臨崩潰的風(fēng)險(xiǎn)。正如一位 FlyData 客戶對(duì)我們說(shuō)的那樣，“我做夢(mèng)都?jí)粢?jiàn)我們的 MySQL 生產(chǎn)數(shù)據(jù)庫(kù)宕機(jī)了。”

這就是為什么如此多的公司轉(zhuǎn)向 Amazon Redshift 來(lái)補(bǔ)充 MySQL 的原因。

Redshift 旨在處理 PB 級(jí)數(shù)據(jù)并在很短的時(shí)間內(nèi)提供分析。當(dāng)您將 MySQL 和 Redshift 配對(duì)時(shí)，您可以消除運(yùn)行查詢時(shí)生產(chǎn)數(shù)據(jù)庫(kù)崩潰的風(fēng)險(xiǎn)。

有多種方法可以將 MySQL 數(shù)據(jù)復(fù)制到 Redshift。但首先，讓我們更深入地了解為什么應(yīng)該將 MySQL 數(shù)據(jù)庫(kù)復(fù)制到 Redshift。

為什么將數(shù)據(jù)從 MySQL 復(fù)制到 Redshift

許多使用 MySQL 為其 Web 應(yīng)用程序提供支持的公司選擇 Redshift 進(jìn)行數(shù)據(jù)分析。您也應(yīng)該這樣做的原因有幾個(gè)：

1. 保持應(yīng)用程序性能。正如我們已經(jīng)提到的，在生產(chǎn) MySQL 數(shù)據(jù)庫(kù)上運(yùn)行分析查詢可能對(duì)其性能產(chǎn)生嚴(yán)重影響。它甚至可能導(dǎo)致它崩潰。分析查詢非常耗費(fèi)資源，需要專用的計(jì)算能力。
2. 分析您的所有數(shù)據(jù)。作為 OLTP 數(shù)據(jù)庫(kù)，MySQL 專為交易數(shù)據(jù)而設(shè)計(jì)，例如客戶記錄和財(cái)務(wù)數(shù)據(jù)。但是，您希望從整個(gè)數(shù)據(jù)集（包括非交易類型）中獲得洞察力。您可以使用 Redshift 在一處捕獲和分析您的所有數(shù)據(jù)。
3. 更快的分析。Redshift 是一個(gè)大規(guī)模并行處理 (MPP) 數(shù)據(jù)倉(cāng)庫(kù)，這意味著它可以在很短的時(shí)間內(nèi)處理大量數(shù)據(jù)。另一方面，MySQL 難以擴(kuò)展到大型現(xiàn)代分析查詢所需的計(jì)算能力。即使是 MySQL 副本數(shù)據(jù)庫(kù)也很難達(dá)到與 Redshift 相同的速度。
4. 可擴(kuò)展性。MySQL 旨在在單節(jié)點(diǎn)實(shí)例上運(yùn)行，而不是現(xiàn)代分布式云基礎(chǔ)架構(gòu)。因此，超出單個(gè)節(jié)點(diǎn)的擴(kuò)展需要時(shí)間和資源密集型技術(shù)，例如分片或主節(jié)點(diǎn)設(shè)置。所有這些都會(huì)進(jìn)一步減慢數(shù)據(jù)庫(kù)的速度。

將 MySQL 復(fù)制到 Redshift 的四種方法

由于 MySQL 的固有弱點(diǎn)，許多公司將數(shù)據(jù)復(fù)制到 Redshift 以滿足其分析需求。有4種方法可以實(shí)現(xiàn)這一點(diǎn)：

1. 進(jìn)出口
2. 增量選擇和復(fù)制
3. 使用 binlog 更改數(shù)據(jù)捕獲 (CDC)
4. ETL

1. 進(jìn)出口

復(fù)制到 Redshift 的最簡(jiǎn)單方法是導(dǎo)出整個(gè) MySQL 數(shù)據(jù)。然而，這也是效率最低的方法。共有三個(gè)步驟：

• 出口
• 轉(zhuǎn)變
• 進(jìn)口

出口

首先，使用 MySQL 的mysqldump命令導(dǎo)出數(shù)據(jù)。一個(gè)典型的mysqldump命令如下所示：

java:

$ mysqldump -h yourmysqlhost -u user mydatabase mytable1 mytable2 --result-file dump.sql

此命令的輸出是您的 MySQL SQL 語(yǔ)句。您不能按原樣在 Redshift 上運(yùn)行 SQL — 您必須將語(yǔ)句轉(zhuǎn)換為適合 Redshift 導(dǎo)入的格式。

轉(zhuǎn)變

為獲得最佳上傳性能，請(qǐng)將您的 SQL 語(yǔ)句轉(zhuǎn)換為 TSV（制表符分隔值）格式。您可以使用 Redshift COPY 命令執(zhí)行此操作。

COPY 命令將您的 SQL 語(yǔ)句轉(zhuǎn)換為 TSV 格式。然后將文件批量上傳到 Amazon S3 中的 Redshift 表中。例如，MySQL 轉(zhuǎn)儲(chǔ)中的一行數(shù)據(jù)如下所示：

java:

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

使用COPY，它會(huì)變成這樣：

請(qǐng)注意，值由制表符分隔(\t)。

您可能還必須將數(shù)據(jù)值轉(zhuǎn)換為與 Redshift 兼容。這是因?yàn)?MySQL 和 Redshift 支持不同的列和數(shù)據(jù)類型。

例如，DATE 值“0000-00-00”在 MySQL 中是有效的，但在 Redshift 中會(huì)拋出錯(cuò)誤。您必須將該值轉(zhuǎn)換為可接受的 Redshift 格式，例如“0001-01-01”。

進(jìn)口

轉(zhuǎn)換 MySQL 語(yǔ)句后，最后一步是將它從 S3 導(dǎo)入到 Redshift。為此，只需運(yùn)行 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';

進(jìn)出口的弊端

盡管導(dǎo)入和導(dǎo)出是復(fù)制到 Redshift 的最簡(jiǎn)單方法，但它并不適合頻繁更新。

例如，通過(guò) 100 Mbps 網(wǎng)絡(luò)從 MySQL 導(dǎo)出 18 GB 數(shù)據(jù)大約需要 30 分鐘。將該數(shù)據(jù)導(dǎo)入 Redshift 還需要 30 分鐘。這假設(shè)您在導(dǎo)入或?qū)С銎陂g遇到零連接問(wèn)題，這將迫使您重新開(kāi)始該過(guò)程。

將 MySQL 復(fù)制到 Redshift 的更有效方法是增量 SELECT 和 COPY。

2.增量SELECT和COPY

如果導(dǎo)入和導(dǎo)出對(duì)于您的需求來(lái)說(shuō)太慢，增量 SELECT 和 COPY 可能是您的答案。

SELECT 和 COPY 方法僅更新自上次更新以來(lái)已更改的記錄。與導(dǎo)入和導(dǎo)出整個(gè)數(shù)據(jù)集相比，這花費(fèi)的時(shí)間和帶寬要少得多。SELECT 和 COPY 使您能夠更頻繁地同步 MySQL 和 Redshift。

要使用增量 SELECT 和 COPY，您的 MySQL 表必須滿足幾個(gè)條件：

• 表必須有一個(gè)updated_at列，每次更改行時(shí)都會(huì)更新其時(shí)間戳。 
• 表必須有一個(gè)或多個(gè)唯一鍵。

和導(dǎo)入導(dǎo)出一樣，這個(gè)方法也分三步：

1. 出口

增量 SELECT 僅導(dǎo)出自上次更新以來(lái)已更改的行。您在 MySQL 上運(yùn)行的 SELECT 查詢?nèi)缦滤荆?/p>

java:

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

將結(jié)果保存到文件以進(jìn)行轉(zhuǎn)換。

2. 轉(zhuǎn)型

此轉(zhuǎn)換步驟與導(dǎo)入導(dǎo)出方法相同。將 MySQL 數(shù)據(jù)轉(zhuǎn)換為 Redshift 的 TSV 格式。

3. 進(jìn)口

此時(shí)，您的 MySQL TSV 文件包括更新的行和新插入的行。您不能簡(jiǎn)單地直接對(duì)目標(biāo) Redshift 表運(yùn)行 COPY 命令。這將導(dǎo)致更新的行被復(fù)制。

為避免重復(fù)行，請(qǐng)使用 DELSERT（刪除 + 插入）技術(shù)：

1. 在 Redshift 上創(chuàng)建一個(gè)與目標(biāo)表具有相同定義的臨時(shí)表。
2. 運(yùn)行 COPY 命令將數(shù)據(jù)上傳到臨時(shí)表。
3. 從目標(biāo)表中刪除臨時(shí)表中也存在的行。它看起來(lái)像這樣：
java:

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

id表的唯一鍵在哪里。
4. 最后，將行從臨??時(shí)表插入到目標(biāo)表：

java:

INSERT INTO users (id, firstname, lastname, updated_at) SELECT id, firstname, lastname, updated_at FROM users_staging s;

SELECT 和 COPY 的缺點(diǎn)

增量 SELECT 和 COPY 比導(dǎo)入和導(dǎo)出更有效，但它有其自身的局限性。

主要問(wèn)題是從 MySQL 表中刪除的行會(huì)無(wú)限期地保留在 Redshift 中。如果您想在從 MySQL 清除舊數(shù)據(jù)的同時(shí)保留 Redshift 上的歷史數(shù)據(jù)，這不是問(wèn)題。否則，在 Redshift 中刪除的行會(huì)在數(shù)據(jù)分析過(guò)程中引起嚴(yán)重的頭痛。

這種方法的另一個(gè)缺點(diǎn)是它不復(fù)制表模式更改。當(dāng)在 MySQL 表中添加或刪除列時(shí)，您需要手動(dòng)對(duì) Redshift 表進(jìn)行相應(yīng)的更改。

最后，用于從 MySQL 表中提取更新行的查詢會(huì)影響 MySQL 數(shù)據(jù)庫(kù)的性能。

如果這些缺點(diǎn)中的任何一個(gè)是破壞者，那么下一個(gè)方法適合您。

3. 使用 Binlog 更改數(shù)據(jù)捕獲

更改數(shù)據(jù)捕獲 (CDC) 是一種技術(shù)，可捕獲對(duì) MySQL 中的數(shù)據(jù)所做的更改并將其應(yīng)用于目標(biāo) Redshift 表。它類似于增量 SELECT 和 COPY，因?yàn)樗粚?dǎo)入更改的數(shù)據(jù)，而不是整個(gè)數(shù)據(jù)庫(kù)。

然而，與增量 SELECT 和 COPY 不同，CDC 允許您實(shí)現(xiàn) MySQL 到 Redshift 的真正復(fù)制。

要對(duì) MySQL 數(shù)據(jù)庫(kù)使用 CDC 方法，您必須使用二進(jìn)制更改日志 (binlog)。Binlog 允許您以流的形式捕獲更改數(shù)據(jù)，從而實(shí)現(xiàn)近乎實(shí)時(shí)的復(fù)制。

Binlog 不僅捕獲數(shù)據(jù)更改（插入、更新、刪除），還捕獲表架構(gòu)更改，例如添加/刪除列。它還確保從 MySQL 刪除的行也在 Redshift 中刪除。

Binlog 入門(mén)

當(dāng)您將 CDC 與 binlog 結(jié)合使用時(shí)，您實(shí)際上是在編寫(xiě)一個(gè)應(yīng)用程序，該應(yīng)用程序?qū)⒘鲾?shù)據(jù)從 MySQL 讀取、轉(zhuǎn)換和導(dǎo)入到 Redshift。

您可以使用一個(gè)名為mysql-replication-listener 的開(kāi)源庫(kù)來(lái)執(zhí)行此操作。這個(gè) C++ 庫(kù)提供了一個(gè)流式 API 來(lái)實(shí)時(shí)從 MySQL binlog 讀取數(shù)據(jù)。高級(jí) API 也可用于多種語(yǔ)言：kodama (Ruby) 和python-mysql-replication (Python)。

1. 設(shè)置

首先，設(shè)置 MySQL 配置參數(shù)以啟用 binlog。以下是binlog相關(guān)參數(shù)列表：

java:

log_bin = /file_path/mysql-bin.log

參數(shù)binlog_format設(shè)置 binlog 事件如何存儲(chǔ)在 binlog 文件中的格式。支持 3 種格式：語(yǔ)句、混合和行。

語(yǔ)句格式將查詢按原樣保存在 binlog 文件中（例如UPDATE SET firstname=’Tom’ WHERE id=293;）。雖然它節(jié)省了 binlog 文件的大小，但在用于復(fù)制時(shí)存在問(wèn)題。

要復(fù)制到 Redshift，請(qǐng)使用行格式。

行格式將更改的值保存在 binlog 文件中。它增加了 binlog 文件大小，但可確保 MySQL 和 Amazon Redshift 之間的數(shù)據(jù)一致性。log_bin設(shè)置存儲(chǔ)binlog文件的路徑。expire_logs_days確定 binlog 文件保留的天數(shù)。

在replicate-wild-do-table參數(shù)中指定要復(fù)制的表。只有那些指定的表才能進(jìn)入 binlog 文件。

我們建議將 binlog 文件保留幾天。這可確保您有時(shí)間解決復(fù)制過(guò)程中出現(xiàn)的任何問(wèn)題。

如果您使用 MySQL 復(fù)制從服務(wù)器作為源，則將 指定log-slave-updates為 TRUE很重要。否則，在復(fù)制主服務(wù)器上所做的數(shù)據(jù)更改將不會(huì)記錄在 binlog 中。

此外，您的 MySQL 帳戶需要具有以下權(quán)限才能執(zhí)行復(fù)制相關(guān)任務(wù)：

• 復(fù)制從站
• 選擇
• 重新加載
• 復(fù)制客戶端
• 鎖表

2. 導(dǎo)出和轉(zhuǎn)換

當(dāng)您使用 binlog 時(shí)，“export”實(shí)際上是您的 MySQL binlog 文件的實(shí)時(shí)數(shù)據(jù)流。binlog 數(shù)據(jù)的交付方式取決于您使用的 API。

例如，對(duì)于 Kodama，binlog 數(shù)據(jù)以 binlog 事件流的形式交付。

Kodama 允許您為不同的事件類型（插入、更新、刪除、更改表、創(chuàng)建表等）注冊(cè)事件處理程序。您的應(yīng)用程序?qū)⒔邮斩M(jìn)制日志事件。然后它將生成準(zhǔn)備好用于 Redshift 導(dǎo)入（用于數(shù)據(jù)更改）或架構(gòu)更改（用于表架構(gòu)更改）的輸出。

數(shù)據(jù)更改導(dǎo)入類似于我們其他復(fù)制方法的轉(zhuǎn)換步驟。然而，與其他的不同，binlog 允許您處理已刪除的事件。您需要專門(mén)處理已刪除的事件以維護(hù)Redshift 上傳性能。

3. 進(jìn)口

最后，是時(shí)候?qū)肽?binlog 數(shù)據(jù)流了。

問(wèn)題是 Redshift 沒(méi)有蒸汽上傳功能。使用我們?cè)谠隽?SELECT 和 COPY 方法中概述的 DELSERT 導(dǎo)入技術(shù)。

Binlog 的缺點(diǎn)

Binlog 是從 MySQL 復(fù)制到 Redshift 的理想方法，但它仍然有缺點(diǎn)。構(gòu)建您的 CDC 應(yīng)用程序需要認(rèn)真的開(kāi)發(fā)工作。

除了我們上面描述的數(shù)據(jù)流之外，您還必須構(gòu)建：

• 交易管理。跟蹤數(shù)據(jù)流性能，以防錯(cuò)誤迫使您的應(yīng)用程序在讀取二進(jìn)制日志數(shù)據(jù)時(shí)停止。事務(wù)管理確保您可以從上次中斷的地方繼續(xù)。
• 數(shù)據(jù)緩沖和重試。同樣，當(dāng)您的應(yīng)用程序正在發(fā)送數(shù)據(jù)時(shí)，Redshift 可能會(huì)變得不可用。您的應(yīng)用程序需要緩沖未發(fā)送的數(shù)據(jù)，直到 Redshift 集群重新聯(lián)機(jī)。如果此步驟操作不當(dāng)，可能會(huì)導(dǎo)致數(shù)據(jù)丟失或重復(fù)數(shù)據(jù)。
• 表模式更改支持。表模式更改二進(jìn)制日志事件（更改/添加/刪除表）作為本機(jī) MySQL SQL 語(yǔ)句出現(xiàn)，它不會(huì)按原樣在 Redshift 上運(yùn)行。要支持表架構(gòu)更改，您必須將 MySQL 語(yǔ)句轉(zhuǎn)換為相應(yīng)的 Amazon Redshift 語(yǔ)句。

4. 使用 ETL 即服務(wù)

借助 ETL 工具，您可以近乎實(shí)時(shí)地將數(shù)據(jù)復(fù)制到 Redshift。

與 CDC 方法不同，此類工具可以管理整個(gè)復(fù)制過(guò)程并自動(dòng)將 MySQL 數(shù)據(jù)類型映射為 Redshift 使用的格式，因此您不必這樣做。您甚至可以同時(shí)將多個(gè) MySQL 數(shù)據(jù)庫(kù)（以及其他類型的數(shù)據(jù)庫(kù)）同步到 Redshift。

此外，設(shè)置過(guò)程簡(jiǎn)單而簡(jiǎn)短。

使用 Amazon Redshift 充分利用 MySQL

您依靠 MySQL 為您的業(yè)務(wù)提供動(dòng)力，但它在數(shù)據(jù)分析方面的局限性是眾所周知的。Redshift 為您的 BI 需求提供了一個(gè)簡(jiǎn)單、強(qiáng)大的解決方案。MySQL 和 Redshift 可以將您的業(yè)務(wù)推向新的高度。

如您所見(jiàn)，有多種方法可以將數(shù)據(jù)從 MySQL 復(fù)制到 Redshift。方法從簡(jiǎn)單到復(fù)雜，從非常緩慢到接近實(shí)時(shí)。您選擇的方法取決于幾個(gè)因素：

• 復(fù)制頻率
• MySQL 數(shù)據(jù)集的大小
• 可用的開(kāi)發(fā)者資源

請(qǐng)記?。鹤羁臁⒆钫鎸?shí)的復(fù)制方法是變更數(shù)據(jù)捕獲 (CDC)，它利用 MySQL 的 binlog。缺點(diǎn)是需要開(kāi)發(fā)人員數(shù)小時(shí)來(lái)構(gòu)建和維護(hù)應(yīng)用程序。