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 can export data from Cloud SQL to Cloud Storage. You can download your 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 Bucket Writer ACL permission set in the project. For more information see Cloud Identity and Access Management for Cloud Storage.

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

Before you begin

Decide the file type to export.

You can export a CSV file for use with other databases or tools. You also can export a SQL dump file, e.g., to export to another SQL database.

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 to a SQL dump file

Exporting from Cloud SQL uses the mysqldump utility with the --single-transaction, --skip-triggers and --hex-blob options, and excludes the mysql database. This means that the export does not include any triggers, stored procedures, or functions. If your database requires any of these elements, you must recreate them manually after you import your data.

If this export does not meet your requirements, you can also run the mysqldump utility directly against your Cloud SQL database, using whatever options you require.

Exporting data using Cloud SQL

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 SQL.
  7. Click Show advanced options and enter a comma-separated list of all the databases that you want to export. Do not include system databases.
  8. Click Export to start the export.

gcloud (2nd Gen)

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. Add the service account to the bucket ACL as a writer:
      gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]
      
  5. Export the database:
      gcloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.gz \
                                  --database=[DATABASE_NAME]
      

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

  6. If you do not need to retain the permissions provided by the ACL you set previously, remove the ACL:
      gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]
      

gcloud (1st Gen)

gcloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.gz \
                            --database [DATABASE_NAMES]

If you get an error like ERROR_RDBMS, you might not have the required permissions to export to that bucket. Confirm that you have at least WRITER permissions on the bucket. For more information on configuring access control in Cloud Storage, see Create and Manage Access Control Lists.

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

cURL

  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 write permissions to your bucket.

    For help with setting ACLs, see Setting ACLs.

  3. Export your database:
    gcloud auth login
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data '{"exportContext":
                    {"fileType": "SQL",
                     "uri": "gs://<BUCKET_NAME>/<PATH_TO_DUMP_FILE>",
                     "databases": ["<DATABASE_NAME1>", "<DATABASE_NAME2>"] }}' \
       -X POST \
       https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/export
    
  4. If you do not need to retain the permissions provided by the ACL you set previously, remove the ACL.
For the complete list of parameters for this request, see the instances:export page.

Exporting data to a CSV file

You can export your data in CSV format, which is usable by other tools and environments.

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 Instance 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. Enter a SQL query to specify the data to export.

    For example, to export the entire contents of the entries table in the guestbook database, you would enter SELECT * FROM guestbook.entries;. Your query must specify a table; you cannot export an entire database in CSV format.

  8. Click Export to start the export.

gcloud (2nd Gen)

  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. Add the service account to the bucket ACL as a writer:
    gsutil acl ch -u [SERVICE_ACCOUNT_ADDRESS]:W gs://[BUCKET_NAME]
    
  5. Export the database:
    gcloud sql export csv [INSTANCE_NAME] gs://[BUCKET_NAME]/[FILE_NAME] \
                                --database=[DATABASE_NAME] --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 permissions provided by the ACL you set previously, remove the ACL:
    gsutil acl ch -d [SERVICE_ACCOUNT_ADDRESS] gs://[BUCKET_NAME]
    

gcloud (1st Gen)

gcloud sql export csv [INSTANCE_NAME] gs://[BUCKET_NAME]/[FILE_NAME] \
                            --database [DATABASE_NAME] --query=[SELECT_QUERY]

If you get an error, you might not have the required permissions to export to that bucket. Confirm that you have at least WRITER permissions on the bucket. For more information on configuring access control in Cloud Storage, see Create and Manage Access Control Lists.

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

cURL

  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 write permissions to your bucket.

    For help with setting ACLs, see Setting ACLs.

  3. Export your database:

    gcloud auth login
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data '{"exportContext":
                    {"fileType": "CSV",
                     "uri": "gs://<BUCKET_NAME>/<PATH_TO_DUMP_FILE>",
                     "databases": ["<DATABASE_NAME>"] },
                     "csvExportOptions":
                         {"selectQuery":"<SELECT_QUERY>"}}' \
       -X POST \
       https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/export
    

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

  4. If you do not need to retain the permissions provided by the ACL you set previously, remove the ACL.
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'

What's next

หน้านี้มีประโยชน์ไหม โปรดแสดงความคิดเห็น

ส่งความคิดเห็นเกี่ยวกับ...

หากต้องการความช่วยเหลือ ให้ไปที่หน้าการสนับสนุน