Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from http://cloud.google.com/sql/docs/mysql/backup-recovery/pitr below:

Use point-in-time recovery (PITR) | Cloud SQL for MySQL

Stay organized with collections Save and categorize content based on your preferences.

This page describes how to use point-in-time recovery (PITR) to restore your primary Cloud SQL instance.

To learn more about PITR, see Point-in-time recovery (PITR).

If you create a Cloud SQL Enterprise Plus edition instance, then PITR is enabled by default, regardless of the method used for creation. If you want to disable the feature, then you must do so manually.

If you create a Cloud SQL Enterprise edition instance in the Google Cloud console, then PITR is enabled by default. Otherwise, if you create the instance by using the gcloud CLI, Terraform, or the Cloud SQL Admin API, then PITR is disabled by default. In this case, if you want to enable the feature, then you must do so manually.

Cloud SQL uses binary logs for PITR.

On August 11, 2023, we launched storing transaction logs for PITR in Cloud Storage. Since this launch, the following conditions apply:

• All Cloud SQL Enterprise Plus edition instances store their binary logs used for PITR in Cloud Storage. Only Cloud SQL Enterprise Plus edition instances that you upgraded from Cloud SQL Enterprise edition before April 1, 2024 and had PITR enabled before August 11, 2023 continue to store their logs for PITR on disk.

• Cloud SQL Enterprise edition instances created with PITR enabled before August 11, 2023 continue to store their logs for PITR on disk.

  Note: For instances that were created before August 11, 2023, Cloud SQL switches the location of the transaction logs used for PITR automatically for you. You can use gcloud CLI or the Cloud SQL Admin API to check where transaction logs are stored for your instance.

• If you upgrade a Cloud SQL Enterprise edition instance after April 1, 2024 that stores transaction logs for PITR on disk to Cloud SQL Enterprise Plus edition, then the upgrade process switches the storage location of the transaction logs used for PITR to Cloud Storage for you. For more information, see Upgrade an instance to Cloud SQL Enterprise Plus edition by using in-place upgrade.

• All Cloud SQL Enterprise edition instances that you create with PITR enabled after August 11, 2023 store logs used for PITR in Cloud Storage.

For more information about how to check the storage location of the transaction logs used for PITR, see Check the storage location of transaction logs used for PITR.

For instances that store binary logs only on disk, you can switch the storage location of the transaction logs used for PITR from disk to Cloud Storage by using gcloud CLI or the Cloud SQL Admin API without incurring any downtime. For more information, see Switch transaction log storage to Cloud Storage.

Cloud SQL retains transaction logs in Cloud Storage for up to the value set in the transactionLogRetentionDays PITR configuration setting. This value can range from 1 to 35 days for Cloud SQL Enterprise Plus edition and 1 to 7 days for Cloud SQL Enterprise edition. If a value for this parameter isn't set, then the default transaction log retention period is 14 days for Cloud SQL Enterprise Plus edition instances and 7 days for Cloud SQL Enterprise edition instances. For more information on how to set the transaction log retention days, see Set transaction log retention.

Although an instance stores the binary logs used for PITR in Cloud Storage, the instance also keeps a smaller number of duplicate binary logs on disk to allow for replication of the logs to Cloud Storage. By default, when you create an instance with PITR enabled, the instance stores its binary logs for PITR in Cloud Storage. Cloud SQL also sets the value of the expire_logs_days and binlog_expire_logs_seconds flags to the equivalent of one day automatically. This translates to one day of logs on disk.

For PITR binary logs that are stored on disk, that are being switched to Cloud Storage, or that are already switched to Cloud Storage, Cloud SQL retains the logs for the minimum value set for one of the following configurations:

• The transactionLogRetentionDays backup configuration setting
• The expire_logs_days or the binlog_expire_logs_seconds flag

Cloud SQL doesn't set any values for these flags if the binary logs are stored on disk, are being switched to Cloud Storage, or have already been switched to Cloud Storage. When logs are stored on disk, modifying the values of these flags can affect the behavior of PITR recovery and how many days worth of logs are stored on disk. While the log storage location is being switched to Cloud Storage, you can't modify the flag values. We also don't recommend that you configure either flag value to 0. For more information, see Configure database flags.

For customer-managed encryption key (CMEK)-enabled instances, binary logs are encrypted using the latest version of the CMEK. To perform a restore, all versions of the key that were the latest versions for the number of days that you configured for the retained-transaction-log-days parameter must be available.

Logs are generated regularly and use storage space. The binary logs are deleted automatically with their associated automatic backup, which happens after the value that's set for transactionLogRetentionDays is met.

To find out how much disk is being used by the binary logs, check the bytes_used_by_data_type metric for the instance. The value for the binlog data type returns the size of the binlogs on the disk. For instances that store transaction logs used for PITR on disk, Cloud SQL purges data from the disk daily to meet the transactionLogRetentionDays PITR setting, as described in Automatic backup and transaction log retention. However, if you set the expire_logs_days or binlog_expire_logs_seconds flag to a value that's lower than transaction log retention days, then Cloud SQL can purge the binary logs sooner.

If the size of your binary logs is causing an issue for your instance:

• Check whether your instance is storing logs on disk. You can switch the storage location of the logs that Cloud SQL uses for PITR from disk to Cloud Storage without downtime by using gcloud CLI or the Cloud SQL Admin API. If you're using Cloud SQL Enterprise edition, then you can also upgrade to Cloud SQL Enterprise Plus edition to switch the storage location of your PITR logs.

• You can increase the instance storage size. However, the binary log size increase in disk usage might be temporary.

• We recommend enabling automatic storage increase to avoid unexpected storage issues.

• If you want to delete logs and recover storage space on disk, then you can deactivate PITR without re-enabling it. However, decreasing the storage used doesn't shrink the size of the disk provisioned for the instance.

• Logs are purged once daily, not continuously. Setting log retention to two days means that at least two days of logs, and at most three days of logs, are retained. We recommend setting the number of backups to one more than the days of log retention.

  For example, if you specify 7 for the value of the transactionLogRetentionDays parameter, then for the backupRetentionSettings parameter, set the number of retainedBackups to 8.

For more information about PITR, see Point-in-time recovery (PITR).

After you complete the switch of the storage location of transaction logs to Cloud Storage, you can free up disk space by reducing the values of the expire_logs_days or binlog_expire_logs_seconds flags. To check the status of the switch, see Check the storage location of transaction logs used for PITR. If you want additional logs to be available on disk— for example, to browse the binary logs with the mysqlbinlog utility— then increase the values of these flags. Cloud SQL retains binary logs on disk for the minimum of the transaction log retention days or the values set for the flags. For more information on how logs for PITR are stored after the switch and how to free up disk space, see Logs after the switch to Cloud Storage.

When you create a new instance in the Google Cloud console, both

and

are automatically enabled.

The following procedure enables PITR on an existing primary instance.

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to enable PITR on and click Edit.
3. Under Customize your instance, expand the Data Protection section.
4. Select the Enable point-in-time recovery checkbox.
5. In the Days of logs field, enter the number of days to retain logs, from 1-35 for Cloud SQL Enterprise Plus edition, or 1-7 for Cloud SQL Enterprise edition.
6. Click Save.

1. Display the instance overview:

   gcloud sql instances describe INSTANCE_NAME

2. If you see enabled: false in the backupConfiguration section, enable scheduled backups:

   gcloud sql instances patch INSTANCE_NAME \
   --backup-start-time=HH:MM

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

3. Enable PITR:

   gcloud sql instances patch INSTANCE_NAME \
   --enable-bin-log

   If you're enabling PITR on a primary instance, you can also configure the number of days for which you want to retain transaction logs by adding the following parameter:

   --retained-transaction-log-days=RETAINED_TRANSACTION_LOG_DAYS

4. Confirm your change:

   gcloud sql instances describe INSTANCE_NAME

   In the backupConfiguration section, you see binaryLogEnabled: true if the change was successful.

To enable PITR, use a Terraform resource.

• If you create a Cloud SQL Enterprise Plus edition instance, then PITR is enabled by default, regardless of the method used for creation. If you want to disable the feature, then you must do so manually.
• If you create a Cloud SQL Enterprise edition instance in the Google Cloud console, then PITR is enabled by default. Otherwise, if you create the instance by using the gcloud CLI, Terraform, or the Cloud SQL Admin API, then PITR is disabled by default. In this case, if you want to enable the feature you must do so manually.

To apply your Terraform configuration in a Google Cloud project, complete the steps in the following sections.

1. Launch Cloud Shell.

2. Set the default Google Cloud project where you want to apply your Terraform configurations.

   You only need to run this command once per project, and you can run it in any directory.

   export GOOGLE_CLOUD_PROJECT=PROJECT_ID

   Environment variables are overridden if you set explicit values in the Terraform configuration file.

Each Terraform configuration file must have its own directory (also called a root module).

1. In Cloud Shell, create a directory and a new file within that directory. The filename must have the .tf extension—for example main.tf. In this tutorial, the file is referred to as main.tf.

   mkdir DIRECTORY && cd DIRECTORY && touch main.tf

2. If you are following a tutorial, you can copy the sample code in each section or step.

   Copy the sample code into the newly created main.tf.

   Optionally, copy the code from GitHub. This is recommended when the Terraform snippet is part of an end-to-end solution.

3. Review and modify the sample parameters to apply to your environment.
4. Save your changes.
5. Initialize Terraform. You only need to do this once per directory.

   terraform init

   Optionally, to use the latest Google provider version, include the -upgrade option:

   terraform init -upgrade

1. Review the configuration and verify that the resources that Terraform is going to create or update match your expectations:

   terraform plan

   Make corrections to the configuration as necessary.

2. Apply the Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

   Wait until Terraform displays the "Apply complete!" message.

3. Open your Google Cloud project to view the results. In the Google Cloud console, navigate to your resources in the UI to make sure that Terraform has created or updated them.

To delete your changes, do the following:

1. To disable deletion protection, in your Terraform configuration file set the deletion_protection argument to false.

   deletion_protection =  "false"

2. Apply the updated Terraform configuration by running the following command and entering yes at the prompt:

   terraform apply

1. Remove resources previously applied with your Terraform configuration by running the following command and entering yes at the prompt:

   terraform destroy

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

• PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
• INSTANCE_NAME: the name of the primary or read replica instance that you're configuring for high availability
• START_TIME: the time (in hours and minutes)

HTTP method and URL:

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

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "startTime": "START_TIME",
      "enabled": true,
      "binaryLogEnabled": true
    }
  }
}

To send your request, expand one of these options:

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

curl -X PATCH \
     -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/INSTANCE_NAME"

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

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

• PROJECT_ID: the ID or project number of the Google Cloud project that contains the instance
• INSTANCE_NAME: the name of the primary or read replica instance that you're configuring for high availability
• START_TIME: the time (in hours and minutes)

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "startTime": "START_TIME",
      "enabled": true,
      "binaryLogEnabled": true
    }
  }
}

To send your request, expand one of these options:

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

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

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1beta4/projects/PROJECT_ID/instances/INSTANCE_NAME" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

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:

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

   Go to Cloud SQL Instances

2. Find the row of the instance to clone.
3. In the Actions column, click the more_vert More Actions menu.
4. Click Create clone.
5. 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.

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.

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:

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"

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:

{
  "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.

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:

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"

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:

{
  "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"
}

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.

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.

For an available instance, you can perform PITR to the latest time. If the instance is unavailable and the instance logs are stored in Cloud Storage, then you can retrieve the latest recovery time and perform the PITR to that time. In both cases, you can restore the instance to a different primary or secondary zone by providing values for the preferred zones.

Get the latest time to which you can recover a Cloud SQL instance that's not available.

Replace INSTANCE_NAME with the name of the instance that you're querying.

gcloud sql instances get-latest-recovery-time INSTANCE_NAME

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:

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"

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",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

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:

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"

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",
  "latestRecoveryTime": "2023-06-20T17:23:59.648821586Z"
}

Using a timestamp is the recommended approach for performing 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.

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to recover and click Create clone.
3. Optionally, on the Create a clone page, update the ID of the new clone.
4. Select Clone from an earlier point in time.
5. Enter a PITR time.
6. Click Create clone.

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'

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:

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"

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:

{
  "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"
}

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:

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"

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:

{
  "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"
}

Preview — Enhanced backups

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. You can process personal data for this feature as outlined in the Cloud Data Processing Addendum, subject to the obligations and restrictions described in the agreement under which you access Google Cloud. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

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.

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to recover and click Create clone.

3. Select Clone from an earlier point in time.

4. Enter a PITR time.

5. Click Create clone.

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 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 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.

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to deactivate and select Edit.
3. Under Customize your instance, expand the Data Protection section.
4. Clear Enable point-in-time recovery.
5. Click Save.

1. Deactivate point-in-time recovery:

   gcloud sql instances patch INSTANCE_NAME \
   --no-enable-bin-log

2. Confirm your change:

   gcloud sql instances describe INSTANCE_NAME

   In the backupConfiguration section, you see binaryLogEnabled: false if the change was successful.

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

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

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id

Request JSON body:

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

To send your request, expand one of these options:

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

curl -X PATCH \
     -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/instance-id"

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

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

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

HTTP method and URL:

PATCH https://sqladmin.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:

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

curl -X PATCH \
     -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/instance-id"

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/project-id/instances/instance-id" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

You can check where your Cloud SQL instance is storing the transaction logs used for PITR.

To determine whether your instance stores logs for PITR on disk or Cloud Storage, use the following command:

   gcloud sql instances describe INSTANCE_NAME
   

Replace INSTANCE_NAME with the name of the instance.

For multiple instances in the same project, you can also check the storage location of the transaction logs. To determine the location for multiple instances, use the following command:

   gcloud sql instances list --show-transactional-log-storage-state
   

Example response:

NAME  DATABASE_VERSION LOCATION       TRANSACTIONAL_LOG_STORAGE_STATE
my_01 MYSQL_8_0        us-central-1     DISK
my_02 MYSQL_8_0        us-central-1     CLOUD_STORAGE
...
   

In the output of the command, the transactionalLogStorageState field or the TRANSACTIONAL_LOG_STORAGE_STATE column provides information about where the transaction logs for PITR are stored for the instance. The possible transaction log storage states are the following:

• DISK: the instance stores the transaction logs used for PITR on disk. If you upgrade a Cloud SQL Enterprise edition instance to Cloud SQL Enterprise Plus edition, then the upgrade process switches the log storage location to Cloud Storage automatically. For more information, see Upgrade an instance to Cloud SQL Enterprise Plus edition by using in-place upgrade. You can also choose to switch the storage location by using gcloud CLI or the Cloud SQL Admin API without upgrading the edition of your instance and without incurring any downtime. For more information, see Switch transaction log storage to Cloud Storage.
• SWITCHING_TO_CLOUD_STORAGE: the instance is switching the storage location for the PITR transaction logs to Cloud Storage.
• SWITCHED_TO_CLOUD_STORAGE: the instance has completed the switching the storage location for PITR transaction logs from disk to Cloud Storage.
• CLOUD_STORAGE: the instance stores the transaction logs used for PITR in Cloud Storage.

If your instance stores its transaction logs used for PITR on disk, then you can switch the storage location to Cloud Storage without incurring any downtime. The overall process of switching the storage location takes approximately the duration of the transaction log retention period (days) to complete. As soon as you start the switch, transaction logs start accruing in Cloud Storage. During the operation, you can check the status of the overall process by using the command in Check the storage location of transaction logs used for PITR.

After the overall process of switching to Cloud Storage is complete, Cloud SQL uses transaction logs from Cloud Storage for PITR.

To switch the storage location to Cloud Storage, use the following command:

   gcloud sql instances patch INSTANCE_NAME \
      --switch-transaction-logs-to-cloud-storage
   

Replace INSTANCE_NAME with the name of the instance. The instance must be a primary instance and not a replica instance. The response is similar to the following:

The following message is used for the patch API method.
{"name": "INSTANCE_NAME", "project": "PROJECT_NAME", "switchTransactionalLogsToCloudStorageEnabled": "true"}

Patching Cloud SQL instance...done.
Updated
[https://sqladmin.prod.googleapis.com/v1/projects/PROJECT_NAME/instances/INSTANCE_NAME].
   

If the command returns an error, then see Troubleshoot the switch to Cloud Storage for possible next steps.

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

• PROJECT_ID: the project ID.
• INSTANCE_ID: the instance ID. The instance must be a primary instance and not a replica instance.

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID

Request JSON body:

{
   "switchTransactionLogsToCloudStorageEnabled": true
}

To send your request, expand one of these options:

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

curl -X PATCH \
     -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/INSTANCE_ID"

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

If the request returns an error, then see Troubleshoot the switch to Cloud Storage for possible next steps.

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

• PROJECT_ID: the project ID.
• INSTANCE_ID: the instance ID. The instance must be a primary instance and not a replica instance.

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID

Request JSON body:

{
   "switchTransactionLogsToCloudStorageEnabled": true
}

To send your request, expand one of these options:

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

curl -X PATCH \
     -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/INSTANCE_ID"

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

If the request returns an error, then see Troubleshoot the switch to Cloud Storage for possible next steps.

For replication purposes, Cloud SQL still retains copies of binary logs on disk.

If you want to browse binary logs with the mysqlbinlog utility, then storing binary logs on disk is useful.

If you configured the expire_logs_days or binlog_expire_logs_seconds flags on your instance before the switch, then the configured values remain intact.

After the switch, since the binary logs that are used to perform PITR are now stored in Cloud Storage, ensure that the values of the flags reflect the retention of transaction logs on disk that you expect. Cloud SQL only retains logs on disk for the minimum value of one of the following:

• the transactionLogRetentionDays PITR configuration setting before the switch. The default value for this setting is 7 days.
• the expire_logs_days or binlog_expire_logs_seconds flags that you set on your instance manually.

If you want to save disk space, then after the switch process completes, configure the value of the expire_logs_days or binlog_expire_logs_seconds flags to 1 day to reduce your allocated disk size and disk storage costs. For more information about transaction log storage and PITR, see Log storage for PITR.

For more information about how to check disk usage, see Logs and disk usage.

To set the number of days to retain binary logs:

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

   Go to Cloud SQL Instances

2. Open the more actions menu for the instance you want to set the transaction log on and select Edit.
3. Under Customize your instance, expand the Data Protection section.
4. In the Enable point-in-time recovery section, expand Advanced options.
5. Enter the number of days to retain logs, from 1-35 for Cloud SQL Enterprise Plus edition or 1-7 for Cloud SQL Enterprise edition.
6. Click Save.

Edit the instance to set the number of days to retain binary logs.

Replace the following:

• INSTANCE_NAME: The name of the instance you want to set the transaction log on.

• DAYS_TO_RETAIN: The number of days of transaction logs to keep. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days.

  If you don't specify a value, then Cloud SQL uses the default value. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.

  gcloud sql instances patch INSTANCE_NAME 

    --retained-transaction-log-days=DAYS_TO_RETAIN
  

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

• PROJECT_ID: the project ID.
• INSTANCE_ID: the instance ID.

• DAYS_TO_RETAIN: the number of days to retain transaction logs. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days.

  If no value is specified, then the default value is used. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "transactionLogRetentionDays": "DAYS_TO_RETAIN"
    }
  }
}

To send your request, expand one of these options:

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

curl -X PATCH \
     -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/INSTANCE_ID"

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/v1/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

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

• PROJECT_ID: the project ID.
• INSTANCE_ID: the instance ID.

• DAYS_TO_RETAIN: the number of days to retain transaction logs. For Cloud SQL Enterprise Plus edition, the valid range is between 1 and 35 days, with a default of 14 days. For Cloud SQL Enterprise edition, the valid range is between 1 and 7 days, with a default of 7 days.

  If no value is specified, then the default value is used. This is valid only when PITR is enabled. Keeping more days of transaction logs requires a bigger storage size.

HTTP method and URL:

PATCH https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID

Request JSON body:

{
  "settings":
  {
    "backupConfiguration":
    {
      "transactionLogRetentionDays": "DAYS_TO_RETAIN"
    }
  }
}

To send your request, expand one of these options:

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

curl -X PATCH \
     -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/INSTANCE_ID"

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 PATCH `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://sqladmin.googleapis.com/sql/v1beta4/projects/PROJECT_ID/instances/INSTANCE_ID" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

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

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 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.

1. 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.

2. Show the binary log files for the instance:

   SHOW BINARY LOGS;
   

3. 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.

4. 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;
   

5. 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.

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

1. 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 \

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.

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:

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"

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:

{
  "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"
}

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:

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"

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:

{
  "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"
}

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,

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 following table lists possible errors that might return with the INVALID REQUEST code when you switch the storage location of the transaction logs from disk to Cloud Storage.

Wait for the switch operation to complete.

To verify the status of the operation and the storage location of the transaction logs, run the command in Check the storage location of transaction logs used for PITR.

• Configure flags on your clone

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025-08-14 UTC.

[[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2025-08-14 UTC."],[],[]]

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4