Point-in-time recovery
Point-in-time recovery (PITR) restores a database to a specific point in time. You can use it to recover from data loss caused by a deployment error, external attack, or administrative mistake.
The recoverable period is determined by the automatic backup time and the binary log retention period. You can select a point within that period and restore the data to a new instance group.
Point-in-time recovery operates at the instance group level. It does not roll back the existing instance group. Instead, it creates a new instance group from the data at the selected point in time.
Prerequisites before point-in-time recovery
Before starting point-in-time recovery, check the instance group status, automatic backup settings, and binary log retention settings.
1. Instance group status
Point-in-time recovery is available only when the instance group status is Available, Primary-Available, Error, Storage-Full(Warning), or Storage-Full(Error).
You can proceed when no operation is in progress and the service is running normally, or when an instance is temporarily unavailable due to an error and recovery is required.
2. Automated backups
An automatic backup file must exist for the source instance group. Therefore, Automatic backup must be enabled. The recoverable point is determined by the available automatic backups and the binary log retention period, so configure an appropriate backup retention period. For details, see Configure automatic backup.
3. Binary logs
Retention settings
Binary logs for the source instance group must be retained. Binary logs are retained for the period specified by the binlog_expire_logs_seconds parameter. The default is 691,200 seconds, or approximately eight days. The system automatically deletes logs after this period, so this setting affects the recoverable period. If necessary, adjust this parameter.
Transaction size and log configuration
Binary logs are written per transaction. Large transactions can increase recovery time, while smaller transactions generally recover faster. You can reduce recovery time by enabling autocommit and using smaller transactions. Reducing the log size can also improve recovery speed. Review the binary log parameters in the parameter group for detailed settings.
Incorrect parameter values can affect the service. Adjust them according to your operational requirements. For details, see Modify parameter groups.
Check latest recoverable time
Point-in-time recovery uses automatic backups and binary logs to restore data to a specific point. The latest recoverable time is determined by when the automatic backup was created and the last entry in the binary logs.
When the Primary instance is in a recoverable state (Available, Storage-Full(Warning), or Storage-Full(Error)), you can restore to the current time or the time of the last binary log entry. Check the latest recoverable time under Instance Group > Point-in-time recovery in Recovery point.
Steps for PITR
Use point-in-time recovery in the MySQL service to create a new instance group from data at a specific point in time.
- Go to the KakaoCloud Console > Data Store > MySQL.
- In the Instance Group menu, select the source MySQL instance group.
- Select MySQL actions or [⋮] > Point-in-time recovery.
Point-in-time recovery follows the same four steps as Create instance group: Basic information, Instance configuration, Advanced settings, and Review. Most fields and policies are the same. The following sections describe only the differences.
PITR step 1: Basic information
Review the source instance group, and configure the recovery point, availability, multi-AZ option, and name.
| Item | Category | Description |
|---|---|---|
| Recovery source information | Source instance group information - Instance group name, engine version, primary storage size, and table name case sensitivity | |
| Recovery point | Latest point | Restore to the current time in UTC+09:00 |
| Specify point | Select a point within the automatic backup and binary log retention period - View the recoverable period from the earliest to the latest point - Enter the date and hour, minute, and second | |
| Instance availability | High availability (Primary and Standby instances) / Single (Primary instance) | Point-in-time recovery currently supports only Single |
| Multi-AZ | Enabled / Disabled | Point-in-time recovery currently supports only Disabled |
| Instance group basic information | Name / Description | Name and description of the new instance group |
When you select Single, the multi-AZ option is disabled.
PITR step 2 and later
For Instance configuration (engine, instance type, storage capacity, and network), Advanced settings (automatic backup, additional settings, and audit logging), and Review, follow the same procedure as Create instance group.