將資料從MySQL複製到Redshift的四種方式

2021-07-16 03:13:37

目前，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命令，按照如下格式匯出資料：

Java $ 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中的一行資料：

Java 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的匯入工作：

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';

匯入匯出的缺點

匯入匯出的資料複製方法雖然簡單，但是它並不適合頻繁更新的場景。例如，如果通過100 Mbps的網路從MySQL匯出18 GB的資料，則需要大約30分鐘;而將這些資料匯入Redshift也還需要30分鐘。一旦網路連接出現了中斷，則該過程還需重做一遍。

二、增量SELECT和COPY

SELECT和COPY方法僅作用於更新那些自上次更新以來，已變更的記錄。與匯入匯出整個資料集相比，該方法花費的時間和頻寬要少得多，因此能夠更頻繁地將MySQL與Redshift進行同步。不過，您的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檔案中已包含了被更新的行和新插入的行。不過，為了避免更新的行被直接複製過去，而出現重複行，您不可簡單、直接地運行COPY命令，而應當使用如下DELSERT(delete + insert)的方式：

在Redshift上創建一個與目標表具有相同定義的臨時表。
運行COPY命令將資料上傳到臨時表中。
使用如下命令，從目標表中刪除那些已在臨時表中存在的行。

Java DELETE FROM users USING users_staging s WHERE users.id = s.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中的資料行。

此方法的另一個缺點是，它不會去複製資料表在結構模式上(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：

Java 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上。由於其設定過程非常簡便，此處就不再贅述了。