Perform point-in-time recovery (PITR)

MySQL | PostgreSQL | SQL Server

You can use point-in-time-recovery (PITR) to restore your primary Cloud SQL instances whether the instance is live, or is deleted. PITR lets you restore the instance to a specific point-in-time. For a deleted instance, you can restore the instance to a specific point-in-time to a new or existing instance.

Cloud SQL provides the following options to restore your instance using PITR:

Perform a PITR on a live instance
Perform a PITR using the backup vault

Perform a PITR on an unavailable instance

Perform a PITR on a deleted instance

Perform a PITR using binary log event positions

For performing a PITR on an unavailable or deleted instance, you'll need to find the latest and earliest recovery time .

Perform a PITR by a using timestamp

Using a timestamp is the recommended approach for performing a PITR. Cloud SQL uses the mysqlbinlog utility to restore instances up to a specific time. For more information about the mysqlbinlog utility, see the MySQL reference documentation .

To complete the following task, you must have the following:

Binary logging and backups enabled for the instance, with continuous binary logs since the last backup before the event from which you want to recover. For more information, see Enable binary logging .
A timestamp to define the recovery point. The events that occur at and after this timestamp aren't reflected in the new instance.

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Open the more actions menufor the instance you want to recover and click Create clone .
Optionally, on the Create a clone page, update the ID of the new clone.
Select Clone from an earlier point in time .
Enter a PITR time.
Click Create clone .

gcloud

Create a clone using PITR.

Replace the following:

SOURCE_INSTANCE_NAME - Name of the instance you're restoring from.
NEW_INSTANCE_NAME - Name for the clone.
TIMESTAMP - UTC timezone for the source instance in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.

gcloud  
sql  
instances  
clone  
 SOURCE_INSTANCE_NAME 
  
 \ 
 NEW_INSTANCE_NAME 
  
 \ 
--point-in-time  
 ' TIMESTAMP 
'

REST v1

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

project-id : The project ID
target-instance-id : The target instance ID
source-instance-id : The source instance ID
restore-timestamp The point-in-time to restore up to

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ source-instance-id 
/clone

Request JSON body:

{
  "cloneContext":
  {
    "kind": "sql#cloneContext",
    "destinationInstanceName": " target-instance-id 
",
    "pointInTime": " restore-timestamp 
"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login , or by using Cloud Shell , which automatically logs you into the gcloud CLI . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

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/v1/projects/ project-id 
/instances/ source-instance-id 
/clone"

PowerShell (Windows)

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by running gcloud init or gcloud auth login . You can check the currently active account by running gcloud auth list .

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ source-instance-id 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ target-instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE",
  "name": " operation-id 
",
  "targetId": " target-instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

REST v1beta4

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

project-id : The project ID
target-instance-id : The target instance ID
source-instance-id : The source instance ID
restore-timestamp The point-in-time to restore up to

HTTP method and URL:

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

Request JSON body:

{
  "cloneContext":
  {
    "kind": "sql#cloneContext",
    "destinationInstanceName": " target-instance-id 
",
    "pointInTime": " restore-timestamp 
"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

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-id 
/instances/ source-instance-id 
/clone"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ source-instance-id 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ target-instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE",
  "name": " operation-id 
",
  "targetId": " target-instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

Perform a PITR using the backup vault

If your Cloud SQL instance is enabled to use enhanced backups , then you can perform point-in-time-recovery for your instance using the backup vault.

Console

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Open the more actions menufor the instance you want to recover and click Create clone.
Select Clone from an earlier point in time.
Enter a PITR time.
Click Create clone.

gcloud

To perform a PITR on an instance from the backup vault, you'll need to find the data-source for the backup that is nearest to the time you want to perform the PITR. To find the backup, see List all the backups in the backup vault for an instance . Once you've identified the backup, run the following command to perform the PITR:

 gcloud sql instances point-in-time-restore DATA_SOURCE 
 PITR_TIMESTAMP 
--project= TARGET_PROJECT

Replace the following:

DATA_SOURCE : the path of the data-source for the backup that is closest to the PITR timestamp you want to recover to.
PITR_TIMESTAMP : the UTC timestamp for the source instance PITR log you want to restore your instance to, in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.
TARGET_PROJECT : the project ID of your Cloud SQL instance.

REST v1

REST v1beta4

Perform PITR on an unavailable instance

Console

You might want to recover an instance that isn't available to a different zone because of the following reasons:

The zone in which the instance is configured isn't accessible. This instance has a FAILED state.
The instance is undergoing maintenance. This instance has a MAINTENANCE state.

To recover an unavailable instance, complete the following steps:

In the Google Cloud console, go to the Cloud SQL Instances page.

Go to Cloud SQL Instances
Find the row of the instance to clone.
In the Actions column, click the More Actions menu.
Click Create clone .
On the Create a clone page, complete the following actions:
1. In the Instance ID field, update the instance ID, if needed.
2. Click Clone from an earlier point in time .
3. In the Point in time field, select a date and time from which you want to clone data. This recovers the state of the instance from that point in time.
4. Click Create clone .

While the clone initializes, you're returned to the instance listing page.

gcloud

You might want to recover an instance that isn't available to a different zone because the zone in which the instance is configured isn't accessible.

gcloud  
sql  
instances  
clone  
 SOURCE_INSTANCE_NAME 
  
 TARGET_INSTANCE_NAME 
  
 \ 
--point-in-time  
 DATE_AND_TIME_STAMP 
  
 \ 
--preferred-zone  
 ZONE_NAME 
  
 \ 
--preferred-secondary-zone  
 SECONDARY_ZONE_NAME

The user or service account that's running the gcloud sql instances clone command must have the cloudsql.instances.clone permission. For more information about required permissions to run gcloud CLI commands, see Cloud SQL permissions .

REST v1

You might want to recover an instance that isn't available to a different zone because the zone in which the instance is configured isn't accessible.

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

PROJECT_ID : the project ID
SOURCE_INSTANCE_ID : the source instance ID
TARGET_INSTANCE_ID : the target instance ID

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ SOURCE_INSTANCE_NAME 
/clone

Request JSON body:

{
  "cloneContext":
  {
    "destinationInstanceName": " TARGET_INSTANCE_ID 
"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

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/v1/projects/ PROJECT_ID 
/instances/ SOURCE_INSTANCE_NAME 
/clone"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ SOURCE_INSTANCE_NAME 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ TARGET_INSTANCE_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CLONE",
  "name": " OPERATION_ID 
",
  "targetId": " TARGET_INSTANCE_NAME 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT_ID 
",
  "instanceUid": " INSTANCE_ID 
"
}

The user or service account that's using the instances.clone API method must have the cloudsql.instances.clone permission. For more information about required permissions to use API methods, see Cloud SQL permissions .

REST v1beta4

You might want to recover an instance that isn't available to a different zone because the zone in which the instance is configured isn't accessible.

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

PROJECT_ID : the project ID
SOURCE_INSTANCE_ID : the source instance ID
TARGET_INSTANCE_ID : the target instance ID

HTTP method and URL:

POST https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT_ID 
/instances/ SOURCE_INSTANCE_NAME 
/clone

Request JSON body:

{
  "cloneContext":
  {
    "destinationInstanceName": " TARGET_INSTANCE_ID 
"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

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_ID 
/instances/ SOURCE_INSTANCE_NAME 
/clone"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT_ID 
/instances/ SOURCE_INSTANCE_NAME 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT_ID 
/instances/ TARGET_INSTANCE_NAME 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CLONE",
  "name": " OPERATION_ID 
",
  "targetId": " TARGET_INSTANCE_ID 
",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT_ID 
/operations/ OPERATION_ID 
",
  "targetProject": " PROJECT_ID 
",
  "instanceUid": " INSTANCE_ID 
"
}

If you try to create a PITR clone at a time after the latest recoverable time, then the following error message is given:

The  
timestamp  
 for 
  
point-in-time  
recovery  
is  
after  
the  
latest  
recovery  
 time 
  
of Timestamp  
of  
latest  
recovery  
time 
.  
Clone  
the  
instance  
with  
a  
 time 
that ' 
s  
earlier  
than  
this  
recovery  
time.

Perform PITR on a deleted instance

To use PITR to restore a deleted instance, you'll need:

the PITR timestamp ( timestamp ) you want to restore your instance to
the target instance name
the time the source instance was deleted ( source-instance-deletion-time )

You can use PITR on a deleted instance using gcloud CLI or the Cloud SQL API only. For more information, see Restore a deleted instance using a PITR .

gcloud

Find your PITR window

To find your deleted instance's PITR window, get the earliest and latest recovery time for your instance. You can select anytime timestamp in this window to perform a PITR.

Find source instance deletion time and log retention days

The source-instance-deletion-time and log-retention-days for the deleted instance are stored with the retained backups for your instance after deletion. To find these values for your deleted instance, see List retained backups .

Restore using a PITR

To restore your deleted instance using a PITR, run the following command:

 gcloud sql instances clone SOURCE_INSTANCE_NAME 
\ NEW_INSTANCE_NAME 
\
--point-in-time=' PITR_TIMESTAMP 
' \
--source-instance-deletion-time= SOURCE_INSTANCE_DELETION_TIMESTAMP

Replace the following:

SOURCE_INSTANCE_NAME : the name of the source instance that you want to restore.
NEW_INSTANCE_NAME : the name of the new instance.
PITR_TIMESTAMP : the UTC timestamp for the source instance PITR log you want to restore your instance to, in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.
SOURCE_INSTANCE_DELETION_TIMESTAMP : the UTC timestamp for the time that the source instance was deleted, in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.

REST v1

Find your PITR window

To find your deleted instance's PITR window, get the earliest and latest recovery time for your instance. You can select anytime timestamp in this window to perform a PITR.

Find source instance deletion time and log retention days

Restore using a PITR

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

project-id : the project ID
target-instance-id : the target instance ID
source-instance-id : the source instance ID
source-instance-deletion-time : the deletion time of the source instance
restore-timestamp the point-in-time which you want to restore the instance

HTTP method and URL:

POST https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ source-instance-id 
/clone

Request JSON body:

{
  "cloneContext":
  {
    "kind": "sql#cloneContext",
    "destinationInstanceName": " target-instance-id 
",
    "sourceInstanceDeletionTime: " source-instance-deletion-time 
",
    "pointInTime": " restore-timestamp 
"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

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/v1/projects/ project-id 
/instances/ source-instance-id 
/clone"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ source-instance-id 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ target-instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CLONE",
  "name": " operation-id 
",
  "targetId": " target-instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

REST v1beta4

Find your PITR window

To find your deleted instance's PITR window, get the earliest and latest recovery time for your instance. You can select anytime timestamp in this window to perform PITR.

Find source instance deletion time and log retention days

Restore using PITR

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

project-id : the project ID
target-instance-id : the target instance ID
source-instance-id : the source instance ID
source-instance-deletion-time : the deletion time of the source instance
restore-timestamp the point-in-time which you want to restore the instance

HTTP method and URL:

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

Request JSON body:

{
  "cloneContext":
  {
    "kind": "sql#cloneContext",
    "destinationInstanceName": " target-instance-id 
",
    "sourceInstanceDeletionTime: " source-instance-deletion-time 
",
    "pointInTime": " restore-timestamp 
"
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

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-id 
/instances/ source-instance-id 
/clone"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ source-instance-id 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ target-instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE",
  "name": " operation-id 
",
  "targetId": " target-instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

Perform a PITR by using binary log positions

While we recommend that you perform PITR using timestamps, as described in Perform PITR by using a timestamp , you can also perform PITR by providing a specific binary log position, or event position, in a binary log file.

For more information about PITR using binary log positions, see PITR Using the Binary Log .

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 Enable binary logging .
The binary logs must be available on disk for you to browse them for events. To check the retention length of your binary logs on disk, see Log retention period . You can't browse binary logs that are stored in Cloud Storage with the mysqlbinlog utility.
A binary log filename and the position of the event you want to recover from (that event and all events that came after it aren't reflected in the new instance). For more information, see Identify the binary log position .

After identifying the binary log filename and position, perform the PITR using binary log event positions .

Identify the recovery position

Use the MySQL client to connect to the instance you want to restore to.

To do so, use the Cloud Shell or your local client machine. For more information, see Connection options for external applications .
Show the binary log files for the instance:
```
  SHOW 
  
 BINARY 
  
 LOGS 
 ; 
 
```
Display the first 100 events in the most recent binary 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.

If the event you're looking for isn't 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 
 ;

When you find the event that marks the point in time you want to restore up to, record the position (shown as Pos ) and the name of the binary log file.

The binary log filename and the position are the values you use for the PITR.

The following is a 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 in the previous sample, you would use 865 in mysql-bin.000011 as the recovery position. The DROP TABLE statement and all operations after it are not reflected in the new instance.

Perform PITR using binary log event positions

gcloud

Use the gcloud sql instances clone command with the --bin-log-file-name and --bin-log-position flags.

Create the new instance using the binary log filename and recovery position.

Replace the following:
- SOURCE_INSTANCE_NAME : Name of the instance you're restoring from.
- NEW_INSTANCE_NAME : Name for the clone.
- BINLOG_FILE_NAME : Name for the binary log, such as mysql-bin.187288 .
- POSITION : The position in the binary log to restore up to, such as 50001356 .
```
gcloud  
sql  
instances  
clone  
 SOURCE_INSTANCE_NAME 
  
 \ 
 NEW_INSTANCE_NAME 
  
 \ 
--bin-log-file-name = 
 " BINLOG_FILE_NAME 
" 
  
 \ 
--bin-log-position = 
 POSITION 
```
For example, a gcloud sql instances 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 
  
 \ 
```
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.

REST v1

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

Before using any of the request data, 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://sqladmin.googleapis.com/v1/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:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

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/v1/projects/ project-id 
/instances/ source-instance-id 
/clone"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ source-instance-id 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/instances/ target-instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE",
  "name": " operation-id 
",
  "targetId": " target-instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/v1/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

REST v1beta4

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

Before using any of the request data, 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://sqladmin.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:

curl (Linux, macOS, or Cloud Shell)

Save the request body in a file named request.json , and execute the following command:

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-id 
/instances/ source-instance-id 
/clone"

PowerShell (Windows)

Save the request body in a file named request.json , and execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method POST `
-Headers $headers `
-ContentType: "application/json; charset=utf-8" `
-InFile request.json `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ source-instance-id 
/clone" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

Response

{
  "kind": "sql#operation",
  "targetLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/instances/ target-instance-id 
",
  "status": "PENDING",
  "user": "user@example.com",
  "insertTime": "2020-01-21T22:43:37.981Z",
  "operationType": "CREATE",
  "name": " operation-id 
",
  "targetId": " target-instance-id 
",
  "selfLink": "https://sqladmin.googleapis.com/sql/v1beta4/projects/ project-id 
/operations/ operation-id 
",
  "targetProject": " project-id 
"
}

Get the earliest and latest recovery time

For an available instance, you can perform a PITR to any timestamp in the instance's PITR window. The PITR window starts a the earliest recovery time and ends at the latest recovery time. If the instance is unavailable and the instance logs are stored in Cloud Storage , or the instance was deleted and had PITR retention enabled , then you can retrieve the earliest and latest recovery time, and perform the PITR to any timestamp in that window. In all cases, you can restore the instance to a different primary or secondary zone by providing values for the preferred zones.

gcloud

Unavailable instance

To get the earliest and latest time to which you can recover a Cloud SQL instance that's not available, run the following command:

 gcloud sql instances get-latest-recovery-time INSTANCE_NAME

Replace the following:

INSTANCE_NAME : the name of the instance you want to find the latest recovery time for.

Deleted instance

To get the earliest and latest time to which you can recover a Cloud SQL deleted instance to, run the following command:

 gcloud sql instances get-latest-recovery-time INSTANCE_NAME 
--source-instance-deletion-time=' SOURCE_INSTANCE_DELETION_TIMESTAMP 
'

Replace the following:

INSTANCE_NAME : the name of the instance you want to find the latest recovery time for.
SOURCE_INSTANCE_DELETION_TIMESTAMP : the UTC timestamp for the time that the source instance was deleted, in RFC 3339 format. For example, 2012-11-15T16:19:00.094Z.

REST v1

Unavailable instance

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

PROJECT_ID : the project ID
INSTANCE_NAME : the name of the instance for which you're querying for the latest recovery time

HTTP method and URL:

GET https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X GET \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
"https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method GET `
-Headers $headers `
-Uri "https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "earliestRecoveryTime": "2023-06-10T17:23:59.648821586Z",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

Deleted instance

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

PROJECT_ID : the project ID
INSTANCE_NAME : the name of the source instance for which you're querying for the latest recovery time
SOURCE_INSTANCE_DELETION_TIME : the time that the source instance was deleted

HTTP method and URL:

GET https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X GET \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
"https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method GET `
-Headers $headers `
-Uri "https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "earliestRecoveryTime": "2023-06-10T17:23:59.648821586Z",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

REST v1beta4

Unavailable instance

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

PROJECT_ID : the project ID
INSTANCE_NAME : the name of the instance for which you're querying for the latest recovery time

HTTP method and URL:

GET https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

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

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method GET `
-Headers $headers `
-Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "earliestRecoveryTime": "2023-06-10T17:23:59.648821586Z",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

Deleted instance

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

PROJECT_ID : the project ID
INSTANCE_NAME : the name of the source instance for which you're querying for the latest recovery time
SOURCE_INSTANCE_DELETION_TIME : the time that the source instance was deleted

HTTP method and URL:

GET https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Execute the following command:

curl -X GET \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
"https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime"

PowerShell (Windows)

Execute the following command:

$cred = gcloud auth print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
-Method GET `
-Headers $headers `
-Uri "https://sqladmin.googleapis.com/v1/projects/ PROJECT_ID 
/instances/ INSTANCE_NAME 
/getLatestRecoveryTime" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "kind": "sql#getLatestRecoveryTime",
  "earliestRecoveryTime": "2023-06-10T17:23:59.648821586Z",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

Troubleshoot

Issue Troubleshooting

Issue	Troubleshooting
`argument --point-in-time: Failed to parse date/time: Unknown string format: 2021-0928T30:54:03.094; received: 2021-0928T30:54:03.094Z` OR `Invalid value at 'body.clone_context.point_in_time' (type.googleapis.com/google.protobuf.Timestamp), Field 'pointInTime', Invalid time format: Failed to parse input,`	The timestamp you provided is invalid.
`HTTP Error 400: Successful backup required for carrying out the operation was not found.` OR `Successful backup required for carrying out the operation was not found. or Time where no backups can be found.`	The timestamp that you provided is for a time where backups or when binlog coordinates could not be found.

argument --point-in-time: Failed to parse date/time: Unknown string format: 2021-0928T30:54:03.094; received: 2021-0928T30:54:03.094Z

Invalid value at 'body.clone_context.point_in_time' (type.googleapis.com/google.protobuf.Timestamp), Field 'pointInTime', Invalid time format: Failed to parse input,

The timestamp you provided is invalid.

HTTP Error 400: Successful backup required for carrying out the operation was not found.

Successful backup required for carrying out the operation was not found. or Time where no backups can be found.

The timestamp that you provided is for a time where backups or when binlog coordinates could not be found.

What's next

Configure flags on your clone