Use the Conformity Knowledge Base AI to help improve your Cloud Posture

Enable Point-in-Time Recovery for MySQL Database Instances

Trend Cloud One™ – Conformity is a continuous assurance tool that provides peace of mind for your cloud infrastructure, delivering over 1000 automated best practice checks.

Risk Level: Medium (should be achieved)

Ensure that Point-in-Time Recovery (PITR) feature is enabled for all MySQL database instances deployed within your Google Cloud Platform (GCP) account, in order to allow you to recover data from a specific point in time at a minimal cost. Automated backups, along with binary logging, must be enabled for your MySQL database instances before you can turn on Point-in-Time Recovery.

Security
Reliability

Point-in-Time Recovery (PITR) helps you recover a Google Cloud MySQL database instance to a specific point in time, down to a fraction of a second. This feature is extremely useful in case an error causes a loss of data or the database gets corrupted, as you can recover your database to its working state (before the error occurred).


Audit

To determine if your Google Cloud MySQL database instances have Point-in-Time Recovery feature enabled, perform the following actions:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and MySQL then press Enter, to list only the MySQL database instances created for the selected GCP project.

05 Click on the identifier (ID) of the database instance that you want to examine.

06 In the navigation panel, select Backups to access the database backup configuration settings available for the selected instance.

07 In the Settings section, make sure that Automated backups is set to Enabled, then check the Point-in-time recovery feature configuration status. If the configuration status is set to Disabled, the Point-in-Time Recovery (PITR) feature is not enabled for the selected Google Cloud MySQL database instance.

08 Repeat step no. 5 – 7 to check the PITR feature status for other database instances available within the selected project.

09 Repeat steps no. 2 – 8 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run projects list command (Windows/macOS/Linux) using custom query filters to list the IDs of all the Google Cloud Platform (GCP) projects available in your cloud account:

gcloud projects list
	--format="table(projectId)"

02 The command output should return the requested GCP project identifiers:

PROJECT_ID
cc-web-project-123123
cc-ml-project-123123

03 Run sql instances list command (Windows/macOS/Linux) using custom filtering to describe the name of each MySQL database instance created for the selected Google Cloud project:

gcloud sql instances list
	--project cc-web-project-123123
	--filter='DATABASE_VERSION:MYSQL*'
	--format="(NAME)"

04 The command output should return the requested database instance name(s):

NAME
cc-mobile-db-instance
cc-user-db-instance

05 Run sql instances describe command (Windows/macOS/Linux) using the name of the MySQL database instance that you want to examine as identifier parameter and custom query filters to describe the binary log configuration status available for the selected database instance. The binary log is a set of log files that contain information about data modifications made to a MySQL database instance:

gcloud sql instances describe cc-mobile-db-instance
	--format="value(settings.backupConfiguration.binaryLogEnabled)"

06 The command output should return the requested configuration status (True for enabled, False for disabled):

False

If the sql instances describe command output returns False, the MySQL binary log is not configured for the selected resource, therefore the Point-in-Time Recovery (PITR) feature is not enabled for the selected Google Cloud MySQL database instance.

07 Repeat step no. 5 and 6 to verify the binary log feature status for other MySQL instances provisioned for the selected project.

08 Repeat steps no. 3 – 7 for each project available within your Google Cloud account.

Remediation / Resolution

To enable and configure Point-in-Time Recovery (PITR) for your Google Cloud MySQL database instances, perform the following actions:

Using GCP Console

01 Sign in to Google Cloud Management Console.

02 Select the Google Cloud Platform (GCP) project that you want to access from the console top navigation bar.

03 Navigate to Cloud SQL Instances dashboard at https://console.cloud.google.com/sql/instances.

04 Click inside the Filter tree box, select Type and MySQL then press Enter, to display only the MySQL database instances available for the selected project.

05 Click on the identifier of the database instance that you want to reconfigure.

06 In the navigation panel, select Backups to access the database backup configuration settings available for the selected instance.

07 Click on the Edit button available in the Settings section, to edit the automated backups and point-in-time recovery settings.

08 On the Edit backups settings panel, select Enable point-in-time recovery to turn on binary logging (required for replication), and enable the Point-in-Time Recovery (PITR) feature for the selected database instance. Note: Changing PITR settings will require your instance to restart. The restart process will shut down your instance, along with all its connections, open files, and running operations.

09 Click SAVE AND RESTART to apply the configuration changes and restart the instance.

10 Repeat step no. 5 – 9 to enable Point-in-Time Recovery for other MySQL instances available within the selected project.

11 Repeat steps no. 2 – 10 for each project deployed in your Google Cloud account.

Using GCP CLI

01 Run sql instances patch command (Windows/macOS/Linux) using the name/ID of the Google Cloud MySQL database instance that you want to reconfigure as identifier parameter, to turn on binary logging (required for replication) and enable the Point-in-Time Recovery (PITR) feature for the selected database instance. The binary log is a set of log files that contain information about data modifications made to a MySQL database instance. Before you can perform a point-in-time recovery, the binary log feature must be enabled for the selected instance, and a binary log file name and position that correspond to the point in time you want to recover the instance to, must be identified: Note: Enabling the MySQL binary log will require the selected instance to restart. The restart process will shut down the instance, along with all its connections, open files, and running operations:

gcloud sql instances patch cc-mobile-db-instance
	--enable-bin-log

02 The output should return the sql instances patch command request status:

The following message will be used for the patch API method.

{"name": "cc-mobile-db-instance", "project": "cc-web-project-123123", "settings": {"backupConfiguration": {"binaryLogEnabled": true, "enabled": true, "location": "us", "startTime": "00:00"}}}

Patching Cloud SQL instance...done.
Updated [https://sqladmin.googleapis.com/sql/v1beta4/projects/cc-web-project-123123/instances/cc-mobile-db-instance].

03 Repeat step no. 1 and 2 to enable the Point-in-Time Recovery feature for other MySQL database instances provisioned for the selected project.

04 Repeat steps no. 1 – 3 for each project created within your Google Cloud account.

References

Publication date Apr 21, 2021