导入和导出数据的最佳做法

MySQL   |  PostgreSQL   |  SQL Server

本页面介绍了使用 Cloud SQL 导入和导出数据的最佳做法。如需了解将数据导入 Cloud SQL 的分步说明，请参阅导入数据。如需了解导出数据（无论是位于 Cloud SQL 中的数据还是位于您所管理实例中的数据）的分步说明，请参阅导出数据。

导入和导出最佳做法

以下是导入和导出数据时要考虑的最佳做法：

• 请勿使用 Cloud Storage 请求者付款存储桶
• 压缩数据以减少费用。
• 减少长时间运行的导入和导出过程
• 使用 bcp 实用程序导入和导出数据
• 使用批量插入导入数据
• 使用 SqlPackage 导入和导出数据
• 使用条带式导入和导出
• 验证导入的数据库

注意：在 Cloud SQL 中，SQL Server 目前支持以下导入和导出操作：

• 使用 BAK 和 SQL 文件导入数据库。
• 使用 BAK 文件导出数据库。

请勿使用 Cloud Storage 请求者付款存储桶

您无法使用启用了请求者付款功能的 Cloud Storage 存储桶来将数据导入 Cloud SQL 以及从 Cloud SQL 导出数据。

压缩数据以减少费用

Cloud SQL 支持导入和导出压缩文件及未压缩文件。压缩文件可以节省 Cloud Storage 上的大量存储空间并减少存储费用，这在导出大型实例时尤为明显。

注意：压缩可能会导致导出性能下降。

导出 BAK 文件时，请使用 .gz 文件扩展名来压缩数据。导入扩展名为 .gz 的文件时，系统会自动将其解压缩。

减少长时间运行的导入和导出过程

导入和导出 Cloud SQL 可能需要很长时间才能完成，具体取决于要处理的数据大小。这可能产生以下影响：

• 您无法停止长时间运行的 Cloud SQL 实例操作。
• 针对每个实例，一次只能执行一项导入或导出操作，而长时间运行的导入或导出操作会阻止其他操作，例如每日自动备份。

对小批量数据使用 Cloud SQL 导入或导出功能可以缩短每项操作的完成时间。

对于整个数据库迁移，您通常应使用 BAK 文件而不是 SQL 文件进行导入。通常，从 SQL 文件导入比从 BAK 文件导入花费的时间更长。

使用 SqlPackage 导入和导出数据

您可以使用 SqlPackage 在 Cloud SQL 中导入和导出数据。SqlPackage 可让您将 SQL 数据库（包括数据库架构和用户数据）导出到 BACPAC 文件 (.bacpac)，并将 BACPAC 文件中的架构和表数据导入到新用户数据库。

SqlPackage 会使用您的凭据连接到 SQL Server 来执行数据库导入和导出。它使得所有 Cloud SQL 用户都可以进行迁移。 如需执行导入和导出操作，您必须拥有以下各项：

• 连接到您的实例的工作站，您可以在其中运行 SqlPackage。如需详细了解连接选项，请参阅连接选项简介。

• 您的系统上安装的 SqlPackage。如需详细了解如何下载并安装 SqlPackage，请参阅 Microsoft 文档。

• 为访问实例而设置的凭据。如需详细了解如何设置凭据，请参阅如何向 Cloud SQL 进行身份验证。

重要提示：Cloud SQL 使用您在创建实例时创建的自签名证书。 如需了解详情，请参阅配置 SSL/TLS 证书。如需允许连接到 Cloud SQL 实例，请提供其他 SqlPackage 参数，如下所示：

对于导入：/TargetTrustServerCertificate:True

对于导出：/SourceTrustServerCertificate:True

注意：如果 BACPAC 导入操作包含 Cloud SQL 不支持的对象（例如使用 PolyBase 的数据源），则该操作会失败。

示例

导入

如需将数据导入数据库 AdventureWorks2017，请运行以下命令：

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Import /tsn:myTargetServer /tdn:AdventureWorks2017
/tu:myUsername /sf:mySourceFile
/TargetTrustServerCertificate:True /tp:myPassword

其中，

• mySourceFile 是您要用作本地存储空间操作来源的源文件。如果您使用此参数，则其他来源参数均无效。
• myTargetServer 是托管目标数据库的服务器的名称。
• myUsername 是您要用于访问目标数据库的 SQL Server 用户名。
• myPassword 是凭据中的密码。

如需了解详情，请参阅 Microsoft 文档。

导出

如需从数据库 AdventureWorks2017 导出数据，请运行以下命令：

c:\Program Files\Microsoft SQL Server\160\DAC\bin>SqlPackage
/Action:Export /TargetFile:"myTargetFile"
/ssn:mySourceServer /su:myUsername /sdn:AdventureWorks2017
/SourceTrustServerCertificate:True /sp:myPassword

其中，

• myTargetFile 是您要用作操作目标（而不是数据库）的目标文件（.dacpac 文件）。如果您使用此参数，则其他目标参数均无效。对于仅支持数据库目标的操作，此参数无效。
• myUsername 是您要用于访问源数据库的 SQL Server 用户名。
• mySourceServer 是托管源数据库的服务器的名称。
• myPassword 是凭据中的密码。

如需了解详情，请参阅 Microsoft 文档。

使用 bcp 实用程序导入和导出数据

在 Cloud SQL 中导入和导出数据的另一种方法是使用批量复制程序 (bcp) 实用程序。通过使用 bcp 实用程序，您可以将 SQL Server 数据库中的数据导出到数据文件，并将数据文件中的数据导入到 SQL Server 数据库。 bcp 实用程序会使用您的凭据连接到 SQL Server 来执行数据库导入和导出。它使得所有 Cloud SQL 用户都可以进行转移。如需执行导入和导出操作，您必须拥有以下各项：

• 可以在其中运行 bcp 实用程序并连接到 Cloud SQL 实例的工作站。 如需详细了解连接选项，请参阅连接选项简介。

• 您的系统上安装的 bcp 实用程序。如需详细了解如何下载并安装 bcp，请参阅 Microsoft 文档。

• 为访问实例而设置的凭据。如需详细了解如何设置凭据，请参阅如何向 Cloud SQL 进行身份验证。

示例

导入

如需将 person.csv 文件中的数据导入 AdventureWorks2017 数据库的 Person 表，请运行以下命令：

bcp Person.Person in "person.csv" -d AdventureWorks2017 -U myLoginID -S myServer

其中，

• myLoginID 是用于连接到 SQL Server 的登录 ID。
• myServer 是您要连接到的 SQL Server 实例。如果未指定服务器，则 bcp 实用程序会连接到本地计算机上的默认 SQL Server 实例。

如需了解详情，请参阅 Microsoft 文档。

导出

如需将 AdventureWorks2017 数据库的 Person 表中的数据导出到 person.dat 文件，请运行以下命令：

bcp Person.Person out "person.dat" -U myLoginID -S myServer -d AdventureWorks2017

其中，

• myLoginID 是用于连接到 SQL Server 的登录 ID。
• myServer 是您要连接到的 SQL Server 实例。如果未指定服务器，则 bcp 实用程序会连接到本地计算机上的默认 SQL Server 实例。

如需了解详情，请参阅 Microsoft 文档。

使用批量插入导入数据

批量插入可让您将 Cloud Storage 中存储的文件的数据导入 Cloud SQL for SQL Server 数据库。

本节介绍以下内容：

• 所需的角色和权限
• 使用批量插入时的注意事项
• 执行批量插入
• 查看导入的数据
• 停用批量插入

所需的角色和权限

如需配置批量插入，您需要以下各项：

• 要导入数据的数据库的 CONTROL 权限。

• 映射到具有以下权限的 IAM 账号的 HMAC 访问密钥和密码：

  • storage.buckets.get
  • storage.objects.create 和 storage.multipartUploads.create，用于写入错误日志和错误数据示例。

  或者，您也可以使用以下角色：

  • Storage Object Viewer
  • Storage Object Creator，用于写入错误日志和错误数据示例。

如需使用批量插入，您需要以下各项：

• msdb.dbo.gcloudsql_bulk_insert 存储过程的 EXECUTE 权限。在实例上启用批量插入后，Cloud SQL 会创建存储过程。默认情况下，Cloud SQL 会向 sqlserver 管理员账号授予 EXECUTE 权限。
• 要导入数据的对象的 INSERT 权限。

如需详细了解如何创建用于批量插入的用户，请参阅创建和管理用户。

使用批量插入时的注意事项

本部分提供了在使用批量插入时处理实例上的安全性、性能和可靠性的建议。

安全

Cloud SQL 会加密 HMAC 访问密钥和密码，并将它们在实例中存储为数据库范围凭据。保存后，这些值便无法访问。如需从实例中删除密钥和密码，您可以使用 T-SQL 命令删除数据库范围凭据。如果您在密钥和密码存储在实例上时进行任何备份，则该备份将包含该密钥和密码。您还可以通过停用和删除 HMAC 密钥来使密钥无效。

以下操作可能会意外转移访问密钥和密码，并提供该凭据：

• 克隆实例：密钥和密码在克隆实例上可用。
• 创建读取副本：密钥和密码在创建的读取副本上可用。
• 从备份恢复：密钥和密码在从备份恢复的实例上可用。

我们建议您在执行这些操作后从目标实例中删除密钥和密码。

批量插入可以将它无法解析的数据写入存储在 Cloud Storage 存储桶中的文件。如果您想保护批量插入有权访问的数据，请配置 VPC Service Controls。

性能

我们建议您在使用批量插入时采取以下做法，以降低对性能的影响：

• 测试 @batchsize 并设置适当的值，因为默认情况下，所有数据会在一个批次中导入。
• 对于大型插入，请暂时停用索引，以加快数据插入速度。
• 尽可能使用 @tablock 选项，因为这样可以减少争用并提高数据加载性能。
• 使用 @ordercolumnsjson 参数指定按聚簇索引的顺序排序的数据。这有助于提高实例性能。

可靠性

我们建议您在使用批量插入时采取以下做法，以降低对实例可靠性的影响：

• 如果加载失败并且使用了 @batchsize，则可能会导致数据部分加载。您可能需要在实例上手动清理此数据。
• 使用 @errorfile 选项来保留错误日志以及加载过程中检测到的错误数据的示例。这可以帮助您更轻松地识别加载失败的行。

执行批量插入

您可以使用以下存储过程执行批量插入操作：

msdb.dbo.gcloudsql_bulk_insert

如需了解详情，请参阅用于使用批量插入的存储过程。

示例：从 Cloud Storage 中的文件导入数据并指定错误文件

1. 启用批量插入

如需在实例上启用批量插入，请启用 cloud sql enable bulk insert 标志。

gcloud sql instances patch INSTANCE_NAME --database-flags="cloud sql enable bulk insert"=on

将 INSTANCE_NAME 替换为您要用于批量插入的实例的名称。

如需了解详情，请参阅配置数据库标志。

在实例上启用此标志后，Cloud SQL 会在实例上安装批量插入存储过程，并向 sqlserver 管理员账号授予执行权限。

2. 创建 HMAC 密钥

您需要使用 HMAC 密钥来访问 Cloud Storage 存储桶。我们建议您为服务账号创建 HMAC 密钥，并向该服务账号授予用于批量插入的存储桶的权限。如需了解详情和安全注意事项，请参阅使用批量插入时的注意事项。

3. 创建要导入的示例数据

1. 使用文本编辑器创建一个采用 ANSI 或 UTF-16 编码的文件，其中包含以下示例数据。将文件保存在 Cloud Storage 存储桶中并为其命名，例如 bulkinsert.bcp。

   1,Elijah,Johnson,1962-03-21
   2,Anya,Smith,1982-01-15
   3,Daniel,Jones,1990-05-21
   

2. 使用以下示例数据创建格式文件。将文件保存在 Cloud Storage 存储桶中并为其命名，例如 bulkinsert.fmt。如需详细了解 SQL Server 中的 XML 和非 XML 格式文件，请参阅创建格式文件。

   13.0
   4
   1       SQLCHAR             0       7       ","      1     PersonID               ""
   2       SQLCHAR             0       25      ","      2     FirstName            SQL_Latin1_General_CP1_CI_AS
   3       SQLCHAR             0       30      ","      3     LastName            SQL_Latin1_General_CP1_CI_AS
   4       SQLCHAR             0       11      "\r\n"   4     BirthDate             ""
   

4. 执行存储过程

1. 使用 sqlserver 用户连接到实例，并创建示例数据库和表以进行批量插入。

   USE MASTER
   GO
   -- create test database
   DROP DATABASE IF EXISTS bulktest
   CREATE DATABASE bulktest
   GO
   
   -- create table to insert
   USE bulktest;
   GO
   CREATE TABLE dbo.myfirstimport(
   PersonID smallint,
   FirstName varchar(25),
   LastName varchar(30),
   BirthDate Date
   );
   

2. 创建数据库主密钥、数据库范围凭据和外部数据源。将身份设置为 S3 Access Key。

     -- create master key
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword1';
   
   -- create database scoped credential
   CREATE DATABASE SCOPED CREDENTIAL GCSCredential
   WITH IDENTITY = 'S3 Access Key',
   SECRET = '<Access key>:<Secret>';
   
   --create external data source
   CREATE EXTERNAL DATA SOURCE GCSStorage
   WITH ( TYPE = BLOB_STORAGE,
   LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
   , CREDENTIAL = GCSCredential
   );
   
   CREATE EXTERNAL DATA SOURCE GCSStorageError
   WITH ( TYPE = BLOB_STORAGE,
   LOCATION = 's3://storage.googleapis.com/bulk-insert-demo/'
   , CREDENTIAL = GCSCredential
   );
   

3. 执行批量插入存储过程以导入示例数据。

   EXEC msdb.dbo.gcloudsql_bulk_insert
   @database = 'bulktest',
   @schema = 'dbo',
   @object = 'myfirstimport',
   @file = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.bcp',
   @formatfile = 's3://storage.googleapis.com/bulk-insert-demo/bulkinsert.fmt',
   @fieldquote = '"',
   @formatfiledatasource = 'GCSStorage',
   @ROWTERMINATOR = '0x0A',
   @fieldterminator = ',',
   @datasource ='GCSStorage',
   @errorfiledatasource = 'GCSStorageError',
   @errorfile = 's3://storage.googleapis.com/oom-data/bulkinsert/bulkinsert_sampleimport.log',
   @ordercolumnsjson =
   '[{"name": "PersonID","order": " asc "},{"name": "BirthDate","order": "asc"}]'
   
   

查看导入的数据

您可以使用以下任一方法查看导入的数据：

• 请运行以下查询：

  SELECT * FROM dbo.myfirstimport
  

• Cloud SQL 会将此过程的记录添加到 SQL 错误日志。您可以在 Cloud Logging 中查看此记录。您还可以在 SQL Server Management Studio (SSMS) 上的 SQL 错误日志数据中查看此记录。

停用批量插入

如需停用批量插入，请移除 cloud sql enable bulk insert 标志：

  gcloud sql instances patch INSTANCE_NAME --database-flags="cloudsql enable bulk insert"=off
  

将 INSTANCE_NAME 替换为要移除批量插入的实例的名称。

或者，您也可以运行以下命令以清除所有数据库标志：

  gcloud sql instances patch INSTANCE_NAME --clear-database-flags
  

将 INSTANCE_NAME 替换为要移除批量插入的实例的名称。

使用条带式导入和导出

执行条带式导入或导出时，您可以缩短操作完成所需的时间，并支持导入和导出大于 5 TB 的数据库。如需了解详情，请参阅使用 BAK 文件导出和导入。

验证导入的数据库

导入操作完成后，连接到您的数据库并运行相应的数据库命令，以确保内容正确无误。例如，连接并列出数据库、表和特定条目。

已知限制

如需查看已知限制列表，请参阅导入和导出数据的问题。

自动执行导出操作

虽然 Cloud SQL 没有提供自动导出数据库的内置方法，但是您可以使用多个 Google Cloud组件构建自己的自动化工具。如需了解详情，请参阅本教程。

问题排查

排查导入操作问题

问题	问题排查
HTTP Error 409: Operation failed because another operation was already in progress。	您的实例已有一项待处理的操作。一次只能执行一项操作。在当前操作完成后尝试您的请求。
导入操作花费的时间太长。	过多的有效连接可能会干扰导入操作。 关闭未使用的操作。检查 Cloud SQL 实例的 CPU 和内存用量，以确保有大量的可用资源。确保将最多资源用于导入操作的最佳方法是在开始执行操作之前重启实例。 重启后，系统会执行以下操作： 关闭所有连接。 结束任何可能正在消耗资源的任务。
如果转储文件中引用的一个或多个用户不存在，导入操作可能会失败。	在导入转储文件之前，拥有对象或获得了对转储数据库中的对象权限的所有数据库用户都必须存在于目标数据库中。否则，导入操作将无法使用原始所有权或权限重新创建对象。 在导入之前，先创建数据库用户。
LSN 不匹配	事务日志备份的导入顺序不正确，或者事务日志链已损坏。 按照与备份集表中相同的顺序导入事务日志备份。
StopAt 过早	此错误表示事务日志文件中的第一条日志晚于 StopAt 时间戳。例如，如果事务日志文件中的第一条日志时间为 2023-09-01T12:00:00，且 StopAt 字段的值为 2023-09-01T11:00:00，则 Cloud SQL 会返回此错误。 确保您使用的是正确的 StopAt 时间戳和正确的事务日志文件。

排查导出操作问题

问题	问题排查
HTTP Error 409: Operation failed because another operation was already in progress.	您的实例已有一项待处理的操作。一次只能执行一项操作。在当前操作完成后尝试您的请求。
HTTP Error 403: The service account does not have the required permissions for the bucket.	确保存储桶已存在，并且 Cloud SQL 实例（正在执行导出）的服务账号具有 Storage Object Creator 角色 (roles/storage.objectCreator) 以允许导出到存储桶。请参阅适用于 Cloud Storage 的 IAM 角色。
您希望自动执行导出。	Cloud SQL 不提供自动执行导出的方法。 您可以使用 Cloud Scheduler、Pub/Sub 和 Cloud Run functions 等 Google Cloud产品构建自己的自动导出系统，类似自动备份一文所述。

后续步骤

• 了解如何使用 BAK 文件导入和导出数据。
• 了解如何使用 SQL 转储文件导入数据。
• 了解如何启用自动备份。
• 了解如何使用备份进行恢复。