Google Cloud Platform (GCP) offers robust backup solutions for SQL databases, providing users with the confidence and peace of mind to safeguard their valuable data. In this article, we will explore the backup process for both the entire SQL instance and individual databases within the instance. We will provide step-by-step instructions on how to perform these backups, ensuring data durability and recoverability.
In Google Cloud Platform (GCP), Cloud SQL provides two main types of backups to ensure data protection and recoverability: automated backups and on-demand backups. Let’s explore each type of backup in Cloud SQL.
Types of backups in Cloud SQL
- Automated Backups.
- On Demand Backups (Manual Backups)
- Point-in-Time Recovery
Automated Backups
- Automated backups are an essential feature of Cloud SQL, enabling regular and automated snapshots of your database instance. These backups are created according to a specified backup window, which is a user-defined time range when backups are taken.
- Cloud SQL automatically manages the retention of automated backups, ensuring that a certain number of backups are retained based on the configured retention period. By default, Cloud SQL retains automated backups for up to 7 days.
- The automated backups capture the entire database instance, including all the databases within it, allowing for comprehensive point-in-time recovery.
On-Demand Backups (Manual Backups)
- In addition to automated backups, Cloud SQL also allows users to create on-demand backups as needed. These manual backups provide additional control and flexibility, allowing you to initiate backups at any time outside of the regular backup window.
- On-demand backups are useful before making significant changes to your database schema, performing critical operations, or conducting tests. They provide an extra layer of data protection and the ability to restore to a specific point in time before a change was made.
- With on-demand backups, users can specify a unique backup name, and they have control over the retention period, allowing for custom retention settings beyond the default 7-day retention of automated backups.
Steps to take Backup of Entire SQL Instance
Step 1: Access the Google Cloud Console:
- Open your web browser and navigate to the Google Cloud Console https://console.cloud.google.com/.
Step 2: Navigate to Cloud SQL:
- From the GCP Console, select “SQL” from the left-hand navigation pane to access the Cloud SQL dashboard.
Step 3: Select the SQL Instance:
- Choose the SQL instance you want to back up from the list of instances displayed on the Cloud SQL dashboard.
Step 4: Go to Backups:
- In the instance details page, click on the “Backups” tab to manage backups for the selected SQL instance.
Step 5: Initiate Backup:
- Click on the “Create Backup” button to initiate a backup of the entire SQL instance.
- Specify a unique backup name for identification purposes.
Step 6: Review and Confirm:
- Review the backup configuration settings, including the backup retention period and backup start time.
Steps to take Backup for individual Database
Step 1: Access the Google Cloud Console:
- Open your web browser and navigate to the Google Cloud Console https://console.cloud.google.com/.
Step 2: Navigate to Cloud SQL:
- From the GCP Console, select “SQL” from the left-hand navigation pane to access the Cloud SQL dashboard.
Step 3: Select the SQL Instance:
- Choose the SQL instance you want to back up from the list of instances displayed on the Cloud SQL dashboard.
Step 4: Click on Export tab:
- Select BAK file in file format option (for PostgreSQL file format will be SQL)
- Select the Database which you want to Export.
- Now Select the Bucket name where you want to export the DB.
- Click on EXPORT.
It’s important to note that Cloud SQL backups are stored in Google Cloud Storage, ensuring data durability and availability. These backups are encrypted to ensure data security in transit and at rest. By leveraging both automated and on-demand backups in Cloud SQL, users can confidently protect their critical data and ensure they have the ability to recover from unexpected incidents or data loss scenarios.
Restoring the Backup
Restoring entire SQL Instance
If some error occurs In a SQL instance and the whole instance got corrupted, then how to recover the entire SQL instance.
Step 1: Navigate to Cloud SQL:
- From the GCP Console, select “SQL” from the left-hand navigation pane to access the Cloud SQL dashboard.
Step 3: Select the SQL Instance:
- Choose the SQL instance you want to restore.
Step 4: Click Backup tab:
- Here you will find all the daily backup. Click on the Restore.
Restoring Database Backup
For restoring the database backup, we need a Bak(For MSSQL) or SQL(For PostgreSQL) file which should be stored in a storage bucket.
Step 1: Navigate to Cloud SQL:
- From the GCP Console, select “SQL” from the left-hand navigation pane to access the Cloud SQL dashboard.
Step 2: Select the SQL Instance:
- Choose the SQL instance you want to restore.
Step 3: Delete the existing DB::
- Go to the Database tab.
- Delete the Database which you are going to restore in the next step.
Step 4: Click the IMPORT tab:
- Choose the Bak file from the bucket where the file was created earlier (Export Step).
- Choose the file format to BAK, enter database name (Same name of database in same format which we have deleted in previous step).
- Finally, Click on RESTORE.
Recover SQL instance using Point-in-Time Recovery
PITR allows users to restore their SQL instance including all databases to a specific point in time within a defined retention period. This precision in data recovery is crucial for minimizing data loss in case of accidental data deletion, data corruption, or erroneous database changes. Without PITR, users would only have access to the most recent backup, potentially resulting in the loss of valuable data changes.
- Both automated and on-demand backups in Cloud SQL enable point-in-time recovery. This means that you can restore your database instance or individual databases within the instance to a specific point in time within the retention period.
- Point-in-time recovery is valuable in scenarios where you need to roll back your data to a known good state before an issue occurred or to recover from accidental data deletion or corruption.
- Cloud SQL provides a straightforward interface to choose the desired recovery point when initiating a restore from a backup.
Here I am going to explain Point-in-time Recovery furthermore. Before that Let me explain what Transaction Logs are.
Transaction Logs – Along with automated backups, GCP captures changes made to the DB in the transaction log files. These logs store a record of all modifications (inserts, updates, deletes) to the DB.
Steps to perform Point-in-Time Recovery
Step 1: Navigate to Cloud SQL:
- From the GCP Console, select “SQL” from the left-hand navigation pane to access the Cloud SQL dashboard.
Step 2: Select the SQL Instance:
- Choose the SQL instance you want to back up from the list of instances displayed on the Cloud SQL dashboard.
Step 3: Click on the Clone tab:
- Give a new Instance name (You can’t perform Point-in-Time recovery on the same instance). And then Select Clone from an earlier point in time. Now give the Date and time(till which time you want to restore).
What happens during this recovery at the back-end – To Perform point-in-time recovery, GCP combines the automated backups and transaction logs files. Let’s understand by an example.
Example – Let’s say our SQL instance backup is happening at 12 AM everyday. At 3AM our SQL instance got corrupted and now we want to do point-in-time recovery. Here’s how it done by GCP:
- Restore Latest Backup: GCP starts by restoring the latest backup taken at 12AM. This brings the DB back to the state it was at 12AM.
- Apply Transaction Logs: GCP then applies the transaction log files recorded between 12AM and 3AM. These logs contain the changes made to the DB during that time period. By applying the transactions from the log files, GCP brings the DB forward to the desired recovery point at 3 AM.
- Recovery Point: After applying the transaction logs, GCP completes the recovery process. The resulting DB state reflects the data and modification as they existed at 3AM.
Conclusion
Taking backups of SQL databases in Google Cloud Platform is a critical practice to ensure data protection and recoverability. By following the step-by-step instructions for backing up the entire SQL instance and individual databases, you can safeguard your valuable data and restore it to a known good state in the event of data loss or corruption. Regularly schedule backups and verify the restore process to ensure the resilience and reliability of your SQL databases in GCP. With these backup practices in place, you can confidently leverage the power of Google Cloud Platform for your data storage and management needs.
That’s all for now.
Thank you for reading!!
Stay tuned for more articles on Cloud computing and DevOps. Don’t forget to follow me for regular updates and insights.
Pingback: Cloud SQL in GCP(Google Cloud Platform) - The CloudOps