Exporting data

MySQL   |  PostgreSQL   |  SQL Server

This page describes how to export data from Cloud SQL instances or from a database server not managed by Cloud SQL.

You can export data from Cloud SQL to Cloud Storage. You can also download data from Cloud Storage to your local environment if you want to access it locally.

To export data to Cloud Storage, the instance's service account needs to have the legacyBucketWriter Cloud IAM role set in the project. For more information, see Cloud Identity and Access Management for Cloud Storage.

Note: In Cloud SQL, SQL Server currently supports the export of native BAK files.

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

Exporting data using Cloud SQL

Note: If your data contains large objects (blobs), the export can consume a large amount of memory, impacting instance performance. For more information, see Known Issues.

Console

1. Go to the Cloud SQL Instances page in the Google Cloud Console.

   Go to the Cloud SQL Instances page

2. Click the instance you want to export data from to open its Instance details page.
3. Click Export in the button bar.
4. Under Cloud Storage export location, select a Cloud Storage bucket or folder for your export.
5. In the Name field, provide a name for your export file and click Select.
6. For Format, select BAK.
7. Select the name of the database you want to export.
8. Click Export to start the export.

gcloud

To export a dump file from an instance to a Cloud Storage bucket:

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. Describe the instance you are exporting from:

     gcloud sql instances describe [INSTANCE_NAME]
     

3. Copy the serviceAccountEmailAddress field.
4. Use gsutil iam to grant the legacyBucketWriter Cloud IAM role to the service account for the bucket. For help with setting Cloud IAM permissions, see Using Cloud IAM permissions.
5. Export the database:

     gcloud beta sql export bak [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.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 Cloud IAM role you set previously, remove it now.

REST

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 legacyBucketWriter Cloud IAM role for your bucket. For help with setting Cloud IAM permissions, see Using Cloud 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 file
   • 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

   HTTP method and URL:

   POST https://www.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_1", "database_name_2"]
       }
   }
   

   To send your request, expand one of these options:

   curl (Linux, macOS, or Cloud Shell)

   Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

   Save the request body in a file called request.json, and execute the following command:

   curl -X POST \
   -H "Authorization: Bearer "$(gcloud auth print-access-token) \
   -H "Content-Type: application/json; charset=utf-8" \
   -d @request.json \
   https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/export

   PowerShell (Windows)

   Note: The command below assumes that you have used either gcloud init or gcloud auth login to authenticate gcloud with your user account. You can check the currently active account by executing gcloud auth list.

   Save the request body in a file called request.json, and execute the following command:

   $cred = gcloud auth print-access-token
   $headers = @{ "Authorization" = "Bearer $cred" }
   
   Invoke-WebRequest `
     -Method POST `
     -Headers $headers `
     -ContentType: "application/json; charset=utf-8" `
     -InFile request.json `
     -Uri "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/export" | Select-Object -Expand Content

   You should receive a JSON response similar to the following:

   Response

   {
     "kind": "sql#operation",
     "targetLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/target-instance-id",
     "status": "PENDING",
     "user": "[e-mail]",
     "insertTime": "2020-01-21T22:43:37.981Z",
     "operationType": "UPDATE",
     "name": "operation-id",
     "targetId": "instance-id",
     "selfLink": "https://www.googleapis.com/sql/v1beta4/projects/project-id/operations/operation-id",
     "targetProject": "project-id"
   }
   

4. If you do not need to retain the Cloud IAM role you set previously, remove it now.

For the complete list of parameters for this request, see the instances:export page.

To see how the underlying REST API request is constructed for this task, see the APIs Explorer on the instances:export page.

What's next

• Learn how to check the status of import and export operations.
• Learn more about importing and exporting data.
• Learn more about Cloud Storage.