Exporting Data for Import into Cloud SQL

This page describes how to create a SQL dump file or CSV file that is usable by Cloud SQL. You can use these files to import your data into Cloud SQL or to create an external master for Cloud SQL replicas.

For general best practices for importing data into Cloud SQL, see Best Practices for Importing and Exporting Data.

Creating a SQL dump file

You can create a SQL dump file for import into Cloud SQL using Cloud SQL or mysqldump.

Exporting without views using Cloud SQL

You can exclude views using the gcloud command-line tool or the Cloud SQL Admin API by specifying each table you want to export. Follow the gcloud or cURL instructions provided in Exporting Data, using the following syntax:

gcloud

gcloud sql export sql [INSTANCE_NAME] gs://[BUCKET_NAME]/sqldumpfile.gz \
    --database=[DATABASE_NAME] --table=[TABLE_NAME1,TABLE_NAME2, ...]

You must specify exactly one database when you specify tables. Do not include views or system tables.

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

cURL

ACCESS_TOKEN="$(gcloud auth application-default 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_NAME>"],
               "sqlExportOptions":
                  {"tables": ["<TABLE_NAME1>,<TABLE_NAME2>,.."]}}}' \
   -X POST \
   https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/export

You must specify exactly one database when you specify tables. Do not include views or system tables.

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

Exporting without views using mysqldump

You use mysqldump to create your SQL dump file with the following flags:

  • --databases You must use the --databases option to specify an explicit list of databases to import, 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.
  • --skip-triggers Triggers should not be included in the SQL dump file.
  • --set-gtid-purged=OFF GTID information must not be included in the SQL dump file, nor should binary logging be disabled by the SQL dump file. (Not required for MySQL 5.5 or external replication.)
  • --ignore-table If your databases include VIEWs, you must exclude them from the export by including a --ignore-table [VIEW_NAME] flag for each view in the databases being exported.

If you are creating a dump file for use in an external master or external replica configuration, you must also use the --master-data=1 and --flush-privileges flags.

In addition, do not use the --routines option. Routines should not be included in the SQL dump file.

From a command line, run mysqldump:

Standard configs

mysqldump --databases [DATABASE_NAME] -h [INSTANCE_IP] -u [USERNAME] -p \
--hex-blob --skip-triggers --set-gtid-purged=OFF --ignore-table [VIEW_NAME1] [...] \
--default-character-set=utf8mb4 > [SQL_FILE].sql

Ext replication

When you create a dump file for use in an external master configuration, you must ensure that you list all databases that you have created. If the replica does not include a database present on the external master instance, writing to that database can cause replication to stop. You cannot simply export all databases, however, because your dump file cannot include the system databases (mysql, performance_schema, and information_schema).

For external replicas, you can choose a subset of your databases to be replicated, but you still must select individual databases to avoid the exporting the system databases.

mysqldump --databases [DATABASE_NAME1, DATABASE_NAME2, ...] -h [INSTANCE_IP] -u [USERNAME] -p \
--master-data=1 --flush-privileges --hex-blob --skip-triggers --ignore-table [VIEW_NAME1] [...] \
--default-character-set=utf8mb4 > [SQL_FILE].sql

For more information about mysqldump, see the mysqldump reference.

Creating a CSV file

To create a CSV file from your local MySQL server:

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 '\"' "

What's next

Was this page helpful? Let us know how we did:

Send feedback about...

Cloud SQL for MySQL