將數據從MySQL復制到Redshift的四種方式
譯文【51CTO.com快譯】目前，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上。由于其設置過程非常簡便，此處就不再贅述了。
小結
綜上所述，MySQL和Redshift的聯合使用，為您的BI需求提供了簡單卻又強大的解決方案。上文列舉的四種將數據從MySQL復制到Redshift的方法，既從簡單到復雜，又從非常緩慢到接近實時。具體該如何選擇，請您參考如下三方面因素：
- 復制頻率
- MySQL數據集的大小
- 可用的開發資源
其中，最快、最可靠的復制方法當屬：利用了MySQL binlog的變更數據捕獲 (CDC)。不過其缺點是需要開發人員花時間去構建和維護應用程序。因此，您需要根據實際業務目標和數據分析需求，來做出明智的決定。
原文標題：MySQL to Redshift: 4 Ways to Replicate Your Data，作者：Ben Putano
【51CTO譯稿，合作站點轉載請注明原文譯者和出處為51CTO.com】
