When managing databases, Point-in-Time Recovery (PITR) is a crucial process that ensures data integrity by allowing restoration to specific moments in time. SkySQL, a cloud-native database service, streamlines this process through its Backup & Restore Service . In this guide, we’ll break down the PITR workflow, explaining each stage and how SkySQL manages snapshots, binary logs, and temporary resources efficiently.

PITR Step-by-Step Logical Flow

To perform a Point-in-Time Recovery (PITR), SkySQL follows the structured process listed below

Logical Steps:

1. Determine the PITR backup chain

The system identifies:

• The last snapshot backup taken before the selected recovery time, which serves as the restore baseline.

• The first snapshot backup taken after the selected recovery time, used for extracting the binary logs needed to trail and reply the server events up to the selected recovery time.

2. Restore baseline state

The system restores the database using the last snapshot backup, taken before the requested PITR time, creating the initial database state onto which changes will be applied.

3. Extract and filter binary logs

Using the snapshot taken after the PITR time, the system extracts and filters binary logs to isolate transactions that occurred after the baseline snapshot and before the selected PITR time. These filtered logs are then applied on the restored instance.

4. Clean up temporary resources

After recovery is complete, all temporary storage and log processing resources are automatically removed to maintain a clean environment.

PITR Workflow Implementation Overview

The PITR process follows a sequence illustrated in the below flowchart

Let’s examine each stage of the workflow:

0. SkySQL Backup & Restore Controller Manager

At the core of the Backup & Restore Service PITR workflow is the SkySQL Backup & Restore Controller Manager, which orchestrates the entire recovery process. The SkySQL Backup & Restore Controller Manager ensures seamless coordination between:

• backup sources identification

• backup sources preparation

• log processing

• final restoration.

• temp resource cleanup

1. Preparing the Backup Chain for Recovery

The recovery process begins by preparing a backup chain, which involves identifying and organising the required snapshots and binary logs needed for a successful PITR operation. This ensures the correct resources are staged and ready before the system proceeds to the next step.

2. Restoring from the Last Snapshot Before the Selected Recovery Time

The process restores the last snapshot taken before the PITR requested time. This snapshot serves as a baseline, on top of which all relevant binary log transactions, identified in step 4.3, are replayed.

The process involves the following steps**:**

• Retrieve the /var/lib/mysql state ****from the last available ****snapshot prior the selected PITR target .

• Initialise a SkySQL database instance using the retrieved /var/lib/mysql state

3. Restoring the First Snapshot After the Selected Recovery Time

The system restores the first snapshot taken after the requested recovery time .

3.1 -3.5 Creating Temporary Resources for Binary Log Processing

Once the backup chain in step 2 is validated, the SkySQL Backup & Restore Controller Manager creates temporary resources to facilitate binary log extraction and filtering, so all transactions relevant to the requested PITR point are identified and replayed

The process involves the following steps**:**

• Scratch Persistent Volume: A temporary storage space for processing logs.

• Binary Log Pod: A containerised environment for processing binary logs.

• Restore Snapshot & Extract Binary Logs: Extracts logs required for replay up to the Selected Recovery Time

• Filter & Copy Binary Logs: Ensures only relevant transactions are applied.

• Delete Temporary Resources: Cleans up storage after processing logs.

4. Final Step: Applying Binary Logs

After initializing the restore target database instance (Step 2) and extracting and filtering the relevant binary logs (Step 3), the system applies the filtered logs to the restored database instance.

Usage Examples

SkySQL Portal Example:

1. Navigate to Backups→Restores

2. Click the “Point-in-time Restore” Button

3. Fill in the “Point-in-time Restore” form with information about your database restore target and backup source services and the selected restoration point in time.

API Example:

1curl --location 'https://api.skysql.com/skybackup/v1/restores' \
2  --header 'Content-Type: application/json' \
3  --header 'Accept: application/json' \
4  --header "X-API-Key: ${API_KEY}" \
5  --data '{
6    "service_id": "<SERVICE_ID>",
7    "id": "<BACKUP_SOURCE_SERVICE_ID>",
8    "point_in_time": "<RESTORE_POINT_IN_TIME, UTC, FORMAT: YYYY-MM-DD HH:MM:SS>"
9  }'

Benefits of SkySQL’s PITR Process

• Automated Workflow: Eliminates manual intervention, reducing recovery time.

• Data Integrity: Ensures that only valid transactions are applied.

• Efficient Resource Management: Uses temporary resources effectively to prevent unnecessary overhead.

• Minimal Downtime: Rapid recovery ensures business continuity.

Note: With SkySQL Backup & Restore, customers have full control over defining a backup schedule that best suits their needs. To ensure complete coverage, it is important to configure the snapshot backup schedule so that the interval between consecutive snapshots is shorter than the expire_logs_days server setting. By default, expire_logs_days in SkySQL is set to 4 days.

Conclusion

SkySQL’s structured PITR workflow offers a dependable and efficient approach to database recovery. By utilizing snapshots, binary log extraction, and temporary resources, it minimizes downtime while preserving data integrity. A thorough understanding of this workflow empowers database administrators to refine their recovery strategies and enhance data resilience in production environments.