How do I troubleshoot and resolve Aurora MySQL-Compatible snapshot restore issues?

Short description

There are different options to restore your entire Aurora MySQL-Compatible DB clusters from a snapshot or backup. You can use the Aurora and Amazon Relational Database Service (Amazon RDS) console to restore the entire DB cluster from a snapshot or restore from a point-in-time recovery (PITR). You can also use AWS Backup to manage automated backups and restore from a PITR. Or you can create an Aurora clone of your database.

Resolution

Note: If you receive errors when you run AWS Command Line Interface (AWS CLI) commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

Use the Aurora and RDS console to restore from a snapshot

When you take a snapshot, Aurora backs up the entire DB cluster. For Aurora, you can't choose to take a snapshot of the individual DB instances in the cluster.

For simple restore operations, use the Aurora and RDS console. When you use the Aurora and RDS console to restore from a DB cluster snapshot, Aurora creates a new cluster and provisions a writer instance. You can't restore a snapshot to an existing cluster.

You can also restore the cluster from a snapshot with the restore-db-cluster-from-snapshot command, but this method only creates the cluster. Then, use the create-db-instance command to create the writer instance and reader instance for the cluster.

After the restore is complete, the new cluster has different endpoints than the original cluster. To minimize downtime when you replace a production cluster, complete the following steps:

1. When you restore to the cluster, use a new name such as original-cluster-name-new.
2. Rename the existing cluster to original-cluster-name-old.
   Note: A downtime of 1-3 minutes occurs when you rename the cluster.
3. Rename the new cluster to the original name.

Also, you must manually add and configure multi-Availability Zone reader instances that you need in the new cluster after you restore to the new cluster.

To add reader instances in the new cluster, complete the following steps:

1. Open the Aurora and RDS console.
2. In the navigation pane, select Databases, and then select the new cluster.
3. Wait for the status of the new primary instance to be Available, and then select Actions.
4. To add a reader to the new cluster in the same AWS Region, select Add reader.
5. To configure a cross-Region read replica, select Create cross-region read replica.

Prevent slow restores

If the volume size is large, the instance class has a lower I/O size, or there's insufficient network bandwidth, then the cluster takes longer to restore. Also, the maximum storage throughput of the target databases can affect the restore speed.

To minimize the time that it takes to restore an Aurora MySQL-Compatible cluster, take the following actions:

• Check the RollbackSegmentHistoryListLength (HLL) value on the original cluster. High HLL values show a large number of undo records, which can slow down restores. To resolve this issue, monitor the HLL metric in CloudWatch and keep the value low.
• To improve restore performance, choose a larger instance class that has higher storage throughput and network bandwidth.
• Create snapshots during low workload periods. If you take a snapshot when there's less activity in the database, then you can reduce the undo records that Aurora processes during the restore.
• Commit transactions in smaller batches. If you break up long-running transactions into smaller units, then you can reduce the size of the undo log.
• Avoid long-running transactions before you take a snapshot. Long running-transactions generate more undo records that Amazon RDS must process during the restore.

Use AWS Backup for PITR

When you use AWS Backup to create snapshots, the snapshots have names that include awsbackup:job-AWS-Backup-job-number. These snapshots are considered manual DB cluster snapshots, but don't count toward the DB cluster snapshot quota for Aurora.

You can also use AWS Backup to manage automated backups for PITR. The automated backups that AWS Backup manages have names that include continuous:cluster-AWS-Backup-job-number. To turn on PITR, you must turn on continuous backups in the backup rule.

Use the AWS Backup console to restore the cluster associated with AWS Backup plans. After you use the AWS Backup console to restore the cluster, you must add the primary writer DB instance. To do this, run the create-db-instance AWS CLI command. Replace --db-cluster-identifier with the name of the restored DB cluster.

Backup failures

Backup failures might occur when the policy you use in AWS Backup has insufficient AWS Identity and Access Management (IAM) permissions. To resolve this issue, verify that the IAM role or user has the required permissions to use AWS Backup to perform backup operations. You must attach an IAM policy that grants the backup:StartBackupJob permission to the IAM role or user.

Backup failures can also occur when the IAM role or user can't access the AWS Key Management Service (AWS KMS) key for the backup operation. If your Aurora cluster uses encryption at rest, then make sure that the IAM role or user can access and use the AWS KMS key that encrypts the cluster storage. You must grant the kms:GenerateDataKey permission on the key resource policy.

Restore failures

When you try to restore an Aurora MySQL-Compatible cluster from a backup snapshot, you might receive an "Incompatible-restore" error for the following reasons:

• The source database contains temporary tables or MyISAM tables that Aurora doesn't support when you restore to a new cluster. To resolve this issue, remove these tables before you take the snapshot.
• Inconsistent or cyclical foreign key constraints in the source database can prevent a successful restore. To resolve this issue, fix the constraints before you take the snapshot.
• The engine version for the target cluster is incompatible. To resolve this issue, verify that the target Aurora MySQL engine version is compatible with the version that you use to create the snapshot. If you want to upgrade the engine version for your new cluster, then restore to a compatible version first. Then, upgrade the restored cluster.
• Incorrect networking configurations. Double-check that the virtual private cloud (VPC), subnet group, and security group settings that you specify during the restore process match the intended target environment.
• The IAM role or user doesn't have AWS KMS key access. If the snapshot uses encryption, then verify that the IAM role or user has permissions to access and use the specified AWS KMS key for decryption.

Create an Aurora clone

As an alternative method, you can create an Aurora clone of your databases. With database cloning, you can create a copy of databases quickly and with minimal additional space. Data is copied only as changes occur.

Related information

Overview of backing up and restoring an Aurora DB cluster

Restoring from a DB cluster snapshot

Restoring a DB cluster to a specified time

Understanding Aurora backup storage usage

Why is my Aurora DB cluster clone, snapshot restore, or point in time restore taking so long?