The MySQL Heatwave Service (MHS) in Oracle Cloud Infrastructure (OCI) includes a backup
feature for MySQL HeatWave database systems (DB systems). However, these backups are
scheduled either once in every 24 hours or can be created on-demand. If any event occurs
in between to alter the data, the customer could potentially loose data worth minutes or hours.
To mitigate this, MHS provides a feature called point-in-time recovery (PITR) for DB systems
to facilitate a more straightforward recovery process where the customer can recover data
upto the last 5 minutes.
As a bonus, multiple appliers have made the existing recovery process faster.
Introduction to PITR
With the point-in-time recovery (PITR) feature, the customer will be able to restore the data from
a given DB system (DB1) to a new DB system (DB2) up to a given point in time between some
moment in the past up to 5 minutes before the last update on DB1.
PITR uses automatic backups to perform recovery. An MHS DB system generates a binary log file
every 5 minutes, and PITR includes the backup of these binary log files on a DB system with
PITR enabled. These binary logs are retained as per the retention-in-days variable defined by the
customer for automatic backup in the DB system backup policy and are used for the recovery.
How do I enable PITR?
PITR can be enabled anytime on an existing DB system with a single click on the DB system
details page.
1. Click the More actions button and select the Edit backup plan. This will display the Edit
Backup Plan dialog, as shown below.
2. That’s it; PITR is enabled for the DB system with a single click, as shown below.
Also, PITR is enabled by default for newly created DB systems.
Restoring a DB System Using PITR
The following steps show how to perform PITR using the DB system.
- On the OCI console, open the navigation menu and select Databases. Under MySQL
HeatWave, click DB systems, as shown below.
MySQL HeatWave Faster Point-in-time Recovery > dbSystemsNavigation.png” data-unresolved-comment-count=”0″ draggable=”false” height=”250″ src=”https://confluence.oci.oraclecorp.com/download/attachments/3741277594/dbSystemsNavigation.png?version=1&modificationDate=1709295932365&api=v2″ title=”MySQL Database Service > MySQL HeatWave Faster Point-in-time Recovery > dbSystemsNavigation.png”>
-
Choose your compartment to access the DB systems in the appropriate compartment, as shown below.
MySQL HeatWave Faster Point-in-time Recovery > compartments.png” data-unresolved-comment-count=”0″ draggable=”false” height=”250″ src=”https://confluence.oci.oraclecorp.com/download/thumbnails/3741277594/compartments.png?version=1&modificationDate=1709295962746&api=v2″ title=”MySQL Database Service > MySQL HeatWave Faster Point-in-time Recovery > compartments.png”>
- Choose the DB system from which you want to perform PITR, as shown below.
MySQL HeatWave Faster Point-in-time Recovery > listOfDbs.png” data-unresolved-comment-count=”0″ draggable=”false” height=”250″ src=”https://confluence.oci.oraclecorp.com/download/attachments/3741277594/listOfDbs.png?version=1&modificationDate=1707982854048&api=v2″ title=”MySQL Database Service > MySQL HeatWave Faster Point-in-time Recovery > listOfDbs.png”> - Click on the More actions button and then click on Restore to a new DB system option, as shown below.
MySQL HeatWave Faster Point-in-time Recovery > sourceDb.png” data-unresolved-comment-count=”0″ draggable=”false” height=”250″ src=”https://confluence.oci.oraclecorp.com/download/attachments/3741277594/sourceDb.png?version=1&modificationDate=1707982954467&api=v2″ title=”MySQL Database Service > MySQL HeatWave Faster Point-in-time Recovery > sourceDb.png”>
- With the Configure the source option, choose Restore from DB system at a point-in-time.
By default, restore will be performed to the latest recovery point, as shown below.
Alternatively, you may choose your preferred recovery point.
MySQL HeatWave Faster Point-in-time Recovery > restoredDb.png” data-unresolved-comment-count=”0″ draggable=”false” height=”250″ src=”https://confluence.oci.oraclecorp.com/download/attachments/3741277594/restoredDb.png?version=1&modificationDate=1707983131504&api=v2″ title=”MySQL Database Service > MySQL HeatWave Faster Point-in-time Recovery > restoredDb.png”> - Verify that the restored DB system is ACTIVE on the DB system details page, as shown below.
MySQL HeatWave Faster Point-in-time Recovery > RestoredDbActive.png” data-unresolved-comment-count=”0″ draggable=”false” height=”250″ src=”https://confluence.oci.oraclecorp.com/download/attachments/3741277594/RestoredDbActive.png?version=2&modificationDate=1709296192018&api=v2″ title=”MySQL Database Service > MySQL HeatWave Faster Point-in-time Recovery > RestoredDbActive.png”>
MySQL HeatWave Faster Point-in-time Recovery > compartments.png” data-unresolved-comment-count=”0″ draggable=”false” height=”250″ src=”https://confluence.oci.oraclecorp.com/download/thumbnails/3741277594/compartments.png?version=1&modificationDate=1709295962746&api=v2″ title=”MySQL Database Service > MySQL HeatWave Faster Point-in-time Recovery > compartments.png”>
Whenever a point-in-time recovery is requested, the PITR service finds the closest automatic
backup and restores the backup to a new DB system. It then replays all the transactions till the
recovery point using the binary log files, which are backed up.
Faster Recovery!
The point-in time restore is much quicker now; with multiple appliers compared to a sequential applier.
The performance improvement in the point-in-time recovery is almost twice with the new
multiple applier feature. It is important to note that the improved performance achieved depends
on the presence of transactions that the parallel workers can effectively parallelize.
Please give it a go and provide us with your suggestions. We look forward to your valuable
feedback as we continue to make our service the best in the industry. For more information,
see Point-In-Time Recovery.