Open menu
-->

Enable Amazon Aurora Backtrack

Cloud Conformity allows you to automate the auditing process of this resolution page. Register for a 14 day evaluation and check your compliance level for free!

Start a Free Trial Product features
Last updated: 23 October 2018
Reliability

Risk level: Low (generally tolerable level of risk)

Ensure that Backtrack feature is enabled for your Amazon Aurora with MySQL compatibility database clusters in order to backtrack your clusters to a specific time, without using backups. Backtrack is an AWS Relational Database Service (RDS) feature that allows you to specify the amount of time that an Aurora MySQL database cluster needs to retain change records so that you can have a fast way to recover from user errors, such as dropping the wrong table or deleting the wrong row by moving your MySQL database to a prior point in time without the need to restore from a recent backup. The feature is currently supported only by Aurora MySQL 5.6 database engine.

Once the Backtrack feature is enabled, Amazon RDS can quickly "rewind" your Aurora MySQL database cluster to a point in time that you specify. In contrast to the backup and restore method, with Backtrack you can easily undo a destructive action, such as a DELETE query without a WHERE clause, with minimal downtime, you can rewind your Aurora cluster in just few minutes, and you can repeatedly backtrack a database cluster back and forth in time to help determine when a particular data change occurred.

Audit

To determine if your Amazon Aurora MySQL-compatible database clusters are using the Backtrack feature, perform the following actions:

Using AWS Console

01 Sign in to AWS Management Console.

02 Navigate to RDS dashboard at https://console.aws.amazon.com/rds/.

03 In the left navigation panel, under Amazon RDS, click Clusters.

04 Select the RDS database cluster that you want to examine and click on the resource name (link) available within DB cluster identifier column. The selected cluster must have the database engine, available in the Engine column, set to Aurora MySQL.

05 In the Details panel section, within Backtrack category, check the Backtrack window configuration attribute value. If the attribute value is set to Disabled, the Backtrack feature is not enabled for the selected Amazon Aurora MySQL database cluster.

06 Repeat step no. 4 and 5 to determine if other Aurora clusters, provisioned in the current region, have backtracking enabled.

07 Change the AWS region from the navigation bar and repeat the entire process for other regions.

Using AWS CLI

01 Run describe-db-clusters command (OSX/Linux/UNIX) using custom query filters to list the names of all Amazon Aurora MySQL-compatible database clusters available in the selected AWS region:

aws rds describe-db-clusters
	--region us-east-1
	--output table
	--query 'DBClusters[?Engine==`aurora`].DBClusterIdentifier | []'  

02 The command output should return a table with the requested RDS names:

------------------------
|  DescribeDBClusters  |
+----------------------+
|  cc-aurora-cluster   |
|  cc-mysql56-cluster  |
+----------------------+

03 Execute again describe-db-clusters command (OSX/Linux/UNIX) using the name of the database cluster that you want to examine as identifier and custom query filters to get the backtrack window, in seconds, configured for the selected MySQL cluster:

aws rds describe-db-clusters
	--region us-east-1
	--db-cluster-identifier cc-aurora-cluster
	--query 'DBClusters[*].BacktrackWindow'  

04 The command output should return an array which contains the target backtrack window time frame (in seconds), or an empty array if backtracking is disabled:

[]

If describe-db-clusters command output returns an empty array, as shown in the output example above, the Backtrack feature is not enabled for the selected Amazon Aurora MySQL database cluster.

05 Repeat step no. 3 and 4 to determine the Backtrack feature status for other Aurora database clusters available within the selected region.

06 Change the AWS region by updating the --region command parameter value and repeat steps no. 1 – 5 to perform the audit process for other regions.

Remediation / Resolution

To enable Backtrack feature for an existing Amazon Aurora MySQL database cluster, you have to re-create the cluster and configure the feature during setup. To implement backtracking for your Aurora database cluster, perform the following:

Using AWS Console

01 Sign in to AWS Management Console.

02 Navigate to RDS dashboard at https://console.aws.amazon.com/rds/.

03 In the left navigation panel, under Amazon RDS, click Instances.

04 Select the primary instance for the Aurora database cluster that you want to create a clone of (see Audit section part I to identify the right cluster). The instance must have the database engine, available in the Engine column, set to Aurora MySQL.

05 Click the Instance Actions button from the dashboard top menu and select Create Clone option.

06 On the Create Clone page, perform the following actions:

  1. In the Settings section, within DB instance identifier box, provide a name for the primary instance of the clone database cluster.
  2. In the Settings section, select Enable Backtrack to activate the feature, then specify the amount of time (hours, up to 72) that you want to be able to backtrack, within the Target Backtrack window box. This setting must be configured in order to remember how far back in time you could go with backtracking.
  3. If required, configure any other settings for the clone database cluster. Once finished, click Create Clone to launch the new Aurora database cluster.

07 Once the cluster is created, replace the required endpoints within your application code to switch the source cluster with the new database cluster.

08 Now you can remove the source Aurora database cluster from your AWS account in order to avoid further charges. To delete the necessary cluster, perform the following:

  1. Select the primary instance for the database cluster that you want to terminate.
  2. Click the Instance Actions button from the dashboard top menu and select Delete.
  3. Within Delete <database-instance-name> instance dialog box, choose whether or not to create a final snapshot for the selected database, then click the Delete button to confirm the action. This should also remove the source database cluster.

09 Repeat steps no. 4 – 8 to enable the Backtrack feature for other Amazon Aurora MySQL database clusters available in the current region.

10 Change the AWS region from the navigation bar and repeat the process for other regions.

Using AWS CLI

01 Run restore-db-cluster-to-point-in-time command (OSX/Linux/UNIX) to re-create the database cluster (see Audit section part II to identify the right resource) and enable backtracking for the selected cluster. The following example creates a clone named "cc-new-aurora-cluster" from an Amazon Aurora MySQL database cluster called "cc-aurora-cluster", that has the Backtrack window set to 24 hours (86400 seconds). When configured, the target Backtrack window value must be set to a number from 0 to 259,200 (72 hours):

aws rds restore-db-cluster-to-point-in-time
	--region us-east-1
	--source-db-cluster-identifier cc-aurora-cluster
	--db-cluster-identifier cc-new-aurora-cluster
	--restore-type copy-on-write
	--use-latest-restorable-time
	--backtrack-window 86400

02 The command output should return the configuration metadata for the new clone cluster:

{
    "DBCluster": [
        {
            "HostedZoneId": "AAAABBBBCCCDDD",
            "EngineMode": "provisioned",
            "Status": "available",
            "MultiAZ": false,
            "LatestRestorableTime": "2018-10-17T17:20:44.617Z",
            "PreferredBackupWindow": "06:08-06:38",
            "DBSubnetGroup": "default",
            "AllocatedStorage": 100,
            "BackupRetentionPeriod": 7,
            "PreferredMaintenanceWindow": "wed:09:49-wed:10:19",
            "Engine": "aurora",
            "AssociatedRoles": [],
            "EarliestRestorableTime": "2018-10-17T17:04:08.907Z",
 
            ...
 
            "IAMDatabaseAuthenticationEnabled": false,
            "ClusterCreateTime": "2018-10-17T17:03:32.874Z",
            "EngineVersion": "5.6.10a",
            "DeletionProtection": false,
            "DBClusterIdentifier": "cc-aurora-cluster",
            "DbClusterResourceId": "cluster-AAABBBCCCDDDAAABBBCCCDDDAA",
            "StorageEncrypted": false,
            "DatabaseName": "cc_mysql_database",
            "DBClusterParameterGroup": "default.aurora5.6",
            "AvailabilityZones": [
                "us-west-2c",
                "us-west-2b",
                "us-west-2a"
            ],
            "Port": 3306
        }
    ]
}

03 Once the cluster is created, replace the required endpoints within your application code to switch the source cluster with the new database cluster.

04 Now it’s safe to remove the source Aurora database cluster in order to avoid further AWS charges:

  1. First, you have to execute delete-db-instance command (OSX/Linux/UNIX) to remove the primary instance for the cluster that you want to terminate:
    aws rds delete-db-instance
    	--region us-east-1
    	--db-instance-identifier cc-aurora-mysql-db
    	--skip-final-snapshot   
    
  2. The command output should return the deleted database instance metadata:
    {
        "DBInstance": {
            "PubliclyAccessible": false,
            "LicenseModel": "general-public-license",
            "PendingModifiedValues": {},
            "Engine": "aurora",
            "MultiAZ": false,
            "DBSecurityGroups": [],
            "PerformanceInsightsEnabled": false,
            "AutoMinorVersionUpgrade": true,
            "PreferredBackupWindow": "06:08-06:38",
            "DBInstanceStatus": "deleting",
            "IAMDatabaseAuthenticationEnabled": true,
     
            ...
     
            "EngineVersion": "5.6.10a",
            "DeletionProtection": false,
            "AvailabilityZone": "us-west-2b",
            "DomainMemberships": [],
            "DBClusterIdentifier": "cc-aurora-cluster",
            "StorageType": "aurora",
            "DbiResourceId": "db-AAABBBCCCDDDAAABBBCCCDDDAA",
            "CACertificateIdentifier": "rds-ca-2015",
            "StorageEncrypted": false,
            "DBInstanceClass": "db.r4.xlarge",
            "DBInstanceIdentifier": "cc-aurora-mysql-db"
        }
    }
    
  3. Then execute delete-db-cluster command (OSX/Linux/UNIX) to delete the source Amazon Aurora database cluster:
    aws rds delete-db-cluster
    	--region us-east-1
    	--db-cluster-identifier cc-aurora-cluster
    	--skip-final-snapshot     
    
  4. The command output should return the metadata for the terminated database cluster:
    {
        "DBCluster": [
            {
                "HostedZoneId": "AAAABBBBCCCDDD",
                "EngineMode": "provisioned",
                "MultiAZ": false,
                "AllocatedStorage": 100,
                "BackupRetentionPeriod": 7,
                "Engine": "aurora",
     
                ...
     
                "IAMDatabaseAuthenticationEnabled": false,
                "ClusterCreateTime": "2018-10-17T17:03:32.874Z",
                "EngineVersion": "5.6.10a",
                "DeletionProtection": false,
                "DBClusterIdentifier": "cc-aurora-cluster",
                "StorageEncrypted": false
            }
        ]
    }
    

05 Repeat steps no. 1 – 4 to enable the Backtrack feature for other Amazon Aurora MySQL database clusters provisioned within the current region.

06 Change the AWS region by updating the --region command parameter value and repeat the entire process for other regions.

References

Publication date Oct 18, 2018