Exporting data

This page describes how to export data from Cloud SQL instances.

Exports use database resources, but they do not interfere with normal database operations unless the instance is under-provisioned.

For best practices for exporting data, see Best Practices for Importing and Exporting Data.

Before you begin

Required roles and permissions

To export data into Cloud Storage, the service account or user must have one of the following sets of roles:

  • The Cloud SQL Editor role and the roles/storage.legacyBucketWriter IAM role.
  • A custom role including the following permissions:
    • cloudsql.instances.get
    • cloudsql.instances.export
    • storage.buckets.create
    • storage.objects.create

If the service account or user is also performing import operations, it might be more convenient to grant the Storage Object Admin IAM role to have all the required permissions for both import and export.

For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

Exporting data from Cloud SQL to a BAK file in Cloud Storage

To export data from a database on a Cloud SQL instance to a BAK file in a Cloud Storage bucket:

Console

  1. In the Google Cloud Console, go to the Cloud SQL Instances page.

    Go to Cloud SQL Instances

  2. Click the instance name to open its Overview page.
  3. Click Export.
  4. In the File format section, click BAK.
  5. In the Data to export section, use the drop-down menu to select the database you want to export from.
  6. In the Destination section, select Browse to search for a Cloud Storage bucket or folder for your export.
  7. Click Export to begin the export.

gcloud

  1. Create a Cloud Storage bucket, if you haven't already.

    For help with creating a bucket, see Creating storage buckets.

  2. Upload the file to your bucket.

    For help with uploading files to buckets, see Uploading objects.

  3. Find the service account for the Cloud SQL instance you're exporting from. You can do this running the gcloud sql instances describe command. Look for the serviceAccountEmailAddress field in the output. 
    gcloud sql instances describe INSTANCE_NAME
  4. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account. For help with setting IAM permissions, see Using IAM permissions.
  5. Export the database: 
    gcloud beta sql export bak INSTANCE_NAME gs://BUCKET_NAME/sqldumpfile.sql.gz \
--database=DATABASE_NAME

    For information about using the gcloud beta sql export bak command, see the command reference page.

  6. If you do not need to retain the IAM role you set previously, revoke it now.

REST v1beta4

  1. Create a bucket for the export: 
    gsutil mb -p PROJECT_NAME -l LOCATION_NAME gs://BUCKET_NAME

    This step is not required, but strongly recommended, so you do not open up access to any other data.

  2. Provide your instance with the storage.objectAdmin IAM role for your bucket. For help with setting IAM permissions, see Using IAM permissions.
  3. Export your database:

    Before using any of the request data below, make the following replacements:

    • project-id: The project ID
    • instance-id: The instance ID
    • bucket_name: The Cloud Storage bucket name
    • path_to_dump_file: The path to the SQL dump fle
    • database_name_1: The name of a database inside the Cloud SQL instance
    • database_name_2: The name of a database inside the Cloud SQL instance
    • offload: Enables serverless export. Set to true to use serverless export.

    HTTP method and URL: 

    POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/export

    Request JSON body: 

    {
 "exportContext":
   {
      "fileType": "BAK",
      "uri": "gs://bucket_name/path_to_dump_file",
      "databases": ["database_name"]
      "offload": true | false
    }
}

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

  4. If you do not need to retain the IAM role you set previously, revoke it now.
For the complete list of parameters for this request, see the instances:export page.

Automating export operations

Although Cloud SQL doesn't provide a built-in way to automate database exports, you can build your own automation tool using several Google Cloud components. To learn more, see this tutorial.

Troubleshooting

Click the links in the table for details:

For this problem... The issue might be... Try this...
Can't see the operation status. The user interface only shows success or failure. Use these database commands to find out more.
Connection closed during the export operation. Query must produce data within first seven minutes. Test the query manually. Learn more.
Unknown error during export. Possible bandwidth issue. Ensure that both the instance and the Cloud Storage bucket are in the same region.
You want to automate exports. Cloud SQL does not provide a way to automate exports. Build your own pipeline to perform this functionality. Learn more.

Can't see the operation status

You can't see the status of an ongoing operation.

The issue might be

The Google Cloud Console reports only success or failure when done, and is not designed to return warnings.

Things to try

Connect to the database and run SHOW WARNINGS.

Connection closed during the export operation

Connection closed during the export operation.

The issue might be

The connection to Cloud Storage may be timing out because the query running in the export is not producing any data within the first seven minutes since the export is initiated.

Things to try

Test the query manually by connecting from any client and sending the output of your query to STDOUT with the command below:

COPY (INSERT_YOUR_QUERY_HERE) TO STDOUT WITH ( FORMAT csv, DELIMITER ',', ENCODING 'UTF8', QUOTE '"', ESCAPE '"' ).

This is expected behavior since when the export is initiated, the client is expected to start sending data right away. Keeping the connection with no data sent ends up breaking the connection and eventually resulting in the export failing and leaving the operation in an uncertain state. Also, this is what the error message from gcloud is trying to say with this message:

operation is taking longer than expected.

Unknown error during export

You see the error message Unknown error while trying to export a database to a Cloud Storage bucket.

The issue might be

The transfer might be failing due to a bandwidth issue.

The Cloud SQL instance may be located in a different region from the Cloud Storage bucket. Reading and writing data from one continent to another involves a lot of network usage, and can cause intermittent issues like this. Check the regions of your instance and bucket

Things to try

Move the Cloud Storage bucket closer to the Cloud SQL instance. Run the operation at a time of lower activity.

Want to automate exports

You want to automate exports.

The issue might be

Cloud SQL does not provide a way to automate exports.

Things to try

You could build your own automated export system using Google Cloud products such as Cloud Scheduler, Pub/Sub, and Cloud Functions.

ERROR_RDBMS system error occurred

You see the error message [ERROR_RDBMS] system error occurred.

The issue might be

  • The user might not have all the Cloud Storage permissions it needs.
  • The database table might not exist.

Things to try

  1. Check that you have at least WRITER permissions on the bucket and READER permissions on the export file. For more information on configuring access control in Cloud Storage, see Create and Manage Access Control Lists.
  2. Ensure the table exists. If the table exists, confirm that you have the correct permissions on the bucket.

What's next