'How to export backup of Cloud SQL database in Google Storage?

I have a GAE project and for that to store the data i have used Cloud SQL database. I need to take on demand backup of my Cloud SQL database and the backup should be stored in google storage.

Can anyone help me to how can i do this programatically using JAVA?

javagoogle-app-enginegoogle-cloud-sql


Solution 1:[1]

You cannot export a backup (on-demand or automated), as documented here. What you can do however is to export your data to a SQL dump file or a CSV file, depending on your use case, and store it on Cloud Storage.

There is no Java utility to perform an export but Google exposes an API endpoint for you to trigger an export. Here's an example request using standard curl tool:

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

You'll find further details about exporting data in the documentation here.

Solution 2:[2]

While LundinCast's answer is still correct und provides helpful information it is outdated to some extend.

New Java Utilities introduced

Google now privides a Java Utilities for it which can be be found here: https://cloud.google.com/sql/docs/mysql/admin-api/rest/v1beta4/instances/export

In my opinion the example code given there lacks the minimum code for how to set the request body. So I provide a simple snippet here:

var exportContext = new ExportContext();
// gs:// stands for the gsutil tool, the filename should have "gz" as file extension
exportContext.setUri("gs://<BUCKET_NAME>/<BACKUP_FILENAME.gz>");     exportContext.setDatabases(List.of("<DATABASE_NAME>"));
exportContext.setFileType("SQL");

var requestBody = new InstancesExportRequest();
requestBody.setExportContext(exportContext);

Neccessary permissions and automatisation

The Java Utility might not work out of the box because you'll need to set some permissions/roles correctly. A complete recipe (which also covers automatisation e.g. every day) can be found here: https://cloud.google.com/architecture/scheduling-cloud-sql-database-exports-using-cloud-scheduler . Unfortunately (as of 2022-04-21) the Python script is outdated (and also examples for other languages are missing). Use the first link given in my answer instead to see newer implementation.

Testing upfront via gcloud

To test the export step it might even help to test it via gcloud first:

gcloud sql export sql <CLOUD_SQL_INSTANCE> gs://<BUCKET_NAME>/<BACKUP_FILENAME>.gz --database=<DATABASE_NAME>

(you might have to set you project first via gcloud config set project <PROJECT_NAME>)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 LundinCast
Solution 2 Semjon Mössinger

Related Questions