This page provides best practices for importing and exporting data with Cloud SQL. For step-by-step instructions for importing data into Cloud SQL, see Importing Data. To export data for import into Cloud SQL, see Exporting Data for Import into Cloud SQL. To export data from Cloud SQL for use in a MySQL instance that you manage, see Exporting Data.
Best practices for importing and exporting
The following are best practices you should consider when importing and exporting data:
- Don't use Requester Pays buckets
- Use the correct flags when you create a SQL dump file
- Compress data to reduce cost
Don't use Requester Pays buckets
You cannot use a bucket that has Requester Pays enabled for imports and exports from Cloud SQL.
Use the correct flags when you create a SQL dump fileIf you do not use the right flags when you export your data to a SQL dump file, your import might be unsuccessful. For information about creating a SQL dump file for import into Cloud SQL, see Creating a SQL dump file.
Compress data to reduce cost
Cloud SQL supports importing and exporting both compressed and uncompressed files. Compression can save significant storage space on Cloud Storage and reduce your storage costs, especially when you are exporting large instances.When you export a SQL dump or CSV file, use a
.gzfile extension to compress the data. When you import a file with an extension of
.gz, it is decompressed automatically. First Generation instances do not support compression for CSV files.
InnoDB is the only supported storage engine for Second Generation instances. For First Generation instances, all storage engines are supported.
You can convert your tables from MyISAM to InnoDB by piping the output of mysqldump through a sed script as follows:
mysqldump --databases [DATABASE_NAME] \ -h [INSTANCE_IP] -u [USERNAME] -p [PASSWORD] \ --hex-blob --default-character-set=utf8mb4 | sed 's/ENGINE=MyISAM/ENGINE=InnoDB/g' > [DATABASE_FILE].sql
Tips for long-running import and export processes
Imports and exports into Cloud SQL using the import functionality (via a Cloud Storage bucket) can take a long time to complete, depending on the size of the database. This can have the following impacts:
- On First Generation instances, operations are limited to 24 hours.
- You cannot stop a long-running operation.
In addition, you can perform only one import or export operation at a time for each instance.
You can decrease the amount of time a single operation requires by using one of the following approaches:
Use the Cloud SQL import or export functionality, but do it with smaller batches of data that will take less than 24 hours to complete.
Don't use the Cloud SQL import or export functionality, but instead replay a dump file directly to Cloud SQL. For example, you can use
cloudsql-import, which imports by replaying a mysqldump file over a MySQL connection.
cloudsql-importis resilient to connection failures and instance restarts.
For more tips, see Diagnosing Issues with Cloud SQL Instances.