Exporting a MySQL database from Google Cloud SQL sounds straightforward, but there are a few different ways to do it depending on the size of your database and whether you want the export operation to use resources from your actual Cloud SQL instance.
Google Cloud SQL provides both UI-based exports and command-line exports using gcloud. You can also choose between standard exports and offload exports.
This guide will walk through both methods of exporting MySQL databases from Cloud SQL to a Google Cloud Storage bucket, including parallel exports and a common issue you may run into when using gcloud commands.
Cloud SQL Export Options
Cloud SQL allows you to export a database into a Google Cloud Storage bucket. From the Cloud SQL UI, there are two main export approaches covered here:
Standard export – uses resources from the Cloud SQL instance itself to create and upload the SQL export.
Offload export – uses a temporary serverless instance created for the export process.
Both methods ultimately allow you to get your database data into Cloud Storage, but the way the export workload is handled is different.
Standard Cloud SQL Export
A standard export is the more straightforward option.
When you initiate a standard export, Cloud SQL uses resources from the database instance itself to generate the export and upload it to your Cloud Storage bucket.
The important thing to keep in mind is that the export operation is using resources from your actual Cloud SQL instance.
For smaller databases or environments where the additional resource usage isn't a concern, this can be perfectly fine.
Cloud SQL Offload Export
Cloud SQL also provides an offload export option.
Instead of performing the export workload directly on your database instance, an offload export uses a temporary serverless instance for the operation.
This temporary instance is created for the export process and is removed by Google Cloud after the export has completed.
The main advantage is that the export workload is handled separately instead of consuming the same resources used by your main Cloud SQL instance.
Exporting MySQL from Cloud SQL Using gcloud
You don't have to perform exports through the Google Cloud Console.
Cloud SQL exports can also be initiated from the command line using the gcloud CLI, which is particularly useful when working with larger databases or when you want to automate the process.
One thing encountered while doing this at the time of writing was that the destination folder in the Cloud Storage bucket needed to be empty before starting the export.
If files already exist in the destination, the export may start but then fail because the selected location isn't empty.
So before starting the export, it's worth checking the destination.
Clearing the Cloud Storage Destination
If you already have files inside the destination folder, you can remove them using:
This recursively removes the existing files from the selected bucket path.
Be careful with this command. Double-check the bucket and folder path before running it because you're deleting objects from Cloud Storage.
You can then verify that the destination is empty with:
If the command doesn't return any objects, the destination is ready for the export.
Running a Parallel Cloud SQL Export
Once the Cloud Storage destination is ready, you can run a parallel export.
The general command is:
The number of threads can be configured based on the resources available to the Cloud SQL instance.
For example, if your Cloud SQL instance has 4 vCPUs, you could use:
Here:
instance-name is the name of your Cloud SQL instance.
gs://bucket-name/mysql-backup is the Cloud Storage destination.
--parallel enables a parallel export.
--threads=2 configures the number of export threads.
--database=my_database selects the database you want to export.
Running an Offload Parallel Export
You can also combine an offload export with a parallel export from the command line.
The general command is:
The main difference here is:
This tells Cloud SQL to perform the export using the offload mechanism rather than relying directly on the database instance's resources.
For example:
Standard Parallel Export vs Offload Parallel Export
The main decision comes down to where you want the export workload to run.
With a standard parallel export, the export workload uses resources from your existing Cloud SQL instance.
With an offload parallel export, the export workload is handled separately using Cloud SQL's offload mechanism.
If minimizing the additional workload on your production Cloud SQL instance is important, the offload option can be useful.
Common Error: Export Destination Is Not Empty
One issue that's easy to overlook is the Cloud Storage destination.
If you're running a gcloud sql export command and the export starts but shortly afterwards fails with an error saying that the destination isn't empty, check the bucket folder first.
Run:
If objects are returned, clear the destination before retrying:
Then verify the destination again:
Once nothing is returned, retry the Cloud SQL export.
Which Cloud SQL Export Method Should You Use?
There's no single method that's going to be right for every situation.
If you're doing a straightforward export and don't mind the operation using resources from your Cloud SQL instance, a standard export is the simplest approach.
If you want the export workload handled separately from the main instance, an offload export is worth considering.
If you're working from the command line and want to perform the export in parallel, --parallel and --threads give you more control over how the operation is executed.
For example, a standard parallel export would look like this:
Or, for an offload parallel export:
Offload exports output files as a MYSQL Dump. Example Output:
Offload exports in Parallel can support only upto 2 threads
Final Thoughts
Cloud SQL gives you a few different ways to export a MySQL database, and knowing the difference between standard, offload, and parallel exports can save some time when you're working with larger databases.
The biggest practical issue I ran into with command-line exports was making sure the destination in the Cloud Storage bucket was actually empty before starting the operation.
It's a small detail, but it's an easy one to miss and can cause an export to fail after you've already kicked it off.
Once that's taken care of, the gcloud sql export sql command makes it pretty easy to control whether you want a standard parallel export or an offloaded one.
#mysql #databases
0 Comments