Solved: GCP cloud SQL - Restore to a different instance on...

riyer1

Hello,

Seeing errors when restoring a backup of MySQL instance to a different instance on a different project

gcloud sql backups restore 1714096800000 --restore-instance=algn-dr-instance-clone1 --project nprd-dr-alignedth-01 --backup-instance=algn-db-01-instance
All current data on the instance will be lost when the backup is restored.

Do you want to continue (Y/n)? Y

ERROR: (gcloud.sql.backups.restore) HTTPError 400: Invalid request: Source instance does not exist.

Can someone suggest on how to resolve this error.

DamianS

Hello @riyer1 ,Welcome on Google Cloud Community.

According to the documentation, you should use [1]REST option to restore SQL backup from one project to another.
SO basically, you must:

1. create at the target project SQLInstance
2. Obtain SQLInstance ID ( in other words, SQL Instance name )
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.

1. https://cloud.google.com/sql/docs/mysql/backup-recovery/restoring#projectid

--
cheers,
DamianS
LinkedIn medium.com Cloudskillsboost

View solution in original post

DamianS