mysql怎么复制表里数据 mysql复制表结构和数据

目前，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)。不过其缺点是需要开发人员花时间去构建和维护应用程序。因此，您需要根据实际业务目标和数据分析需求，来做出明智的决定。