The MySQL Heatwave Database Service in the Oracle Cloud Infrastructure (OCI) permits users to take automatic, periodic backups of their MySQL database systems (DB Systems). However, if the backup were configured to occur in the middle of the night and the data-altering event occurs during the middle of the day, there could be hours of data changes lost and must be recreated.

Fortunately, DB Systems have a feature that you can use to protect yourself against such recovery events to make the recovery much easier and much less work. It is named point-in-time recovery (PITR) and is a combination of replication technologies and backup strategies.

In this blog, we will learn about PITR beginning with a brief overview of how PITR works on-prem. This will give you the opportunity to understand PITR and how to use it to your advantage for data recovery.

What is PITR, and how does it work?

Those who have their own on-prem MySQL Servers have at their disposal a feature in MySQL called binary logs, which record the changes to your data in a special binary format that can be replayed if data recovery is needed. Like physical backups, binary logs are not human readable, but unlike physical backups, they must be processed one event (data change) at a time, making them cumbersome for use as a recovery mechanism.

The best way to enable binary logging is to place the following line in your my.cnf (or my.ini) configuration file and restart your MySQL server.

log_bin=ON

You can also determine if binary logging is enabled using the following command. Notice that the value is ON, which means that binary logging is enabled.

Interestingly, binary logs are one of the key components in enabling the high-availability features in MySQL. For more information about binary logging, see "The Binary Log" section in the online MySQL reference manual. For more information about high availability in MySQL, see "Group Replication" section in the online MySQL reference manual.

Once binary logging is turned on, your MySQL server records each event as it is processed and writes it in the log. The logs are designed so that they can be rotated manually or automatically to reduce file sizes.

When combined with regular backups, you can setup your backup routines to rotate the binary logs immediately before a snapshot of the data. This enables you to begin recording which binary logs have been created since the last backup.

Should an event occur where you must restore to a period between the automated backups, you can restore that latest backup and then apply the logs to the point of the event by replaying (executing or applying) the binary logs that were created since the last backup. For those using this mechanism on their on-prem servers, you can use binary logging tools to help locate a precise location to restore your data.

This mechanism, PITR, is available in DB Systems and is fully automated. You do not need to know anything about binary logs, which backup to restore, or any such details – all of it is handled by the PITR automation mechanisms. In fact, it is so easy to use it, you merely turn it on and forget about it (until you must recover your data).

However, automation of complex tasks always results in some restrictions in making it consistent and dependable. The limitation imposed for PITR is the recovery window. Currently, you can recover your data on your DB System with PITR enabled to any 5-minute period. Thus, at most data changes you may have to recover is 5 min, which is a small price to pay for automatic recovery of data between backups. This is because the binary logs are copied to external storage (object storage) every 5 min. Recall that the binary logs are where the changes to the data are recorded. Saving the binary logs allows PITR to “replay” the changes from the last backup before the target recovery date.

The oldest data you can recover depends on how long you have PITR enabled, and the value set for the Backup retention period specified in the Backup Policy as shown in Figure 1.

Now that we know a little more about PITR, let’s see how to set it up on our DB Systems.

How to enable PITR on an existing DB System

You can setup PITR at any time for a DB System by enabling it on the DB System details page. The feature requires the activation of automatic backups, so if your DB System has that feature turned off, you will need to enable both backup policy and PITR policy.

To check if your DB System has PITR or automatic backups enabled, visit the DB System details page and look for the Backup section as shown in Figure 2.

Enable link as shown. This will open a new Edit Backup Plan dialog where you can configure the feature. You can set the backup retention period (in days), enable PITR by ticking the Enable point in time restore tick box, and choose the backup window. Once your settings are ready, you can click the Save Changes button to enable PITR and automatic backups (if applicable). Figure 3 shows the Edit Backup Plan dialog.

If you have a DB System running MySQL 8.0.28, you will need to update to the latest version to ensure you have the latest PITR capabilities. You can upgrade your DB System on the details page by clicking on the Edit link next to the MySQL version and choose the version you want to upgrade to from the list.

Once the changes are saved, the DB System will enter an update period so that the DB System automation can complete the changes to the configuration in the background. Once complete, you will see the automatic backups and PITR enabled, as shown in Figure 4.

How to enable PITR when creating a DB System

Configure DB System dialog in the Backup section, as shown in Figure 5.

Wait, how do I know what backups I have taken?

One of the great benefits of PITR is that do not need to know which backup to restore; PITR figures that out for you based on the time you specify when recovering using PITR. The backups used in PITR are automatic backups, which is why automatic backups are required when you enable PITR.

However, there are also manual backups that you can take at any time. These manual backups will not conflict with PITR as they are not part of the PITR feature. You should consider taking a manual backup any time you identify risk for your data such as rolling out new schemas, new applications, data ingestion, etc.

If you want to see the backups taken (both automatic and manual), you can list your backups by selecting the Backups list in the Resources menu on the DB System details page, as shown in Figure 6. Here, we see two manual backups and an automatic backup.

Now that we know how to set up PITR, we can observe a small demonstration on how to recover a DB System using PITR.

Using PITR to recover data

The recovery of a DB System using PITR is very similar to recovery using any normal backup. The difference is in how you select the backup. If you choose to recover using a PITR entry, you will need to select the specific time you want to restore. However, to make this demonstration feasible, we will need to introduce an event from which we want to recover. A simple DROP DATABASE or similar SQL statement will suffice. Here, I have issued a DROP DATABASE sakila. I issued this command by logging into a compute instance using the public IP address as shown on the compute instance details page then launched MySQL Shell on the compute instance to connect to the DB System. I then issued the DROP command at approximately 21:06 UTC.

I logged into the compute instance with the following command.

ssh -i c:\users\<user>\.ssh\ssh-key-2022-08-16.key opc@150.136.69.126

From there, I logged into MySQL using MySQL Shell with the following and dropped the database.

[opc@connection-instance ~]$ mysqlsh --sql mysql_admin@10.0.1.226:33060 MySQL Shell 8.0.30 Copyright (c) 2016, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. MySQL 10.0.1.226:33060+ ssl SQL > DROP DATABASE sakila; Query OK, 23 rows affected (0.1493 sec)

Ok, now we have our event that altered the data irrevocably. Now, we can attempt to restore to a point before 21:06 UTC. We can do so by clicking on Restore to New DB System using the context menu for the backup in our list, as shown in Figure 7. If you have more backups from which to choose, you should choose the one dated most recently before the event from which you want to recover.

This launches a new dialog where you can change several parameters for the new DB System. The first section of the dialog concerns the backup settings, as shown in Figure 8.

This is the area where you can specify that you want to restore to a specific point-in-time. Begin by clicking on the Restore from DB system at a point in time. Notice that this allows you to choose the latest PITR period or a specific period. For this demonstration, we tick the Select a specific point in time radio button. Finally, you can enter a period that occurs before your event. Here, the last entry available is 21:03 UTC. 

Once you click the Restore button and the DB System is ready, we can log in and see that the sakila database is indeed present and the unwanted data change event has been recovered. Recall, there is much going on under the hood. The DB System is restored from the last known good backup and once restored, by applying the binary logs recorded since the backup. All this occurs without any intervention from the user. How cool is that?

Recall, we must first log into a compute instance before launching the MySQL Shell using the Private IP Address of the restored DB System as shown in Figure 9.

The following demonstrates how to test to ensure that the sakila database is present.

That’s it! We now know how to restore a DB System to a specific period using point-in-time recovery.

Limitations

The PITR feature has a few limitations that you may want to consider when planning your MySQL Heatwave DB Systems. The following summarize the limitations of PITR today. See the "Limitations" section in the OCI online documentation for more information and specifics of the limitations of PITR.

• High Availability (HA) DB Systems are not supported. Thus, you cannot use PITR to recover an HA DB System nor can you use PITR to restore to a new HA DB System.
• The Automatic Backup feature is required for PITR.
• The time to restore when restoring using PITR must be after the time PITR is enabled.

Conclusion

Point-in-Time recovery is one of those features, such as automated backups, which can increase your ability to recover from data disasters such as human error (e.g., a DELETE SQL statement without a WHERE clause) or unlikely system failures resulting in the loss of data. PITR reduces your risk of data loss due to events such as these. In fact, you can protect your data from loss or changes up to a 5-minute window, which means you do not have to worry about trying to recover from hours of valid data entry or changes to recover your data. With PITR, the most data you would ever have to recover from reentry or reprocessing is five minutes. Once you’ve used PITR to recover your data, you are likely to enable it on all your critical DB Systems.

For more information about PITR, see the "Restoring With Point In Time Restore Feature" section in the OCI online documentation.