“Seamless Cloud SQL Migration: How to Move Your Data Between Google Cloud Projects with Ease”

Recently I’ve found interesting question on Google Cloud Community, about restoring backup of MySQL instance to different instance at different project. That question got my attention so I’ve decided to not only help, but also create sort of guideline for anyone who might facing the same problem.

➡️ Link to community topic: https://www.googlecloudcommunity.com/gc/Databases/GCP-cloud-SQL-Restore-to-a-different-instance-on-different/m-p/744426#M2954

Types of Databases in CloudSQL

Before we start doing magic, it’s worth to mention, what is CloudSQL, what types of databases Google Cloud can offer in terms of CloudSQL service. So basically CloudSQL is :

Fully managed, cost-effective relational database service for PostgreSQL, MySQL, and SQL Server. Try Enterprise Plus edition for a 99.99% availability SLA and category-leading performance.

https://cloud.google.com/sql?hl=en

CloudSQL provide following features:

• Fully managed
• Price performance options
• High availability with near sub-second downtime maintenance
• Gemini in Cloud SQL
• Firebase Data Connect

📹 YouTube video for CloudSQL

Vid1. Database Configurations with Google Cloud SQL / GoogleCloudTech YT Channel

Moving forward, CloudSQL offer following types of databases:

MySQL:

• Versions Supported: CloudSQL supports MySQL versions 5.6, 5.7, and 8.0.
• Use Cases: Suitable for web applications, content management systems, and online transaction processing (OLTP).
• Features: Provides high availability, automated backups, and read replicas for scalability.

PostgreSQL:

• Versions Supported: CloudSQL supports PostgreSQL versions from 9.6 to 15.
• Use Cases: Preferred for complex queries, analytical workloads, and GIS data due to its advanced features.
• Features: Supports advanced data types, full-text search, and extensions like PostGIS.

SQL Server:

• Versions Supported: CloudSQL supports SQL Server 2017 and 2019.
• Use Cases: Common in enterprise environments, suitable for large-scale OLTP and business intelligence applications.
• Features: Includes support for SQL Server Analysis Services (SSAS), SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS).

➡️ I’m talking only about CloudSQL, however Google offers much more. If you don’t know which type of DB you should choose, follow this mini guide and look at use-case examples:
https://cloud.google.com/products/databases?hl=en

Migration process

We’ve went so far trough types of databases available at Google Cloud. But how such DBs can be migrated to other project? Unfortunately there is no such option available via UI. In fact, there is no gcloud command, which allows you to migrate DBs, between projects. The one solution, which I found when I’ve tried to replicate issue described at community post, was REST API usage. Following command present example of REST API usage:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/TARGET_PROJECT_ID/instances/TARGET_SQL_INSTANCE_NAME/restoreBackup"

• H “Authorization: Bearer $(gcloud auth print-access-token)” — is responsible for obtaining auth token
  -d @request.json — is responsible for keeping SQL instance data

➡️ More info about REST API for SQL: https://cloud.google.com/sql/docs/mysql/apis

Migration process

Following steps will guid you through entire process, so you should be able to migrate your project without any issues.

1. Create at the target project SQLInstance : https://cloud.google.com/sql/docs/mysql/create-instance
2. Obtain SQLInstance ID ( in other words, SQL Instance name )
   https://cloud.google.com/sql/docs/mysql/instance-info#info
3. Run following command to obtain backupID

curl -X GET \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
  "https://sqladmin.googleapis.com/sql/v1beta4/projects/SOURCE_PROJECT_ID/instances/SOURCE_SQL_INSTANCE_NAME/backupRuns"

4. You will get output similar to :

damian_sztankowski@cloudshell:~ (projecta-421511)$ curl -X GET      -H "Authorization: Bearer $(gcloud auth print-access-token)"      "https://sqladmin.googleapis.com/sql/v1beta4/projects/projecta-421511/instances/testprojecta/backupRuns"
{
  "kind": "sql#backupRunsList",
  "items": [
    {
      "kind": "sql#backupRun",
      "status": "SUCCESSFUL",
      "enqueuedTime": "2024-04-29T04:37:55.560Z",
      "id": "1714365475560",
      "startTime": "2024-04-29T04:37:55.580Z",
      "endTime": "2024-04-29T04:39:26.945Z",
      "type": "ON_DEMAND",
      "windowStartTime": "2024-04-29T04:37:55.560Z",
      "instance": "testprojecta",
      "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/projecta-421511/instances/testprojecta/backupRuns/1714365475560",
      "location": "us",
      "backupKind": "SNAPSHOT"
    },
    {
      "kind": "sql#backupRun",
      "status": "SUCCESSFUL",
      "enqueuedTime": "2024-04-29T04:35:30.777Z",
      "id": "1714365330777", <- backup ID ( will be needed )
      "startTime": "2024-04-29T04:35:30.804Z",
      "endTime": "2024-04-29T04:37:02.218Z",
      "type": "AUTOMATED",
      "description": "Backup automatically created after creating an instance with PITR enabled",
      "windowStartTime": "2024-04-29T04:35:30.777Z",
      "instance": "testprojecta", <- instance NAME ( will be needed )
      "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/projecta-421511/instances/testprojecta/backupRuns/1714365330777",
      "location": "us",
      "backupKind": "SNAPSHOT"
    }
  ]
}

5. Create request.json file and provide info:

{
  "restoreBackupContext":
  {
    "backupRunId": 1714365330777, <- This backup will be restored at another project
    "project": "projecta-421511", <- source project
    "instanceId": "testprojecta" <- source INSTANCE SQL NAME
  }
}

6. Execute following command to trigger restore

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/TARGET_PROJECT_ID/instances/TARGET_SQL_INSTANCE_NAME/restoreBackup"

Command should send similar output :

damian_sztankowski@cloudshell:~ (projecta-421511)$ curl -X POST \
     -H "Authorization: Bearer $(gcloud auth print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-b-421511/instances/tetsprojectb/restoreBackup"
{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-b-421511/instances/tetsprojectb",
  "status": "PENDING", <- it means that restore has been started
  "user": "damian.sztankowski",
  "insertTime": "2024-04-29T04:56:18.903Z",
  "operationType": "RESTORE_VOLUME",
  "name": "d320975e-6032-4b72-b7dd-afb000000032",
  "targetId": "tetsprojectb",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-b-421511/operations/d320975e-6032-4b72-b7dd-afb000000032",
  "targetProject": "project-b-421511

7. Go to TARGET PROJECT -> SQL -> Backups. Wait until restore will end.

⚠️ Important: The user restoring to a different project must have the cloudsql.instances.restoreBackup permission for the target project and the cloudsql.backupRuns.get permission for the source instance. These permissions are included in the Cloud SQL Admin role.

➡️ More info about restoring backup: https://cloud.google.com/sql/docs/mysql/backup-recovery/restoring#projectid

Do not forget the 👏✌️❤️ if you like this content!

Also, I will be glad if you hit the follow button so you get notified of my new posts.

You can also follow me on LinkedIn.

You can join our Google Cloud Community Poland LinkedIn group. It’s open!

Thank you!