Amazon Relational Database Service
用户指南 (API Version 2014-10-31)
AWS 服务或AWS文档中描述的功能,可能因地区/位置而异。请点击 Amazon AWS 入门,可查看中国地区的具体差异

从 MySQL 数据库实例导入和导出数据

建议使用本节中的过程将数据导入到 MySQL 数据库实例中或从该实例中导出数据。您可以使用这些过程从其他 MySQL 数据库实例、在 Amazon RDS 外部运行的 MySQL 实例以及其他类型的数据源导入数据。要使用复制功能将数据导出到在 Amazon RDS 外部运行的 MySQL 实例,建议使用使用复制导出 MySQL 数据中所讨论的过程。

概述

在所述的情况下,推荐以下用于将数据导入到 MySQL 数据库实例中的过程:

  • 您可能能够使用 AWS Database Migration Service 以最有效方式迁移您的数据。AWS DMS 可在最少停机时间下迁移数据库,对于许多数据库引擎,将继续进行持续复制,直到您已准备好切换到您的 MySQL 数据库实例。您可以使用 AWS DMS 从非 MySQL 数据库引擎迁移到 Amazon RDS MySQL 数据库实例,或者执行 MySQL 数据库的部分迁移。如果您正在从其他数据库引擎迁移到 MySQL,则可使用 AWS Schema Conversion Tool 迁移未由 AWS DMS 迁移的架构对象。有关 AWS DMS 的更多信息,请参阅什么是 AWS Database Migration Service

    如果满足以下所有条件,建议您不要使用 AWS DMS,而是使用 MySQL 数据库迁移工具:

    • 您具有一个同类迁移,其中,您将从 MySQL 数据库迁移到 Amazon RDS MySQL 数据库实例。

    • 您正在迁移整个数据库。

      如果您正在将数据的子集从 MySQL 数据库迁移到 Amazon RDS,则 AWS DMS 是一个很好的选项。但是,当迁移整个数据库时,AWS DMS 会创建表、主键和 (在某些情况下) 唯一索引,但它不会创建有效迁移源中的数据时不需要的任何其他对象。例如,它不会创建二级索引、非主键约束或数据默认值。如果您正在迁移完整数据库,则可以将架构复制到 RDS MySQL 数据库实例,然后使用 AWS DMS 迁移数据,或者使用本机 MySQL 迁移工具 (稍后将在本主题中讨论) 迁移数据。

    • 使用 MySQL 数据库迁移工具可减少要迁移数据库所需的停机时间量。有关示例请查看 将数据导入到 Amazon RDS MySQL 或 MariaDB 数据库实例中,同时减少停机时间

  • 要从 MySQL 数据库实例中的现有数据库导入数据,可创建只读副本,然后提升该只读副本。有关更多信息,请参阅 使用 PostgreSQL、MySQL 和 MariaDB 只读副本

  • 要移动少量 MySQL 数据 (或源 MySQL 数据库不会发生服务中断的情况下),您可以使用命令行实用工具通过一个简单的过程直接将数据复制到 Amazon RDS MySQL 数据库实例。有关更多信息,请参阅 将数据从 MySQL 或 MariaDB 数据库导入到 Amazon RDS MySQL 或 MariaDB 数据库实例

  • 要移动大量 MySQL 数据 (或当您需要最大程度地减少使用外部 MySQL 实例的实时网站或应用程序的服务中断时),您可以备份数据,将数据复制到 Amazon Elastic Compute Cloud (Amazon EC2),并导入 Amazon RDS MySQL 数据库实例中。然后,您可以使用复制功能,针对自复制到 Amazon EC2 后添加到源系统中的所有数据将两个实例同步。有关 将数据导入到 Amazon RDS MySQL 或 MariaDB 数据库实例中,同时减少停机时间.

  • 如果数据的来源并不是现有的 MySQL 数据库,您可以创建平面文件并使用 mysqlimport 实用工具导入这些文件。有关更多信息,请参阅 从任意源将数据导入 MySQL 或 MariaDB 数据库实例

  • 要通过将现有的 MySQL 数据库实例用作复制主体来设置复制功能,请参阅利用 Amazon RDS 外部运行的 MySQL 或 MariaDB 实例进行复制

注意

“mysql”系统数据库包含登录到数据库实例并访问数据所需的身份验证和授权信息。在数据库实例中删掉、改动、重命名或截取“mysql”数据库中的表、数据或其他内容可能会导致错误,并可能导致无法访问数据库实例和数据。如果出现此情况,可使用 AWS CLI restore-db-instance-from-db-snapshot 命令从快照还原数据库实例,或使用 AWS CLI restore-db-instance-to-point-in-time 命令进行恢复。

导入数据的注意事项

本部分包含有关将数据加载到 MySQL 中的其他技术信息。它面向熟悉 MySQL 服务器架构的高级用户。请注意,与 LOAD DATA LOCAL INFILE 相关的所有备注也适用于 mysqlimport

二进制日志

与关闭二进制日志记录后加载相同数据的情况相比,启用二进制日志记录后加载数据会导致性能损失,并需要额外的可用磁盘空间 (高达 4 倍以上)。性能损失的严重程度和所需的可用磁盘空间量与加载数据所使用的事务大小成正比。

事务大小

事务大小在 MySQL 数据加载中起着重要的作用。它对资源消耗、磁盘空间利用率、恢复过程、恢复时间和输入格式 (平面文件或 SQL) 有重大影响。本部分介绍了事务大小如何影响二进制日志记录以及让案例在大数据加载过程中禁用二进制日志记录。如前文所述,通过设置 Amazon RDS 自动备份保留期,可启用和禁用二进制日志记录。非零值会启用二进制日志记录,零会将其禁用。我们还介绍了大型事务对 InnoDB 的影响以及将事务大小保持在较小状态的重要性。

小型事务

对于小型事务,二进制日志记录会使加载数据所需的磁盘写入次数翻倍。根据上传速率、加载期间出现的其他数据库活动以及 Amazon RDS 数据库实例的容量,这种情况会严重降低其他数据库会话的性能并增加数据加载所需的时间。

此外,二进制日志占用的磁盘空间大致等于数据备份和移除前加载的数据量。幸好 Amazon RDS 会频繁备份并删除二进制日志,以尽量减少这种情况。

大型事务

大型事务对启用二进制日志记录的 IOPS 和磁盘占用量的不良影响可达 3 倍。这是因为二进制日志缓存会溢到磁盘,占用磁盘空间并会为每次写入增加额外 IO。事务提交或回滚前,无法将缓存写入二进制日志,因此其会根据数据加载量按比例占用磁盘空间。提交事务后,必须将缓存复制到二进制日志,为磁盘上的数据创建第三副本。

因此,启用二进制日志记录后加载数据所使用的可用磁盘空间必须至少为禁用二进制日志记录加载过程的三倍。例如,在加载过程中,作为单个事务加载的 10 GB 数据将至少占用 30 GB 的磁盘空间:表 10 GB + 二进制日志缓存 10 GB + 二进制日志本身 10 GB。缓存文件会一直保存在磁盘上,直至创建缓存文件的会话终止或在另一事务处理期间会话再次填充其二进制日志缓存。备份前二进制日志必须保存在磁盘上,因此可能要过一段时间才会释放额外的 20 GB 空间。

在使用 LOAD DATA LOCAL INFILE 加载数据的情况下,如果数据库必须从加载前创建的备份中恢复,请为数据创建另一个副本。在恢复期间,MySQL 将二进制日志中的数据提取到平面文件中,然后执行 LOAD DATA LOCAL INFILE,如同原始事务一样,只是这次的输入文件在数据库服务器本地。继续上述示例,恢复将失败,除非可以使用的可用磁盘空间至少达到 40 GB。

禁用二进制日志记录

在可能的情况下,在大数据加载过程中禁用二进制日志记录,以避免资源开销和增加磁盘空间要求。在 Amazon RDS 中,只需将备份保留期设置为零就可以禁用二进制日志记录。如果执行此操作,建议您在即将加载前为数据库实例拍摄数据库快照,以便在需要时可以快速轻松地在加载过程中撤消做出的更改。

加载后,将备份保留期设置回相应 (非零) 值。

如果数据库实例是只读副本的源数据库实例,您就无法将备份保留期设置为零。

InnoDB

本部分中的信息为使用 InnoDB 时保持小型事务提供了有力论据。

撤消

InnoDB 可以生成撤消以支持事务回滚和 MVCC 等功能。撤消可存储在 InnoDB 系统表空间 (通常为 ibdata1) 中,并且在清除线程移除前会一直保留。清除线程无法超越最早的活动事务撤消,因此直到此事务提交或完成回滚后才能有效地阻止此线程。如果在加载期间数据库正在处理其他事务,则其撤消同样会在系统表空间中累计并且无法移除,即使它们已提交并且其他事务不需要撤消 MVCC。在这种情况下,访问由任一事务 (不仅仅是负载事务) 更改的任一行的所有事务 (包括只读事务) 都会减速,因为它们会通过撤消进行扫描,而这些撤消如果不适用于长期运行的负载事务,就可能会被清除。

由于撤消存储于系统表空间中,并且此系统表空间的规模不会缩小,因而大数据负载事务可能会导致系统表空间变得很大,如果不从头开始重新创建数据库,这些占用的磁盘空间就无法回收。

回滚

InnoDB 已针对提交进行了优化。回滚大型事务可能需要很长时间。在一些情况下,执行时间点恢复或还原数据库快照可能会更快。

输入数据格式

MySQL 可以接受以下两种传入数据格式之一:平面文件和 SQL。本部分说明了每一种格式的一些主要优势和劣势。

平面文件

只要将事务保持在相对较小的状态,使用 LOAD DATA LOCAL INFILE 加载平面文件就是最快速、最经济的数据加载方式。由于减少了数据库中的开销,因而与使用 SQL 加载相同的数据相比,平面文件需要的网络流量通常更少、传输成本更低并且加载速度快得多。

一个大事务

LOAD DATA LOCAL INFILE 会将整个平面文件作为一个事务进行加载。这不一定是一件坏事。如果可以将单个文件大小保持在较小状态,会有很多优势:

  • 恢复功能 – 易于跟踪已加载的文件。如果在加载期间出现问题,只需简要操作就可以从停止处继续加载。可能要将一些数据重新传送到 Amazon RDS,但使用小文件,就可以将重新传输的量减少至最低水平。

  • 并行加载数据 – 如果 IOP 和网络带宽足以支持单一文件的加载,那么并行加载就可以节省时间。

  • 限制负载率 – 影响其他进程的数据加载?通过增加文件之间的时间间隔限制负载。

注意

LOAD DATA LOCAL INFILE 的优势会随着事务大小的增加而迅速减弱。如果无法将一个大数据集分成几个较小的数据集,SQL 可能是更好的选择。

SQL

与平面文件相比,SQL 的一个主要优势为:易于将事务大小保持在较小状态。但是,SQL 的加载时间显著长于平面文件,并且在发生故障后,难以确定加载恢复点。例如,Mysqldump 文件不可重启。如果加载 Mysqldump 文件时出现故障,需要在加载恢复前修改或替换该文件。另一种方法是在纠正故障后,还原到加载前的时间点,然后重播此文件。

使用 Amazon RDS 快照采集检查点

如果加载将持续数小时甚至几天时间,那么无二进制日志记录加载过程的前景并不非常诱人,除非您可以采集定期检查点。这就是 Amazon RDS 数据库快照功能带来的极大便利之处。数据库快照会创建一个时间点一致的数据库实例副本,其可用于将数据库还原到发生崩溃或其他事故前的时间点。

要创建检查点,只需拍摄数据库快照。可移除为检查点拍摄的先前任一数据库快照,而不会影响耐久性或还原时间。

拍摄快照的速度也很快,因此频繁地检验检查点不会显著增加加载时间。

减少加载时间

这里有一些可以减少加载时间的其他技巧:

  • 加载前创建所有辅助索引。对于熟悉其他数据库的人而言,这样做违反常规。添加或修改辅助索引会使 MySQL 创建一个指数发生变化的新表,将数据从现有表复制到新表并删掉原始表。

  • 以 PK 顺序加载数据。这对 InnoDB 表尤为有用,其中加载时间可减少 75 – 80%,数据文件大小可减小一半。

  • 禁用外键约束 foreign_key_checks=0。对于使用 LOAD DATA LOCAL INFILE 加载的平面文件,在许多情况下都需要这一操作。对于任何加载过程,禁用 FK 检查都会提供显著的性能收益。请务必确保启用约束并验证加载后的数据。

  • 并行加载,除非已经接近资源限制。适当时使用分区表。

  • 加载时,通过 SQL 使用多值插入,将语句的执行开销降到最低。使用 Mysqldump 时,这会自动完成。

  • 减少 InnoDB 日志 IO innodb_flush_log_at_trx_commit=0

注意

使用 innodb_flush_log_at_trx_commit=0 会导致 InnoDB 每秒都刷新其日志,而不是在每次提交时刷新。这提供了很大的速度优势,但在崩溃期间可能导致数据丢失。请谨慎使用。