将数据从 Amazon Aurora MySQL 数据库集群保存到 Amazon S3 存储桶中的文本文件
您可以使用 SELECT INTO OUTFILE S3
语句从 Amazon Aurora MySQL 数据库集群中查询数据,并将数据保存到 Amazon S3 存储桶中存储的文本文件。在 Aurora MySQL 中,文件首先存储在本地磁盘上,然后导出到 S3。导出完成后,将删除本地文件。
您可以使用 Amazon S3 管理密钥(SSE-S3)或 Amazon KMS key(SSE-KMS:Amazon 托管式密钥或客户管理密钥) 对 Amazon S3 桶进行加密。
LOAD DATA FROM S3
语句可以使用 SELECT INTO
OUTFILE S3
语句创建的文件将数据加载到 Aurora 数据库集群中。有关更多信息,请参阅 将数据从 Amazon S3 存储桶中的文本文件加载到 Amazon Aurora MySQL 数据库集群。
注意
Aurora Serverless v1 数据库集群不支持此功能。Aurora Serverless v2 数据库集群支持此功能。
也可以使用 Amazon Web Services Management Console、Amazon CLI 或 Amazon RDS API 将数据库集群数据和数据库集群快照数据保存到 Amazon S3。有关更多信息,请参阅将数据库集群数据导出到 Amazon S3 和将数据库集群快照数据导出到 Amazon S3。
目录
为 Aurora MySQL 授予 Amazon S3 的访问权限
在将数据保存到 Amazon S3 存储桶之前,您必须先为 Aurora MySQL 数据库集群授予 Amazon S3 的访问权限。
为 Amazon S3 授予 Aurora MySQL 的访问权限
-
创建一个 Amazon Identity and Access Management(IAM)策略,以提供允许 Aurora MySQL 数据库集群访问 Amazon S3 存储桶和对象的权限。有关说明,请参阅创建 IAM 策略以访问 Amazon S3 资源。
注意
在 Aurora MySQL 版本 3.05 及更高版本中,您可以使用 Amazon KMS 客户管理密钥对对象进行加密。为此,请在您的 IAM policy 中包含
kms:GenerateDataKey
权限。有关更多信息,请参阅 创建 IAM 策略以访问 Amazon KMS 资源。您不需要此权限即可使用 Amazon 托管式密钥或 Amazon S3 管理密钥(SSE-S3)来加密对象。
-
创建一个 IAM 角色,并将您在 创建 IAM 策略以访问 Amazon S3 资源 中创建的 IAM 策略附加到新的 IAM 角色。有关说明,请参阅创建 IAM 角色以允许 Amazon Aurora 访问Amazon服务。
-
对于 Aurora MySQL 版本 2,将
aurora_select_into_s3_role
或aws_default_s3_role
数据库集群参数设置为新 IAM 角色的 Amazon 资源名称(ARN)。如果没有为aurora_select_into_s3_role
指定 IAM 角色,则 Aurora 使用在aws_default_s3_role
中指定的 IAM 角色。对于 Aurora MySQL 版本 3,使用
aws_default_s3_role
。如果集群是 Aurora 全局数据库的一部分,则为该全局数据库中的每个 Aurora 集群设置此参数。
有关数据库集群参数的更多信息,请参阅Amazon Aurora 数据库集群和数据库实例参数。
-
要允许 Aurora MySQL 数据库集群中的数据库用户访问 Amazon S3,请将您在创建 IAM 角色以允许 Amazon Aurora 访问Amazon服务中创建的角色与该数据库集群关联。
对于 Aurora 全局数据库,将此角色与该全局数据库中的每个 Aurora 集群关联。
有关将 IAM 角色与数据库集群关联的信息,请参阅将 IAM 角色与 Amazon Aurora MySQL 数据库集群关联。
-
配置 Aurora MySQL 数据库集群以允许建立到 Amazon S3 的出站连接。有关说明,请参阅启用从 Amazon Aurora 到其它 Amazon 服务的网络通信。
对于 Aurora 全局数据库,为该全局数据库中的每个 Aurora 集群启用出站连接。
授予权限以在 Aurora MySQL 中保存数据
发出 SELECT INTO OUTFILE S3
语句的数据库用户必须具有特定角色或权限。在 Aurora MySQL 版本 3 中,您可以授予 AWS_SELECT_S3_ACCESS
角色。在 Aurora MySQL 版本 2 中,您可以授予 SELECT
INTO S3
权限。预设情况下,将为数据库集群的管理用户授予适当的角色或权限。您可以使用以下语句之一向另一个用户授予权限。
对 Aurora MySQL 版本 3 使用以下语句:
GRANT AWS_SELECT_S3_ACCESS TO '
user
'@'domain-or-ip-address
'
提示
当您使用 Aurora MySQL 版本 3 中的角色方法时,还可以通过使用 SET ROLE
或 role_name
SET ROLE
ALL
语句来激活角色。如果您不熟悉 MySQL 8.0 角色系统,可以在基于角色的权限模型中了解详情。有关更多详细信息,请参阅《MySQL 参考手册》中的 Using roles
这仅适用于当前的活动会话。当您重新连接时,必须再次运行 SET ROLE
语句来授予权限。有关更多信息,请参阅《MySQL 参考手册》中的 SET ROLE 语句
可以使用 activate_all_roles_on_login
数据库集群参数,在用户连接到数据库实例时自动激活所有角色。设置此参数后,您通常不必显式调用 SET ROLE
语句,即可激活角色。有关更多信息,请参阅《MySQL 参考手册》中的 activate_all_roles_on_login
但是,当存储过程由其他用户调用时,您必须在该存储过程的开头显式调用 SET ROLE ALL
才能激活该角色。
对 Aurora MySQL 版本 2 使用以下语句:
GRANT SELECT INTO S3 ON *.* TO '
user
'@'domain-or-ip-address
'
AWS_SELECT_S3_ACCESS
角色和 SELECT INTO S3
权限特定于 Amazon Aurora MySQL,而不适用于 MySQL 数据库或 RDS for MySQL 数据库实例。如果您在作为复制源的 Aurora MySQL 数据库集群和作为复制客户端的 MySQL 数据库之间设置了复制,则角色或权限的 GRANT
语句将导致复制停止并出现错误。您可以安全地跳过该错误,继续复制。要跳过 RDS for MySQL 数据库实例上的错误,请使用 mysql_rds_skip_repl_error 过程。要跳过外部 MySQL 数据库上的错误,请使用 slave_skip_errors
指定 Amazon S3 存储桶的路径
用于指定在 Amazon S3 存储桶上存储数据和清单文件的路径的语法与在 LOAD DATA FROM S3 PREFIX
语句中使用的语法类似,如下所示。
s3
-region
://bucket-name
/file-prefix
路径包括以下值:
-
region
(可选)– 包含用来存储数据的 Amazon S3 存储桶的Amazon区域。该值为可选项。如果您没有指定region
值,则 Aurora 将文件保存到与您的数据库集群位于相同区域的 Amazon S3 中。 -
bucket-name
– 要将数据保存到的 Amazon S3 存储桶的名称。支持指定虚拟文件夹路径的对象前缀。 -
file-prefix
– 指定要保存到 Amazon S3 的文件的 Amazon S3 对象前缀。
SELECT INTO OUTFILE S3
语句创建的数据文件使用以下路径,其中 00000
表示从零开始的 5 位整数。
s3
-region
://bucket-name
/file-prefix
.part_00000
例如,假设 SELECT INTO OUTFILE S3
语句指定 s3-us-west-2://bucket/prefix
作为存储数据文件的路径并创建三个数据文件。指定的 Amazon S3 存储桶包含以下数据文件。
-
s3-us-west-2://bucket/prefix.part_00000
-
s3-us-west-2://bucket/prefix.part_00001
-
s3-us-west-2://bucket/prefix.part_00002
创建清单以列出数据文件
您可以使用 SELECT INTO OUTFILE S3
语句和 MANIFEST ON
选项,以 JSON 格式创建列出由语句创建的文本文件的清单文件。LOAD DATA FROM S3
语句可以使用清单文件将数据文件加载回 Aurora MySQL 数据库集群中。有关使用清单将数据文件从 Amazon S3 加载到 Aurora MySQL 数据库集群的更多信息,请参阅使用清单指定要加载的数据文件。
清单中包含的由 SELECT INTO OUTFILE
S3
语句创建的数据文件按照它们由语句创建的顺序列出。例如,假设 SELECT INTO OUTFILE S3
语句指定 s3-us-west-2://bucket/prefix
作为存储数据文件的路径并创建三个数据文件和一个清单文件。指定的 Amazon S3 存储桶包含一个名为 s3-us-west-2://bucket/prefix.manifest
的清单文件,其中包含以下信息。
{ "entries": [ { "url":"s3-us-west-2://bucket/prefix.part_00000" }, { "url":"s3-us-west-2://bucket/prefix.part_00001" }, { "url":"s3-us-west-2://bucket/prefix.part_00002" } ] }
SELECT INTO OUTFILE S3
您可以使用 SELECT INTO OUTFILE S3
语句从数据库集群中查询数据,并将数据直接保存到 Amazon S3 存储桶中存储的带分隔符的文本文件。
不支持压缩文件。从 Aurora MySQL 版本 2.09.0 开始支持加密文件。
语法
SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
select_expr
[,select_expr
...] [FROMtable_references
[PARTITIONpartition_list
] [WHEREwhere_condition
] [GROUP BY {col_name
|expr
|position
} [ASC | DESC], ... [WITH ROLLUP]] [HAVINGwhere_condition
] [ORDER BY {col_name
|expr
|position
} [ASC | DESC], ...] [LIMIT {[offset
,]row_count
|row_count
OFFSEToffset
}] INTO OUTFILE S3 's3_uri
' [CHARACTER SETcharset_name
] [export_options
] [MANIFEST {ON | OFF}] [OVERWRITE {ON | OFF}] [ENCRYPTION {ON | OFF | SSE_S3 | SSE_KMS ['cmk_id
']}]export_options
: [FORMAT {CSV|TEXT} [HEADER]] [{FIELDS | COLUMNS} [TERMINATED BY 'string
'] [[OPTIONALLY] ENCLOSED BY 'char
'] [ESCAPED BY 'char
'] ] [LINES [STARTING BY 'string
'] [TERMINATED BY 'string
'] ]
参数
SELECT INTO OUTFILE S3
语句使用以下特定于 Aurora 的必需和可选参数。
- s3-uri
-
为要使用的 Amazon S3 前缀指定 URI。使用指定 Amazon S3 存储桶的路径中所述的语法。
- FORMAT {CSV|TEXT} [HEADER]
-
(可选)以 CSV 格式保存数据。
TEXT
选项是默认选项,并生成现有的 MySQL 导出格式。CSV
选项会生成逗号分隔的数据值。CSV 格式遵循 RFC-4180规范。如果指定可选关键字 HEADER
,则输出文件包含一个标题行。标题行中的标签与SELECT
语句中的列名称相对应。您可以将 CSV 文件用于训练数据模型,以便与 Amazon ML 服务一起使用。有关将导出的 Aurora 数据与 Amazon ML 服务结合使用的更多信息,请参阅将数据导出到 Amazon S3 以进行 SageMaker 模型训练(高级)。 - MANIFEST {ON | OFF}
-
指示是否在 Amazon S3 中创建清单文件。清单文件是 JavaScript 对象表示法 (JSON) 文件,可用于通过
LOAD DATA FROM S3 MANIFEST
语句将数据加载到 Aurora 数据库集群中。有关LOAD DATA FROM S3 MANIFEST
的更多信息,请参阅将数据从 Amazon S3 存储桶中的文本文件加载到 Amazon Aurora MySQL 数据库集群。如果在查询中指定了
MANIFEST ON
,则在创建并上传所有数据文件后在 Amazon S3 中创建清单文件。使用以下路径创建清单文件:s3
-region
://bucket-name
/file-prefix
.manifest有关清单文件的内容格式的更多信息,请参阅 创建清单以列出数据文件。
- OVERWRITE {ON | OFF}
-
指示是否覆盖指定的 Amazon S3 桶中的现有文件。如果指定
OVERWRITE ON
,则覆盖与在s3-uri
中指定的 URI 内的文件前缀匹配的现有文件。否则将出错。 - ENCRYPTION {ON | OFF | SSE_S3 | SSE_KMS ['
cmk_id
']} -
指示是使用 Amazon S3 管理密钥(SSE-S3)还是使用 Amazon KMS keys(SSE-KMS,包括 Amazon 托管式密钥和客户管理密钥)进行服务器端加密。
SSE_S3
和SSE_KMS
设置适用于 Aurora MySQL 版本 3.05 及更高版本。您也可以使用
aurora_select_into_s3_encryption_default
会话变量代替ENCRYPTION
子句,如以下示例所示。使用 SQL 子句或会话变量,但不能同时使用这两者。set session set session aurora_select_into_s3_encryption_default={ON | OFF | SSE_S3 | SSE_KMS};
SSE_S3
和SSE_KMS
设置适用于 Aurora MySQL 版本 3.05 及更高版本。将
aurora_select_into_s3_encryption_default
设置为以下值时:-
OFF
– 遵循 S3 桶的默认加密策略。aurora_select_into_s3_encryption_default
的默认值为OFF
。 -
ON
或SSE_S3
– 使用 Amazon S3 管理密钥(SSE-S3)对 S3 对象进行加密。 -
SSE_KMS
– 则使用 Amazon KMS key加密 S3 对象。在这种情况下,您还需要包含会话变量
aurora_s3_default_cmk_id
,例如:set session aurora_select_into_s3_encryption_default={SSE_KMS}; set session aurora_s3_default_cmk_id={NULL | '
cmk_id
'};-
如果
aurora_s3_default_cmk_id
是NULL
,则使用加密 S3 对象Amazon 托管式密钥。 -
如果
aurora_s3_default_cmk_id
为非空字符串cmk_id
,则使用客户管理密钥加密 S3 对象。cmk_id
值不能为空字符串。
-
当您使用
SELECT INTO OUTFILE S3
命令时,Aurora 按如下方式决定加密:-
如果
ENCRYPTION
子句存在于 SQL 命令中,则 Aurora 仅依赖ENCRYPTION
的值,而不使用会话变量。 -
如果
ENCRYPTION
子句不存在,则 Aurora 依赖于会话变量的值。
有关更多信息,请参阅《Amazon Simple Storage Service 用户指南》中的使用 Amazon S3 管理密钥(SSE-S3)进行服务器端加密和使用 Amazon KMS 密钥(SSE-KMS)进行服务器端加密。
-
在 MySQL 文档的 SELECT 语句
注意事项
写入到 Amazon S3 存储桶的文件数取决于 SELECT INTO OUTFILE S3
语句选择的数据数量以及 Aurora MySQL 的文件大小阈值。默认文件大小阈值为 6GB。如果语句选择的数据小于文件大小阈值,则将创建单个文件;否则将创建多个文件。该语句所创建文件的其他注意事项包括下列内容:
-
Aurora MySQL 确保数据文件中的行不会跨文件边界拆分。对于多个文件,除了最后一个文件之外,每个数据文件的大小通常接近文件大小阈值。不过,有时保持在文件大小阈值之下会导致在两个数据文件之间拆分某一行。在这种情况下,Aurora MySQL 创建一个保留该行而不拆分的数据文件,但可能会大于文件大小阈值。
-
由于 Aurora MySQL 中的每个
SELECT
语句作为原子事务运行,选择大数据集的SELECT INTO OUTFILE S3
语句可能需要一段时间来运行。如果语句由于任何原因失败,您可能需要重新启动和发出该语句。不过,如果语句失败,已上传到 Amazon S3 的文件将保留在指定的 Amazon S3 存储桶中。您可以使用其他语句来上传剩余的数据而不用重新启动。 -
如果要选择的数据量很大(超过 25 GB),我们建议您使用多个
SELECT INTO OUTFILE S3
语句以将数据保存到 Amazon S3 中。每个语句应选择不同部分的数据来保存,并且在保存数据文件时指定使用file_prefix
参数中的不同s3-uri
。使用多个语句对要选择的数据进行分区可以更轻松地从一个语句中的错误中恢复。如果一条语句发生错误,则只需要重新选择部分数据并将其上传到 Amazon S3 即可。使用多个语句还有助于避免单个长时间运行的事务,这可改进性能。 -
如果多个
SELECT INTO OUTFILE S3
语句在file_prefix
参数中使用相同的s3-uri
,并且并行运行这些语句以选择数据并上传到 Amazon S3 中,则行为是不确定的。 -
Aurora MySQL 不会将元数据(例如,表架构或文件元数据)上传到 Amazon S3 中。
-
有时,您可以重新运行
SELECT INTO OUTFILE S3
查询,以便从故障中恢复。在这些情况下,您必须在 Amazon S3 存储桶中删除具有在s3-uri
中指定的文件前缀的任何现有数据文件,或者在OVERWRITE ON
查询中包括SELECT INTO OUTFILE S3
。
SELECT INTO OUTFILE S3
语句返回一个典型的 MySQL 错误编号以及有关成功或失败的响应。如果您无权访问 MySQL 错误编号和响应,则确定何时才完成的最简单方法是在语句中指定 MANIFEST ON
。清单文件是该语句写入的最后一个文件。换而言之,如果您具有清单文件,则语句已完成。
目前,没有其他方法可以在运行期间直接监视 SELECT
INTO OUTFILE S3
语句的进度。不过,假设您使用该语句将大量数据从 Aurora MySQL 写入到 Amazon S3 中,并且您知道语句选择的数据大小。在这种情况下,您可以通过监视 Amazon S3 中数据文件的创建操作来估算进度。
为此,您可以利用这样一个常识,也就是每次当语句选择大约 6GB 数据时,将在指定 Amazon S3 存储桶中创建一个数据文件。将选定数据的大小除以 6GB 可以估计要创建的数据文件数。您可以通过监视在语句运行期间上传到 Amazon S3 的文件数来估算语句的进度。
示例
以下语句选择 employees
表中的所有数据并将数据保存到与 Aurora MySQL 数据库集群位于不同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,
) 字符终止,每行以换行 (\n
) 字符终止。如果在指定的 Amazon S3 存储桶中存在与 sample_employee_data
文件前缀匹配的文件,该语句将返回错误。
SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
以下语句选择 employees
表中的所有数据,并将数据保存到与 Aurora MySQL 数据库集群位于相同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,
) 字符终止,每行以换行 (\n
) 字符终止,还创建一个清单文件。如果在指定的 Amazon S3 存储桶中存在与 sample_employee_data
文件前缀匹配的文件,该语句将返回错误。
SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON;
以下语句选择 employees
表中的所有数据并将数据保存到与 Aurora 数据库集群位于不同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,
) 字符终止,每行以换行 (\n
) 字符终止。该语句在指定的 Amazon S3 存储桶中覆盖与 sample_employee_data
文件前缀匹配的任何现有文件。
SELECT * FROM employees INTO OUTFILE S3 's3-us-west-2://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' OVERWRITE ON;
以下语句选择 employees
表中的所有数据,并将数据保存到与 Aurora MySQL 数据库集群位于相同区域的 Amazon S3 存储桶中。该语句创建数据文件,其中每个字段以逗号 (,
) 字符终止,每行以换行 (\n
) 字符终止,还创建一个清单文件。该语句在指定的 Amazon S3 存储桶中覆盖与 sample_employee_data
文件前缀匹配的任何现有文件。
SELECT * FROM employees INTO OUTFILE S3 's3://aurora-select-into-s3-pdx/sample_employee_data' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;