Point-in-time recovery

This page describes how to use point-in-time recovery to restore your Cloud SQL instance work.

To learn more about point-in-time recovery, see this page.

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.

    After identifying the binary log file and position, you can perform the point-in-time recovery.

Point-in-time recovery is enabled by default when you create a new Cloud SQL instance.

Enabling binary logs

Console

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

    Go to the Cloud SQL Instances page

  2. Select the instance for which you want to enable point-in-time recovery.
  3. Click Edit.
  4. In the Backups, recovery, and high availability section, select Automate backups and Enable point-in-time recovery.
  5. Click Save.
  6. In the Instance details page for the instance, binaryLogEnabled is displayed as true.

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]
    

    You specify the backup-start-time parameter using 24-hour time in UTC±00 time zone.

  3. Enable point-in-time recovery:
    
    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.

REST v1beta4

Before using any of the request data below, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID (primary or replica)
  • start-time The time in the format "HH:MM"
  • enabled: Set to true for a primary instance. Set to false for a replica instance

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "startTime": "start-time",
      "enabled": enabled,
      "binaryLogEnabled": true
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

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. Use the MySQL client to connect to the instance you want to restore.

    To do so, use the Cloud Shell or your local client machine. For more information, see [Connection options for external applications][connection-options].

  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 don't 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.

  4. If the event you are looking for is not displayed, use the last position displayed as the starting point to search the next set of events:

    SHOW BINLOG EVENTS IN '<BINARY_LOG_FILE>' FROM <POSITION> LIMIT 100;
    
  5. When you find the event that marks the point in time to which you want to restore, record the position you want to restore (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.

Performing the point-in-time recovery

Console

  1. Go to the Cloud SQL Instances page in the Google Cloud 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 Click CLONE.
  3. In the Create a clone window, update the name of the new instance, if needed.
  4. Select Clone from earlier time.
  5. In Binary log file name, enter the name of the binary log you previously identified.
  6. Enter the position of the event 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 clone 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. Use the operation ID returned from the clone command to check the status of the restore operation.
    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.

REST v1beta4

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

Before using any of the request data below, make the following replacements:

  • project-id: The project ID
  • target-instance-id: The target instance ID
  • source-instance-id: The source instance ID
  • binary-log-file-name The name of the binary log file
  • binary-log-position The position within the binary log file

HTTP method and URL:

POST https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/source-instance-id/clone

Request JSON body:

{
  "cloneContext":
  {
    "kind": "sql#cloneContext",
    "destinationInstanceName": "target-instance-id",
    "binLogCoordinates":
    {
      "kind": "sql#binLogCoordinates",
      "binLogFileName": "binary-log-file-name",
      "binLogPosition": "binary-log-position"
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Disabling binary logs

Console

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

    Go to the Cloud SQL Instances page

  2. Select the instance for which you want to disable point-in-time recovery.
  3. Click Edit.
  4. In the Backups, recovery, and high availability section, deselect Enable point-in-time recovery.
  5. Click Save.
  6. In the Instance details page for the instance, binaryLogEnabled is displayed as false.

gcloud

  1. Disable point-in-time recovery:
    
    gcloud sql instances patch [INSTANCE_NAME] --no-enable-bin-log
    
        
  2. Confirm your change:
    gcloud sql instances describe [INSTANCE_NAME]
    

    Under backupConfiguration, look for binaryLogEnabled: false.

REST v1beta4

Before using any of the request data below, make the following replacements:

  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "enabled": false,
      "binaryLogEnabled": false
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following:

Disk usage and point-in-time recovery

Point-in-time recovery (PITR) uses binary logs. These logs update regularly, and use storage space. The binary logs are automatically deleted with their associated automatic backup, which generally happens after about 7 days.

If the size of your binary logs are causing an issue for your instance, you can increase the instance storage size, but the binary log size increase in disk usage might be temporary. To avoid unexpected storage issues, we recommend enabling automatic storage increases when using PITR.

To delete the logs and recover storage, you can disable point-in-time recovery. Note, however, that decreasing the storage used does not shrink the size of the storage provisioned for the instance.

Setting transaction log retention

To set the number of days to retain binary logs (from one to seven):

REST

Before using any of the request data below, make the following replacements:

  • days-to-retain: The number of days to retain transaction logs, from 1 to 7
  • project-id: The project ID
  • instance-id: The instance ID

HTTP method and URL:

PATCH https://www.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "retainedTransactionLogDays": "days-to-retain"
    }
  }
}

To send your request, expand one of these options:

You should receive a JSON response similar to the following: