[[411316]]

【51CTO.com快譯】目前，MySQL已憑借著其強大的功能、靈活且可靠的服務，成為了世界上最受歡迎的開源云端數(shù)據(jù)庫之一。每天都有數(shù)以萬計的公司，使用MySQL來為其基于Web的應用和服務提供支持。

但是當涉及到數(shù)據(jù)分析時，情況就不同了。即使是最小的分析查詢，MySQL也會很快陷入困境，甚至會讓您的整個應用程序面臨崩潰的風險。而Redshift則能夠處理PB級的數(shù)據(jù)，并在較短的時間內(nèi)提供各種分析。這就是為什么如今許多公司都轉(zhuǎn)為使用Amazon的Redshift，來補齊MySQL短板的原因。也就是說，它們通過將MySQL和Redshift配合使用，以消除在運行查詢時產(chǎn)生導致數(shù)據(jù)庫崩潰的風險。對此，我們需要將MySQL里的數(shù)據(jù)設法復制到Redshift中。下面讓我們來具體討論四種實用的實現(xiàn)方法。

為何要將數(shù)據(jù)從MySQL復制到Redshift?

對于提供Web應用的公司而言，選擇將數(shù)據(jù)從MySQL復制到Redshift，不但是為了便于數(shù)據(jù)分析，而且可以獲得如下方面的優(yōu)勢：

• 保持應用的性能。正如我們已經(jīng)提到的，在生產(chǎn)環(huán)境的MySQL數(shù)據(jù)庫上運行分析查詢，不但會對其性能產(chǎn)生嚴重影響，而且可能導致其崩潰。鑒于分析查詢非常耗費資源，我們需要為其分配專用的計算力。
• 分析所有數(shù)據(jù)。作為OLTP數(shù)據(jù)庫，MySQL是專為諸如：客戶記錄和財務數(shù)據(jù)等事務性數(shù)據(jù)而設計的。如果您希望從整個數(shù)據(jù)集(包括非事務類型)中獲得有關數(shù)據(jù)的洞見，則需要使用Redshift從同一處捕獲和分析您的所有數(shù)據(jù)。
• 更快的分析。Redshift屬于大規(guī)模并行處理 (massively parallel processing，MPP)類數(shù)據(jù)倉庫。這意味著，它可以在較短的時間內(nèi)處理大量的數(shù)據(jù)。而作為對比，MySQL則難以為大型分析查詢擴展出足夠的計算力。即便是MySQL的副本數(shù)據(jù)庫，也很難達到與Redshift同等的速度。
• 可擴展性。MySQL主要是在單個節(jié)點實例上運行，而并非分布式的云基礎架構處。因此，超出單個節(jié)點的擴展往往需要花費時間，并且需要用到諸如分片、或主節(jié)點設置等資源密集型的技術。這些都會進一步減慢數(shù)據(jù)庫的速度與效率。

將MySQL復制到Redshift的四種方法

許多公司都會通過如下四種方法，來實現(xiàn)從MySQL到Redshift的數(shù)據(jù)復制：

一、導入與導出

將數(shù)據(jù)復制到Redshift的最簡單方法，莫過于導出整個MySQL的數(shù)據(jù)。不過，這也是效率最低的方法。它包含了：導出、轉(zhuǎn)換、導入三個步驟。

導出

我們可以使用MySQL的經(jīng)典MySQLdump命令，按照如下格式導出數(shù)據(jù)：

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

由于該命令的輸出是MySQL的SQL語句，而無法運行在Redshift上，因此您必須將該語句轉(zhuǎn)換為適合Redshift導入的格式。

轉(zhuǎn)換

為獲得最佳的上傳性能，您需要將SQL語句轉(zhuǎn)換為TSV(tab-separated values，制表符分隔值)的格式。為此，您可以使用Redshift的COPY命令，將數(shù)據(jù)文件批量上傳到Amazon S3中的Redshift表中。下面展示了MySQLdump中的一行數(shù)據(jù)：

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

請注意，這些值都是被制表符(\t)分隔開來的。而且，如果MySQL和Redshift支持不同的數(shù)據(jù)列和類型，您可能還需要將數(shù)據(jù)值轉(zhuǎn)換為與Redshift相兼容的類型。例如，DATE值“0000-00-00”在MySQL中是有效的，但是在Redshift中會被拋出錯誤。那么，您就必須將該值轉(zhuǎn)換為可被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'; 

導入導出的缺點

導入導出的數(shù)據(jù)復制方法雖然簡單，但是它并不適合頻繁更新的場景。例如，如果通過100 Mbps的網(wǎng)絡從MySQL導出18 GB的數(shù)據(jù)，則需要大約30分鐘;而將這些數(shù)據(jù)導入Redshift也還需要30分鐘。一旦網(wǎng)絡連接出現(xiàn)了中斷，則該過程還需重做一遍。

二、增量SELECT和COPY

SELECT和COPY方法僅作用于更新那些自上次更新以來，已變更的記錄。與導入導出整個數(shù)據(jù)集相比，該方法花費的時間和帶寬要少得多，因此能夠更頻繁地將MySQL與Redshift進行同步。不過，您的MySQL表必須滿足如下兩個條件，方可使用該增量方法：

• 數(shù)據(jù)表必須有一個updated_at列，而且在每次變更行時，都會更新其時間戳。
• 數(shù)據(jù)表必須有一到多個唯一鍵。

和導入導出一樣，該增量方法也分三個步驟：

1. 導出

由于增量SELECT僅導出自上次更新以來已變更的行，因此您需要在MySQL上運行如下SELECT查詢命令：

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

然后將結果保存到文件中，以備后續(xù)的轉(zhuǎn)換。

2. 轉(zhuǎn)換

與導入導出方法相同，該步驟是將MySQL數(shù)據(jù)轉(zhuǎn)換為Redshift支持的TSV格式。

3. 導入

至此，MySQL的TSV文件中已包含了被更新的行和新插入的行。不過，為了避免更新的行被直接復制過去，而出現(xiàn)重復行，您不可簡單、直接地運行COPY命令，而應當使用如下DELSERT(delete + insert)的方式：

• 在Redshift上創(chuàng)建一個與目標表具有相同定義的臨時表。
• 運行COPY命令將數(shù)據(jù)上傳到臨時表中。
• 使用如下命令，從目標表中刪除那些已在臨時表中存在的行。

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

• 最后，將剩下的數(shù)據(jù)行，從臨​​時表插入到目標表中：

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

SELECT和COPY的缺點

雖然增量SELECT和COPY比導入導出更為有效，但它也有著自身的局限性。其主要問題在于：從MySQL表中刪除的數(shù)據(jù)行，會無限期地保留在Redshift中。因此，如果您想在從MySQL中清除舊數(shù)據(jù)的同時，保留Redshift上的歷史數(shù)據(jù)，那么就無所謂。否則，您就需要在數(shù)據(jù)分析的過程中，去額外刪除Redshift中的數(shù)據(jù)行。

此方法的另一個缺點是，它不會去復制數(shù)據(jù)表在結構模式上(schema)的變更。也就是說，當您在MySQL表中添加或刪除數(shù)據(jù)列時，則需要手動對Redshift數(shù)據(jù)表進行相應的變更。

此外，那些被用于從MySQL表中提取更新數(shù)據(jù)行的查詢，也會影響MySQL數(shù)據(jù)庫的整體性能。

三、使用Binlog變更數(shù)據(jù)的捕獲

變更數(shù)據(jù)捕獲 (Change data capture，CDC)技術，可以捕獲任何在MySQL中發(fā)生的數(shù)據(jù)變更，并將其應用到目標Redshift表上。與增量SELECT和COPY方法的類似，它只導入變更的數(shù)據(jù)，而非整個數(shù)據(jù)庫;而與增量方法不同的是，CDC允許您實現(xiàn)從MySQL到Redshift的真正復制。

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

Binlog不僅能夠捕獲數(shù)據(jù)的變更(如：插入、更新、刪除)，而且可以捕獲數(shù)據(jù)表在結構模式上的變更(例如：添加、刪除某列)。此外，它還能確保從MySQL刪除的數(shù)據(jù)行也在Redshift中被刪除。

當您將CDC與binlog結合使用時，您實際上是通過編寫一個應用程序，實現(xiàn)了流數(shù)據(jù)從MySQL讀取、轉(zhuǎn)換和導入至Redshift的過程。具體而言，您可以使用一個名為mysql-replication-listener的開源庫來實現(xiàn)。這個C++庫提供了一個流式API，在MySQL的binlog處實時讀取數(shù)據(jù)。當然，其他高級語言，包括Ruby的kodama和Python的python-mysql-replication也提供了類似的高級API。其具體實現(xiàn)過程為：

1. 設置

首先，請參照MySQL的如下配置參數(shù)設置，來啟用binlog：

Java 
log_bin = /file_path/mysql-bin.log 

其中，參數(shù)binlog_format設置了binlog事件存儲在binlog文件中的格式。它支持：語句、混合和行，三種格式。其中，語句格式會將查詢按照原樣保存到binlog文件中(例如：UPDATE SET firstname=’Tom’ WHERE id=293;)。這樣雖然節(jié)省了binlog文件的大小，但是在復制過程中，可能會出現(xiàn)問題。

因此，對Redshift的復制場景而言，請使用行的格式。該格式會將變更的值，保存在binlog文件中。它雖然增加了binlog文件的大小，但是可以確保MySQL與Redshift之間數(shù)據(jù)的一致性。

log_bin設置了存儲binlog文件的路徑。expire_logs_days確定了binlog文件被保留的天數(shù)。我們建議將binlog文件保留數(shù)天，以確保有時間解決復制期間出現(xiàn)的任何問題。而replicate-wild-do-table參數(shù)則指定了待復制的表。也就是說，只有那些被指定的表才能進入binlog文件。

如果您使用MySQL的從服務器(slave server)作為復制源的話，則需要將log-slave-updates設置為TRUE。否則，在主復制服務器上所做的數(shù)據(jù)變更，將不會被記錄在binlog中。

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

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

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

當您使用binlog時，需要導出的實際上是MySQL的各個binlog文件的實時數(shù)據(jù)流。而binlog數(shù)據(jù)的具體交付方式，則取決于您使用到的API。例如，對于Kodama而言，binlog數(shù)據(jù)會根據(jù)binlog事件流的形式予以交付。也就是說，Kodama允許您為不同的事件類型(插入、更新、刪除、變更表、創(chuàng)建表等)注冊事件處理句柄(handlers)。應用程序在接收到binlog事件后，會生成一個用于將數(shù)據(jù)變更導入Redshift，或包含表結構模式變更的輸出。

與其他復制方法不同，binlog變更方式需要您專門處理那些已被刪除的事件，以維持Redshift的上傳性能。

3. 導入

為了將binlog數(shù)據(jù)流上傳并導入Redshift，我們需要借用在增量SELECT和COPY方法中提及的DELSERT導入技術。

Binlog的缺點

Binlog方法雖然能夠滿足從MySQL到Redshift的數(shù)據(jù)復制需求，但是它需要您花時間去開發(fā)CDC應用。也就是說，除了上面提到的數(shù)據(jù)流之外，您還必須構建：

• 事務管理。跟蹤數(shù)據(jù)流的性能，以免應用程序在讀取binlog的數(shù)據(jù)時出現(xiàn)報錯和中止，并能夠從上一次中斷處繼續(xù)。
• 數(shù)據(jù)緩沖和重試。為了避免Redshift在應用程序發(fā)送數(shù)據(jù)時不可用，進而導致數(shù)據(jù)丟失或重復，您應當設法讓應用程序緩沖未發(fā)送的數(shù)據(jù)，直到Redshift集群重新上線。
• 支持表結構模式的變更。Binlog中的表結構模式的變更事件(如：變更、添加、刪除表)往往是作為MySQL原生的SQL語句出現(xiàn)的。不過，它不能直接運行在Redshift上，而需要設法將此類變更從MySQL語句，轉(zhuǎn)換為相應的Redshift語句。

四、使用ETL即服務

借助ETL工具，您可以近乎實時地將數(shù)據(jù)復制到Redshift中。與CDC方法不同，此類工具可以管理整個復制過程，并自動將MySQL數(shù)據(jù)的類型，映射為Redshift使用的格式。您甚至可以同時將多個MySQL數(shù)據(jù)庫(以及其他類型的數(shù)據(jù)庫)同步到Redshift上。由于其設置過程非常簡便，此處就不再贅述了。

小結

綜上所述，MySQL和Redshift的聯(lián)合使用，為您的BI需求提供了簡單卻又強大的解決方案。上文列舉的四種將數(shù)據(jù)從MySQL復制到Redshift的方法，既從簡單到復雜，又從非常緩慢到接近實時。具體該如何選擇，請您參考如下三方面因素：

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

其中，最快、最可靠的復制方法當屬：利用了MySQL binlog的變更數(shù)據(jù)捕獲 (CDC)。不過其缺點是需要開發(fā)人員花時間去構建和維護應用程序。因此，您需要根據(jù)實際業(yè)務目標和數(shù)據(jù)分析需求，來做出明智的決定。

原文標題：MySQL to Redshift: 4 Ways to Replicate Your Data，作者：Ben Putano

【51CTO譯稿，合作站點轉(zhuǎn)載請注明原文譯者和出處為51CTO.com】