【.com快译】目前，MySQL以其强大的功能、灵活可靠的服务成为全球最受欢迎的开源云数据库之一。每天，数以万计的公司使用MySQL来支持他们基于Web的应用程序和服务。但是当涉及到数据分析时，情况就不同了。即使是最小的分析查询，MySQL也能迅速陷入困境，甚至使您的整个应用程序面临崩溃的风险。而Redshift则可以在较短的时间内处理PB级的数据并提供各种分析。这也是为什么现在很多公司都转向Amazon的Redshift来弥补MySQL的短板。也就是说，他们将MySQL与Redshift结合使用，以消除运行查询时数据库崩溃的风险。为此，我们需要尝试将MySQL中的数据复制到Redshift中。让我们详细讨论四种实用的实现方法。为什么要将数据从MySQL复制到Redshift？对于提供Web应用的公司来说，选择将数据从MySQL复制到Redshift，不仅是为了方便数据分析，还有以下好处：保持应用性能。正如我们已经提到的，在生产MySQL数据库上运行分析查询会对其性能产生严重影响，并可能导致其崩溃。由于分析查询是资源密集型的，我们需要为它们分配专用的计算能力。分析所有数据。作为OLTP数据库，MySQL专为交易数据而设计，例如：客户记录和财务数据。如果您想从整个数据集（包括非事务类型）中获取有关数据的见解，您需要使用Redshift从同一位置捕获和分析所有数据。更快的分析。Redshift是一个大规模并行处理(MPP)数据仓库。这意味着，它可以在相对较短的时间内处理大量数据。相比之下，MySQL很难为大型分析查询扩展足够的计算能力。即使是MySQL的副本数据库也很难达到与Redshift相同的速度。可扩展性。MySQL主要运行在单节点实例上，而不是分布式云基础设施。因此，扩展到单个节点之外通常需要时间，并且需要资源密集型技术，例如分片或主节点设置。这些都会进一步拖慢数据库的速度和效率。将MySQL复制到Redshift的四种方法很多公司使用以下四种方法将数据从MySQL复制到Redshift：1.导入和导出将数据复制到Redshift最简单的方法是导出整个MySQL数据。然而，这也是效率最低的方法。包括：导出、转换、导入三个步骤。导出我们可以使用MySQL经典的MySQLdump命令导出数据，格式如下：Java$MySQLdump-hyourmysqlhost-uusermydatabasemytable1mytable2--result-filedump.sql由于该命令输出的是MySQLSQL语句，无法在Redshift上运行，所以你该语句必须转换为适合Redshift导入的格式。Conversion为了获得最佳的上传性能，需要将SQL语句转换为TSV（tab-separatedvalues，制表符分隔值）格式。为此，您可以使用Redshift的COPY命令将数据文件批量上传到AmazonS3中的Redshift表。下面是MySQLdump中的一行数据：Javamysql>INSERTINTO`users`(`id`,`firstname`,`lastname`,`age`)VALUES(1923,'John','Smith',34),(1925,'汤米','国王');请注意，这些值由制表符(\t)分隔。另外，如果MySQL和Redshift支持不同的数据列和类型，你可能还需要将数据值转换成与Redshift兼容的类型。例如，DATE值“0000-00-00”在MySQL中有效，但在Redshift中会抛出错误。然后，您必须将该值转换为Redshift可接受的格式-“0001-01-01”。导入只需要运行如下COPY命令即可完成导入到Redshift：数据复制方式简单，不适合频繁更新的场景。例如，如果您通过100Mbps的网络从MySQL导出18GB的数据，大约需要30分钟；将这些数据导入Redshift还需要30分钟。一旦网络连接中断，则需要再次重复该过程。2.增量SELECT和COPYSELECT和COPY方法只用于更新那些自上次更新后发生变化的记录。与导入和导出整个数据集相比，此方法花费的时间和带宽要少得多，因此可以使MySQL与Redshift更频繁地同步。但是，你的MySQL表必须满足以下两个条件才能使用这种增量方式：数据表必须有一个updated_at列，并且它的时间戳会在每改变一行时更新。一张数据表必须有一个或多个唯一键。与import和export一样，这种增量方式也分为三个步骤：1.Export由于增量SELECT只导出自上次更新以来发生变化的行，因此需要在MySQL上运行如下SELECT查询命令：JavaSELECT*FROMusersWHEREupdated_at>='2016-08-1220:00:00'；然后将结果保存到文件中以供后续转换。2.转换方法与导入导出相同。这一步是将MySQL数据转换成Redshift支持的TSV格式。3.此时，MySQLTSV文件包含更新和新插入的行。但是，为了防止更新的行被直接复制而出现重复行，不能简单的直接运行COPY命令，而是使用下面的DELSERT（删除+插入）方法：桌子。运行COPY命令将数据上传到临时表。使用以下命令从目标表中删除那些已存在于临时表中的行。JavaDELETEFROMusersUSINGusers_stagingsWHEREusers.id=s.id;最后，将临时表中剩余的数据行插入到目标表中：SELECT和COPY的缺点虽然增量SELECT和COPY比import和export效率更高，但它们也有自己的局限性。主要问题是从MySQL表中删除的行会无限期地保留在Redshift中。因此，如果您想在Redshift上保留历史数据，同时从MySQL中清除旧数据，那么这并不重要。否则，需要在数据分析时额外删除Redshift中的数据行。此方法的另一个缺点是它不会复制对表架构的更改。也就是说，当你在MySQL表中增加或删除数据列时，需要手动对Redshift数据表进行相应的修改。此外，用于从MySQL表中获取更新行的查询也会影响MySQL数据库的整体性能。3、使用CDC（Changedatacapture）技术抓取Binlog变更数据，可以抓取MySQL中发生的任何数据变更，应用到目标Redshift表中。类似于增量SELECT和COPY方法，它只导入变化的数据，而不是整个数据库；与增量方法不同，CDC允许您实现从MySQL到Redshift的真正复制。要对MySQL数据库使用CDC方法，您必须使用binlog（二进制更改日志）。Binlog允许您以数据流的形式捕获变化的数据，从而实现近乎实时的复制。Binlog不仅可以捕获数据变化（如：insert、update、delete），还可以捕获数据表结构模式的变化（如：增加、删除某列）。此外，它确保从MySQL中删除的行在Redshift中也被删除。当你把CDC和binlog结合起来的时候，你实际上是通过写一个应用来实现从MySQL读取流式数据，转换，导入到Redshift的过程。具体来说，您可以使用名为mysql-replication-listener的开源库来实现。这个C++库提供了一个流式API来实时读取MySQL的binlog中的数据。当然，其他高级语言，包括Ruby的kodama、Python的python-mysql-replication等也提供了类似的高级API。具体实现过程为：1.设置首先请参考以下MySQL配置参数设置启用binlog：javalog_bin=/file_path/mysql-bin.log其中，参数binlog_format设置了binlogevents存储的格式二进制日志文件。它支持：statement、mixin和line，三种格式。其中，语句格式会将查询原样保存到binlog文件中（例如：UPDATESETfirstname='Tom'WHEREid=293;）。这样虽然节省了binlog文件的大小，但是在复制过程中可能会出现问题。因此，对于Redshift复制场景，请使用行格式。这种格式会将更改后的值保存在binlog文件中。虽然增加了binlog文件的大小，但是可以保证MySQL和Redshift之间的数据一致性。log_bin设置存放binlog文件的路径。expire_logs_days确定binlog文件保留的天数。我们建议将二进制日志文件保留几天，以确保有时间解决复制过程中出现的任何问题。replicate-wild-do-table参数指定要复制的表。即只有那些指定的表才能进入binlog文件。如果使用MySQL的从服务器（slaveserver）作为复制源，需要设置log-slave-updates为TRUE。否则，在主复制服务器上所做的数据更改将不会记录在binlog中。另外，你的MySQL账号还需要有以下权限才能执行复制相关的任务：Replicationslaveselectreloadreplicationclientlocktable2.导出和转换当你使用binlog时，需要导出的其实是MySQL的实时数据每个二进制日志文件的流。binlog数据的具体传递方式取决于你使用的API。例如，对于Kodama，binlog数据以binlog事件流的形式传递。也就是说，Kodama允许您为不同的事件类型（插入、更新、删除、更改表、创建表等）注册事件处理程序。应用程序收到binlog事件后，将生成一个输出，用于将数据更改导入Redshift，或包括表结构架构更改。与其他复制方法不同，binlogmutation方法需要您专门处理那些已被删除的事件，以保持Redshift的上传性能。3.导入为了将binlog数据流上传导入Redshift，我们需要借用增量SELECT和COPY方法中提到的DELSERT导入技术。Binlog的缺点Binlog方式虽然可以满足MySQL到Redshift的数据复制需求，但是需要花时间开发CDC应用。即除了上面提到的数据流，还必须构建：事务管理。跟踪数据流的性能，防止应用在读取binlog数据时报错中止，从上次中断继续。数据缓冲和重试。为避免应用程序发送数据时Redshift不可用，导致数据丢失或重复，您应该尽量让应用程序缓冲未发送的数据，直到Redshift集群重新上线。支持对表结构架构的更改。Binlog中表结构方式的变化事件（如：改表、增表、删表）往往以MySQL原生SQL语句的形式出现。但是，它不能直接在Redshift上运行，而是需要尝试将这种从MySQL语句的修改转化为对应的Redshift语句。4.将ETL用作服务使用ETL工具，您可以近乎实时地将数据复制到Redshift中。与CDC方法不同，此类工具可以管理整个复制过程并自动将MySQL数据类型映射到Redshift使用的格式。您甚至可以同时将多个MySQL数据库（和其他类型的数据库）同步到Redshift。由于设置过程非常简单，这里不再赘述。总结总之，结合使用MySQL和Redshift可以为您的BI需求提供一个简单但功能强大的解决方案。上面列出的四种将数据从MySQL复制到Redshift的方法从简单到复杂，从非常慢到接近实时。具体如何选择，请参考以下三个因素：复制频率MySQL数据集的大小可用的开发资源其中，最快最可靠的复制方式是：ChangeDataCapture(CDC)usingMySQLbinlog。但是，缺点是开发人员需要花费时间来构建和维护应用程序。因此，您需要根据您的实际业务目标和数据分析需求做出明智的决策。原标题：MySQLtoRedshift:4WaystoReplicateYourData，作者：BenPutano
