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

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

但是當涉及到數(shù)據(jù)分析時，情況就不同了。即使是最小的分析查詢，MySQL 也會很快陷入困境，使您的整個應用程序面臨崩潰的風險。正如一位 FlyData 客戶對我們說的那樣，“我做夢都夢見我們的 MySQL 生產(chǎn)數(shù)據(jù)庫宕機了?！?/p>

這就是為什么如此多的公司轉向 Amazon Redshift 來補充 MySQL 的原因。

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

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

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

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

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

將 MySQL 復制到 Redshift 的四種方法

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

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

1. 進出口

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

• 出口
• 轉變
• 進口

出口

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

java:

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

此命令的輸出是您的 MySQL SQL 語句。您不能按原樣在 Redshift 上運行 SQL — 您必須將語句轉換為適合 Redshift 導入的格式。

轉變

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

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

java:

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

使用COPY，它會變成這樣：

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

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

例如，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 網(wǎng)絡從 MySQL 導出 18 GB 數(shù)據(jù)大約需要 30 分鐘。將該數(shù)據(jù)導入 Redshift 還需要 30 分鐘。這假設您在導入或導出期間遇到零連接問題，這將迫使您重新開始該過程。

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

2.增量SELECT和COPY

如果導入和導出對于您的需求來說太慢，增量 SELECT 和 COPY 可能是您的答案。

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

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

• 表必須有一個updated_at列，每次更改行時都會更新其時間戳。 
• 表必須有一個或多個唯一鍵。

和導入導出一樣，這個方法也分三步：

1. 出口

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

java:

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

將結果保存到文件以進行轉換。

2. 轉型

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

3. 進口

此時，您的 MySQL TSV 文件包括更新的行和新插入的行。您不能簡單地直接對目標 Redshift 表運行 COPY 命令。這將導致更新的行被復制。

為避免重復行，請使用 DELSERT（刪除 + 插入）技術：

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

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

id表的唯一鍵在哪里。
4. 最后，將行從臨??時表插入到目標表：

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 清除舊數(shù)據(jù)的同時保留 Redshift 上的歷史數(shù)據(jù)，這不是問題。否則，在 Redshift 中刪除的行會在數(shù)據(jù)分析過程中引起嚴重的頭痛。

這種方法的另一個缺點是它不復制表模式更改。當在 MySQL 表中添加或刪除列時，您需要手動對 Redshift 表進行相應的更改。

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

如果這些缺點中的任何一個是破壞者，那么下一個方法適合您。

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

更改數(shù)據(jù)捕獲 (CDC) 是一種技術，可捕獲對 MySQL 中的數(shù)據(jù)所做的更改并將其應用于目標 Redshift 表。它類似于增量 SELECT 和 COPY，因為它只導入更改的數(shù)據(jù)，而不是整個數(shù)據(jù)庫。

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

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

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

Binlog 入門

當您將 CDC 與 binlog 結合使用時，您實際上是在編寫一個應用程序，該應用程序將流數(shù)據(jù)從 MySQL 讀取、轉換和導入到 Redshift。

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

1. 設置

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

java:

log_bin = /file_path/mysql-bin.log

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

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

要復制到 Redshift，請使用行格式。

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

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

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

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

此外，您的 MySQL 帳戶需要具有以下權限才能執(zhí)行復制相關任務：

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

2. 導出和轉換

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

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

Kodama 允許您為不同的事件類型（插入、更新、刪除、更改表、創(chuàng)建表等）注冊事件處理程序。您的應用程序將接收二進制日志事件。然后它將生成準備好用于 Redshift 導入（用于數(shù)據(jù)更改）或架構更改（用于表架構更改）的輸出。

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

3. 進口

最后，是時候導入您的 binlog 數(shù)據(jù)流了。

問題是 Redshift 沒有蒸汽上傳功能。使用我們在增量 SELECT 和 COPY 方法中概述的 DELSERT 導入技術。

Binlog 的缺點

Binlog 是從 MySQL 復制到 Redshift 的理想方法，但它仍然有缺點。構建您的 CDC 應用程序需要認真的開發(fā)工作。

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

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

4. 使用 ETL 即服務

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

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

此外，設置過程簡單而簡短。

使用 Amazon Redshift 充分利用 MySQL

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

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

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

請記住：最快、最真實的復制方法是變更數(shù)據(jù)捕獲 (CDC)，它利用 MySQL 的 binlog。缺點是需要開發(fā)人員數(shù)小時來構建和維護應用程序。