Point-in-time recovery

Point-in-time Recovery

Point-in-time Recovery (PITR) is a feature that allows you to restore a database to a specific point in time. It operates at the instance group level. This is especially useful when a deployment error occurs, data is lost due to external attacks, or an administrative mistake leads to data deletion.

The restoration period depends on when automated backups were taken and how long binary logs are retained. You can restore to a specific point in time within that window by creating a new instance group.

Prerequisites Before Point-in-time Recovery

1. Instance Group Status

Point-in-time recovery is available only when the instance group status is Primary-Available, Available, or Error.
You can proceed with PITR when there are no ongoing tasks and the service is stable, or when some instances are unavailable due to errors and recovery is needed.

2. Automated Backups

To perform PITR, automated backup files must exist for the target instance group. Ensure that automated backup is enabled. The recoverable time window is based on the timing of these backups and the retention of binary logs, so it's important to set an appropriate backup retention period. For details, refer to Automated Backup Settings.

3. Binary Logs

Retention Settings

Binary logs must be retained for PITR to work. Retention is controlled by the binlog_expire_logs_seconds parameter. The default value is 691,200 seconds (about 8 days). Logs are automatically deleted after this period, so this setting directly affects the recoverable window. Adjust the value as needed.

Transaction Size and Log Configuration

Binary logs are written per transaction. Larger transactions slow down recovery; smaller ones allow faster restoration. Enabling autocommit and reducing log size can improve speed. Check parameters like binlog_row_image and binlog-format.

⚠️ Misconfiguring these parameters may impact service. Refer to Modify Parameter Group for safe adjustments.

Check Latest Recoverable Time

PITR uses both automated backups and binary logs to restore up to a specific point. The latest recoverable time depends on when backups were taken and the last entry in the binary logs.

If the Primary instance is Available or Primary Available, you can restore up to the current time or the most recent binary log entry. Go to Instance Group > PITR > Recovery Time to check the latest available point.

Steps for PITR

Use the PITR function in the MySQL service to create a new instance group with data restored to a chosen point in time.

1. Go to the KakaoCloud Console > Data Store > MySQL.

2. Select the MySQL instance group for PITR from the Instance Group menu.

3. Click MySQL actions or [⋮] > Point-in-time Recovery.

4. Fill in the information on the PITR page and click Confirm.

   Item	Category	Description
   Recovery Info		Includes selected group name, engine version, default storage size, extra options, etc.
   Recovery Time		Choose recovery time (UTC+9) - Latest time: restore to the latest point - Specify time: choose a time within the recoverable period
   Specify Time		Available when "Specify time" is selected - Choose time from the calendar
   Basic Settings	Instance Group Name	Unique within the same project e.g., mysql-instance-01
   	Description (Optional)	Up to 100 characters
   Instance Availability	High Availability (Primary + Standby) (Coming Soon)	Create multiple instances across single or multiple AZs
   	Single (Primary Only)	Create a single Primary instance in a single AZ
   MySQL Settings	Engine Version	Automatically selected Supported: 8.0.34, 8.0.41
   	MySQL Admin Username	1–32 lowercase letters or underscores e.g., admin (cannot use reserved name root)
   	MySQL Password	8–16 characters, no spaces or /, ', ", @ ⚠️ Password must be changed after DB launches
   Parameter Group		Choose based on engine version
   Instance Type		Select DB compute type See: Instance Types
   Default Storage	SSD	Type and size (100 GB–5,120 GB)
   Log Storage	SSD	For logs only (100 GB–16,384 GB)
   Network	Multi-AZ Option	Only for high availability. Configure subnet and instance count
   	VPC	Select VPC for database deployment
   	Subnet & Instance Count	Assign subnet and number of instances Max 6 for HA
   	Primary	Select subnet and instance count for Primary
   	Standby	Select subnet and instance count for Standby (if using Multi-AZ)
   	Add Subnet	Add subnets per availability zone
   	Security Group	Select or create a security group Multiple can be selected
   Automated Backup	Enable Option	Perform daily backups if enabled
   	Retention Period	1–35 days
   	Backup Start Time	Backup starts within 15 minutes of selected time
   Additional Settings	Case Sensitivity	Separate setting from lower_case_table_names in parameter group ⚠️ Cannot change after creation