Exporting data from Cloud SQL

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

You export Cloud SQL data to a CSV or SQL dump file in Cloud Storage. You can then import the file into another MySQL database in Cloud SQL. 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 must have the storage.objectAdmin IAM role set in the project. For more information, see Cloud Identity and Access Management for Cloud Storage.

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.

You can export a CSV file. You also can export a SQL dump file, for example, if you want to export to another SQL database.

Standard export versus serverless export

For a standard export from Cloud SQL, the export is run while the database is online. When the databases being exported are smaller, the impact is likely to be minimal. However, when there are large databases, or large objects, such as BLOBs in the database, there's the possibility that the export might degrade database performance. This might impact the time it takes to perform database queries and operations against the database. Once you start an export, it is not possible to stop it if your database starts to respond slowly.

To prevent slow responses during an export, you can use serverless export. With serverless export, Cloud SQL creates a separate, temporary instance to offload the export operation. Offloading the export operation allows databases on the primary instance to continue to serve queries and perform operations at the usual performance rate. When the data export is complete, the temporary instance is automatically deleted.

A serverless export takes longer to do than a standard export, because it takes time to create the temporary instance. At a minimum, it takes longer than five minutes, but for larger databases, it might be longer. Consider the impact to time and performance before determining which type of export to use.

You can use serverless export on a primary instance or a read replica.

CSV and mysqldump exports behave the same in Cloud SQL as they would if they were from on any other MySQL database. The database does not lock during the export operation, unless you use the --master-data option during the export.

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.

Use the same SQL Mode for import and export

The SQL Mode setting affects how Cloud SQL interprets SQL queries. For example, if you export from a database without Strict SQL enabled, then try to import to Cloud SQL (which enables Strict SQL by default), the import might fail. The best practice is to use the same SQL Mode on import that you used for export. Review the SQL Mode on both the source and target databases for compatibility. Pay particular attention to the flags that enable Strict SQL mode. If Strict SQL is NOT set on your database, you will likely want to remove it in Cloud SQL. If you remove Strict SQL, you must set another flag. To verify that your Cloud SQL instance has the desired mode set, run SELECT @@GLOBAL.sql_mode;.

Exporting data from Cloud SQL to a SQL dump file

To create a SQL dump file, you export data from Cloud SQL to Cloud Storage. Once the file is in Cloud Storage, you can import it into another Cloud SQL database. You can also download data from Cloud Storage to your local environment if you want to access it locally.

Before you begin

This procedure requires you to export a file to Cloud Storage. To export data into Cloud Storage, the instance's service account must have the storage.objectAdmin IAM roles set in the project. For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

You can find the instance's service account name in the Google Cloud Console on your instance's Overview page. You can verify the roles for your Cloud Storage bucket by using the gsutil tool to inspect the bucket:

gsutil iam get gs://[BUCKET_NAME]

Learn more about using IAM with buckets.

Exporting data to a SQL dump file in Cloud Storage

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

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 Overview page.
  3. Click EXPORT in the button bar.
  4. Under File format, click SQL to create a SQL dump file.
  5. Under Data to export, click One or more databases in this instance to export specific databases.
  6. Use the drop-down menu to select the databases you want to export from.
  7. Under Destination, select Browse to search for a Cloud Storage bucket or folder for your export.
  8. 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 an Object.

  3. Describe the instance you are exporting from:
      gcloud sql instances describe [INSTANCE_NAME]
      
  4. Copy the serviceAccountEmailAddress field.
  5. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account for the bucket. For help with setting IAM permissions, see Using IAM permissions.
  6. Export the database:
      gcloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.gz \
                                  --database=[DATABASE_NAME] --offload
      

    The export sql command does not contain triggers or stored procedures, but does contain views. To export triggers and/or stored procedures, use the mysqldump tool.

    For more information about using the export sql command, see the sql export sql command reference page.

  7. 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://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id/export

    Request JSON body:

    {
     "exportContext":
       {
          "fileType": "SQL",
          "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.

Exporting data from Cloud SQL to a CSV file

You can export your data in CSV format, which is usable by other tools and environments. Exports happen at the database level. During a CSV export, you can specify the schemas to export. All schemas under the database level are eligible for export.

Before you begin

This procedure requires you to export a file to Cloud Storage. To export data into Cloud Storage, the instance's service account must have the storage.objectAdmin IAM roles set in the project. For help with IAM roles, see Cloud Identity and Access Management for Cloud Storage.

You can find the instance's service account name in the Google Cloud Console on your instance's Overview page. You can verify the roles for your Cloud Storage bucket by using the gsutil tool to inspect the bucket:

gsutil iam get gs://[BUCKET_NAME]

Learn more about using IAM with buckets.

Exporting data to a CSV file in Cloud Storage

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

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 to open its Overview page.
  3. Click EXPORT.
  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.

    You can use a file extension of .gz to compress your export file.

  6. Set Format to CSV.
  7. Click Show advanced options.
  8. Under Database, select the name of the database from the drop-down menu.
  9. Under SQL query, enter a SQL query to specify the table to export data from.

    For example, to export the entire contents of the entries table in the guestbook database, you enter

    SELECT * FROM guestbook.entries;
    Your query must specify a table in the specified database; you cannot export an entire database in CSV format.

  10. Click Export to start the export.
  11. The Export database? dialog box opens with a message that the export process can take an hour or more for large databases. During the export, the only operation you can perform on the instance is viewing information. You can't stop the export once it starts. If this is a good time to start an export, click EXPORT. Otherwise, click CANCEL.

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 an Object.

  3. Describe the instance you are exporting from:
    gcloud sql instances describe [INSTANCE_NAME]
    
  4. Use gsutil iam to grant the storage.objectAdmin IAM role to the service account for the bucket. For help with setting IAM permissions, see Using IAM permissions.
  5. Export the database:
    gcloud sql export csv [INSTANCE_NAME] gs://[BUCKET_NAME]/[FILE_NAME] \
                                --database=[DATABASE_NAME]
                                --offload
                                --query=[SELECT_QUERY]
    

    For information about using the export csv command, see the sql export csv 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_csv_file: The path to the CSV file
    • database_name: The name of a database inside the Cloud SQL instance
    • offload: Enables serverless export. Set to true to use serverless export.
    • select_query: SQL query for export

    HTTP method and URL:

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

    Request JSON body:

    {
     "exportContext":
       {
          "fileType": "CSV",
          "uri": "gs://bucket_name/path_to_csv_file",
          "databases": ["database_name"],
          "offload": true | false
          "csvExportOptions":
           {
               "selectQuery":"select_query"
           }
       }
    }
    

    To send your request, expand one of these options:

    You should receive a JSON response similar to the following:

    If your select query specifies a database, it overrides the databases property.

  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.

Exporting in CSV format is equivalent to running the following SQL statement:

  SELECT [QUERY] INTO OUTFILE ... CHARACTER SET 'utf8mb4'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
        ESCAPED BY '\\' LINES TERMINATED BY '\n'

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

Exporting data from your local MySQL server to a CSV file

To export a MySQL database that is not managed by Cloud SQL to a CSV file, for later import into Cloud SQL, use the following command:

mysql --host=[INSTANCE_IP] --user=[USER_NAME] --password [DATABASE] \
-e " SELECT * FROM [TABLE] INTO OUTFILE '[FILE_NAME]' CHARACTER SET 'utf8mb4'
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\"' "

Exporting data using mysqldump

If you are exporting data from an on-premises MySQL database or from a Cloud SQL database for import into a Cloud SQL database, you must use the mysqldump utility with the following flags:

  • --databases You must use the --databases option to specify an explicit list of databases to export, and this list must not contain the mysql system database.
  • --hex-blob If your database contains any binary fields, you must use this flag to ensure that your binary fields are imported correctly.
  • --set-gtid-purged=OFF GTID information must not be included in the SQL dump file, and binary logging must not be disabled by the SQL dump file. (Not required for MySQL 5.5 or external replication.)
  • --single-transactionReplicating from an external server

Standard configuration

From a command line, run mysqldump:

mysqldump --databases [DATABASE_NAME] -h [INSTANCE_IP] -u [USERNAME] -p \
--hex-blob --single-transaction --set-gtid-purged=OFF \
--default-character-set=utf8mb4 > [SQL_FILE].sql

For help with mysqldump, see the mysqldump reference.

External replication

To create a dump file for use in an external server configuration, see Replicating from an external server.

What's next