Restoring an Instance

This page describes how to restore your instance from a backup and perform a point-in-time recovery of an instance.

For more information about restore operations or point-in-time recovery, see Overview of restoring an instance.

Restoring an instance from a backup

You can restore a backup to the instance from which the backup was taken, or you can restore a backup to another instance in the same project.

Restoring from a backup to the same instance

When you restore from a backup to the same instance, you return the data on that instance to its state when you took the backup. For information about restoring an instance, see General tips about performing a restore.

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. If the instance you want to restore has any read replicas or a failover replica, delete them, using the more actions menu More actions icon..
  3. Click the instance you want to restore to open its Instance details page.
  4. In the Recent backups list, find the backup from which to restore and select Restore from the more actions menu More actions icon..
  5. In the Restore instance from backup dialog box, click OK to start the restore process.

    The default target instance is the same instance from which you started the restore process.

    The restore backup dialog box.

  6. You can check the status of the restore by going to the Operations page of the instance.
  7. After the restore completes, recreate any replicas you deleted in the first step.

    You cannot reuse instance names for up to a week after an instance is deleted.

gcloud

  1. Describe the instance to see whether it has any replicas:
    gcloud sql instances describe [INSTANCE_NAME]
    

    Note any instances listed under replicaNames.

  2. Delete all replicas:
    gcloud sql instances delete [REPLICA_NAME]
    

    Repeat for the failover replica and all read replicas.

  3. List the backups for the instance:
    gcloud sql backups list --instance [INSTANCE_NAME]
    
  4. Find the backup you want to use and record its ID value.
    Be sure to select a backup that is marked as `SUCCESSFUL`.
  5. Restore from the specified backup:
    gcloud sql backups restore [BACKUP_ID] --restore-instance=[INSTANCE_NAME]
  6. After the restore completes, recreate any replicas you deleted in the first step.

    You cannot reuse instance names for up to a week after an instance is deleted.

cURL

  1. List all replicas for the instance:
    ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         -X GET \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]?fields=replicaNames
    
  2. Delete all replicas:
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         -X DELETE \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[REPLICA_NAME]
    

    Repeat for each read replica and for the failover replica.

  3. List the backups for the instance to get the id of the backup you want to use:
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         -X GET \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/backupRuns
    
  4. Restore the instance from the backup:
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data '{ "restoreBackupContext": {"backupRunId": "[BACKUP_ID]"}}' \
         -X POST \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[INSTANCE_NAME]/restoreBackup
    
  5. After the restore completes, recreate any replicas you deleted previously.

    You cannot reuse instance names for up to a week after an instance is deleted.

Restoring from a backup to a different instance

When you restore from a backup to a different instance, you update the data on the target instance to the state of the source instance when you took the backup. For more information, see General tips about performing a restore and Tips and requirements for restoring to a different instance.

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. If the instance you want to restore has any read replicas or a failover replica, delete them, using the more actions menu More actions icon..
  3. Click the instance from which you want to restore to open its Instance details page.
  4. In the Backups list, find the backup from which to restore and select Restore.
  5. In the Restore instance from backup dialog, select a Target instance to which to restore the backup, and then click OK.
  6. You can check the status of the restore by going to the Operations page of the target instance.
  7. After the restore completes, recreate any replicas you deleted previously.

    You cannot reuse instance names for up to a week after an instance is deleted.

gcloud

  1. Describe the target instance to see whether it has any replicas:
    gcloud sql instances describe [TARGET_INSTANCE_NAME]
    

    Note any instances listed under replicaNames.

  2. Delete all replicas:
    gcloud sql instances delete [REPLICA_NAME]
    

    Repeat for the failover replica and each read replica.

  3. List the backups for the source instance:
    gcloud sql backups list --instance [SOURCE_INSTANCE_NAME]
    
  4. Find the backup you want to use and record its ID value.
    Be sure to select a backup that is marked as `SUCCESSFUL`.
  5. Restore from the specified backup to the target instance:
    gcloud sql backups restore [BACKUP_ID] --restore-instance=[TARGET_INSTANCE_NAME] \
                                              --backup-instance=[SOURCE_INSTANCE_NAME]
        
  6. After the restore completes, recreate any replicas you deleted previously.

    You cannot reuse instance names for up to a week after an instance is deleted.

cURL

  1. List all replicas for the target instance:
    ACCESS_TOKEN="$(gcloud auth application-default print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         -X GET \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[TARGET_INSTANCE_NAME]?fields=replicaNames
    
  2. Delete all replicas:
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         -X DELETE \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[REPLICA_NAME]
    

    Repeat for each read replica and for the failover replica.

  3. List the backups for the source instance to get the id of the backup you want to use:
    ACCESS_TOKEN="$(gcloud auth print-access-token)"
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         -X GET \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[SOURCE_INSTANCE_NAME]/backupRuns
    
  4. Restore the target instance from the backup:
    curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
         --header 'Content-Type: application/json' \
         --data '{ "restoreBackupContext": {"backupRunId": "[BACKUP_ID]", "instanceId": "[SOURCE_INSTANCE_NAME]"}}' \
         -X POST \
         https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[TARGET_INSTANCE_NAME]/restoreBackup
    
  5. After the restore completes, recreate any replicas you deleted previously.

    You cannot reuse instance names for up to a week after an instance is deleted.

Performing a point-in-time recovery

Point-in-time recovery enables you to recover an instance to a specific point in time. A point-in-time recovery always creates a new instance; you cannot perform a point-in-time recovery to an existing instance.

Before you begin

Before completing this task, you must have:

  • Binary logging and backups enabled for the instance, with continuous binary logs since the last backup before the event you want to recover from.

    For more information, see Enabling binary logging.

  • A binary log file name and the position of the event you want to recover from (that event and all events that came after it will not be reflected in the new instance).

    For more information, see Identifying the binary log position.

Performing the point-in-time recovery

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Open the more actions menu More actions icon. for the instance you want to recover and select Clone.
  3. In the Create clone window, update the name of the new instance if needed.
  4. Under Advanced, select Clone from earlier position
  5. Enter the name of the binary log you identified previously in Binary log file name.
  6. Enter the position of the event you want to repair from in Recovery position.
  7. Click Create clone.

gcloud

  1. Create the new instance using the binary log file name and recovery position you have identified:
    gcloud sql instances clone [SOURCE_INSTANCE_NAME] [NEW_INSTANCE_NAME] \
           --bin-log-file-name=[BINLOG_FILE_NAME] --bin-log-position=[POSITION]
    

    For example, a restore command might look similar to the following:

    gcloud sql instances clone instance1 instance1-clone \
           --bin-log-file-name=mysql-bin.0000031 --bin-log-position=107
    
  2. Check the status of the restore operation by using the operation ID returned from the clone command:
    gcloud sql operations describe [OPERATION_ID]
    When the operation is in progress, a state of RUNNING is returned. When the operation is complete, a state of DONE is returned.

For more information about point-in-time recovery, see the MySQL Reference, Point-in-Time Recovery Using the Binary Log.

cURL

Create the new instance using the binary log file name and recovery position you have identified:

ACCESS_TOKEN="$(gcloud auth print-access-token)"
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" \
     --header 'Content-Type: application/json' \
     --data '{"cloneContext": {"kind": "sql#cloneContext",
              "destinationInstanceName": "[TARGET_INSTANCE_NAME]",
              "binLogCoordinates": {"kind": "sql#binLogCoordinates",
                                    "binLogFileName": "[BINLOG_FILE_NAME]",
                                    "binLogPosition": "[POSITION]" }}}' \
     https://www.googleapis.com/sql/v1beta4/projects/[PROJECT-ID]/instances/[SOURCE_INSTANCE_NAME]/clone \
     -X POST

Identifying the recovery position

Before you can perform a point-in-time recovery, you must have identified a binary log file name and position that correspond to the point in time you want to recover the instance to.

You use the MySQL Client to accomplish this task.

  1. Connect to the instance you want to restore with MySQL Client.

    This can be done by using the Cloud Shell or from your local client machine. For more information, see Connection Options for External Applications.

  2. Show the binary log files for the instance:

    SHOW BINARY LOGS;
    
  3. Display the first 100 events in the most recent log file:

    SHOW BINLOG EVENTS IN '<BINARY_LOG_FILE>' LIMIT 100;
    

You can adjust the number of rows to show, but you should not show all of the events in the file until you know how large the file is. Displaying a large number of events can affect system performance.

  1. If the event you are looking for is not displayed, display the next set of events by using the last position displayed:

    SHOW BINLOG EVENTS IN '<BINARY_LOG_FILE>' FROM <POSITION> LIMIT 100;
    
  2. When you find the event that marks the point in time to which you want to restore, record its position (shown as Pos) and the name of the log file.

The log file name and the position are the values you use for the point-in-time recovery.

Below is some sample output from the SHOW BINLOG EVENTS command:

+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
| mysql-bin.000011 |   4 | Format_desc |  88955285 |         120 | Server ver: 5.6.30-log, Binlog ver: 4               |
| mysql-bin.000011 | 120 | Query       |  88955285 |         211 | create database db1                                 |
| mysql-bin.000011 | 211 | Query       |  88955285 |         310 | use `db1`; CREATE TABLE t (c CHAR(20))              |
| mysql-bin.000011 | 310 | Query       |  88955285 |         381 | BEGIN                                               |
| mysql-bin.000011 | 381 | Table_map   |  88955285 |         426 | table_id: 18 (db1.t)                                |
| mysql-bin.000011 | 310 | Query       |  88955285 |         381 | BEGIN                                               |
| mysql-bin.000011 | 426 | Write_rows  |  88955285 |         464 | table_id: 18 flags: STMT_END_F                      |
| mysql-bin.000011 | 464 | Xid         |  88955285 |         495 | COMMIT /* xid=56 */                                 |
| mysql-bin.000011 | 495 | Query       |  88955285 |         566 | BEGIN                                               |
| mysql-bin.000011 | 566 | Table_map   |  88955285 |         611 | table_id: 18 (db1.t)                                |
| mysql-bin.000011 | 611 | Write_rows  |  88955285 |         649 | table_id: 18 flags: STMT_END_F                      |
| mysql-bin.000011 | 649 | Xid         |  88955285 |         680 | COMMIT /* xid=57 */                                 |
| mysql-bin.000011 | 680 | Query       |  88955285 |         751 | BEGIN                                               |
| mysql-bin.000011 | 751 | Table_map   |  88955285 |         796 | table_id: 18 (db1.t)                                |
| mysql-bin.000011 | 796 | Write_rows  |  88955285 |         834 | table_id: 18 flags: STMT_END_F                      |
| mysql-bin.000011 | 834 | Xid         |  88955285 |         865 | COMMIT /* xid=58 */                                 |
| mysql-bin.000011 | 865 | Query       |  88955285 |         977 | use `db1`; DROP TABLE `t` /* generated by server */ |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------+
16 rows in set (0.04 sec)

To restore up to the DROP TABLE statement, bolded above, you would use "865" in "mysql-bin.000011" as the recovery position. The DROP TABLE statement and all operations after it would not be reflected in the new instance.

Enabling binary logging

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud Platform Console.

    Go to the Cloud SQL Instances page

  2. Select the instance for which you want to enable binary logging.
  3. Click Edit.
  4. In the Backups and binary logging section, check Enable automated backups if needed, and Enable binary logging.

    Automated backups must be enabled before you can enable binary logging.

  5. Click Save.
  6. In the Overview page for the instance, under Properties, you should see Binary log displayed as Enabled.

gcloud

  1. Display the instance details:
    gcloud sql instances describe [INSTANCE_NAME]
    
  2. If you see enabled: false under backupConfiguration, enable scheduled backups:
    gcloud sql instances patch [INSTANCE_NAME] --backup-start-time [HH:MM]
    

    The `backup-start-time` parameter is specified in 24-hour time, in the UTC±00 time zone.

  3. Enable binary logging:
    gcloud sql instances patch [INSTANCE_NAME] --enable-bin-log
    
  4. Confirm your change:
    gcloud sql instances describe [INSTANCE_NAME]
    

    Under backupConfiguration, look for binaryLogEnabled: true.

What's next

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...

Cloud SQL for MySQL