[[411316]]

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

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

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

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

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

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

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

一、導(dǎo)入與導(dǎo)出

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

導(dǎo)出

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

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

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

轉(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’); 

請(qǐng)注意，這些值都是被制表符(\t)分隔開來的。而且，如果MySQL和Redshift支持不同的數(shù)據(jù)列和類型，您可能還需要將數(shù)據(jù)值轉(zhuǎn)換為與Redshift相兼容的類型。例如，DATE值“0000-00-00”在MySQL中是有效的，但是在Redshift中會(huì)被拋出錯(cuò)誤。那么，您就必須將該值轉(zhuǎn)換為可被Redshift接受的格式--“0001-01-01”。

導(dǎo)入

您只需要運(yùn)行如下COPY命令，便可完成向Redshift的導(dǎo)入工作：

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

導(dǎo)入導(dǎo)出的缺點(diǎn)

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

二、增量SELECT和COPY

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

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

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

1. 導(dǎo)出

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

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

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

2. 轉(zhuǎn)換

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

3. 導(dǎo)入

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

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

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

• 最后，將剩下的數(shù)據(jù)行，從臨​​時(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)出更為有效，但它也有著自身的局限性。其主要問題在于：從MySQL表中刪除的數(shù)據(jù)行，會(huì)無限期地保留在Redshift中。因此，如果您想在從MySQL中清除舊數(shù)據(jù)的同時(shí)，保留Redshift上的歷史數(shù)據(jù)，那么就無所謂。否則，您就需要在數(shù)據(jù)分析的過程中，去額外刪除Redshift中的數(shù)據(jù)行。

此方法的另一個(gè)缺點(diǎn)是，它不會(huì)去復(fù)制數(shù)據(jù)表在結(jié)構(gòu)模式上(schema)的變更。也就是說，當(dāng)您在MySQL表中添加或刪除數(shù)據(jù)列時(shí)，則需要手動(dòng)對(duì)Redshift數(shù)據(jù)表進(jìn)行相應(yīng)的變更。

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

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

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

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

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

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

1. 設(shè)置

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

Java 
log_bin = /file_path/mysql-bin.log 

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

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

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

如果您使用MySQL的從服務(wù)器(slave server)作為復(fù)制源的話，則需要將log-slave-updates設(shè)置為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í)，需要導(dǎo)出的實(shí)際上是MySQL的各個(gè)binlog文件的實(shí)時(shí)數(shù)據(jù)流。而binlog數(shù)據(jù)的具體交付方式，則取決于您使用到的API。例如，對(duì)于Kodama而言，binlog數(shù)據(jù)會(huì)根據(jù)binlog事件流的形式予以交付。也就是說，Kodama允許您為不同的事件類型(插入、更新、刪除、變更表、創(chuàng)建表等)注冊(cè)事件處理句柄(handlers)。應(yīng)用程序在接收到binlog事件后，會(huì)生成一個(gè)用于將數(shù)據(jù)變更導(dǎo)入Redshift，或包含表結(jié)構(gòu)模式變更的輸出。

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

3. 導(dǎo)入

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

Binlog的缺點(diǎn)

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

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

四、使用ETL即服務(wù)

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

小結(jié)

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

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

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

原文標(biāo)題：MySQL to Redshift: 4 Ways to Replicate Your Data，作者：Ben Putano

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