Getting Started / Hands-on / ...
Fast database cloning & backtrack to accelerate application development
with Amazon Aurora
Amazon Aurora is a MySQL and PostgreSQL-compatible relational database that combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open source databases. In this tutorial, you will learn how to clone an Aurora DB cluster for development and testing and how to enable and use backtrack to “rewind” the DB cluster to the time you specify.
Cloning an Aurora cluster is extremely useful if you want to assess the impact of changes to your database, or if you need to perform workload-intensive operations—such as exporting data or running analytical queries, or simply if you want to use a copy of your production database in a development or testing environment. You can make multiple clones of your Aurora DB cluster. You can even create additional clones from other clones, with the constraint that the clone databases must be created in the same region as the source databases.
Backtrack lets you rewind the Aurora DB cluster to the time you specify. With backtrack enabled, Aurora keeps a record of changes to your database and allows you to switch to a previous consistent state. With this feature you can easily undo mistakes. For example, if by accident you perform a destructive action, such as a DELETE without a WHERE clause, you can quickly backtrack to a state before the accident. Unlike restoring from a snapshot or automated backup—a slower operation, backtrack lets you move back and forth in time in a matter of minutes.
In this tutorial, you will create an Aurora DB cluster with backtrack enabled. You will add some data to it and move back and forth in time by using the backtrack feature. You will clone the database and perform queries on the copy.
This tutorial is not within the free tier and will cost you less than $1 provided you follow the steps in the tutorial and terminate your resources at the close of the tutorial.
Prerequisites
You will need the ability to connect to the database and issue commands to it. Since we recommend you avoid assigning public IP addresses to DB clusters, you will likely need an EC2 instance in the same VPC as the DB cluster. If you don't already have one running, follow these instructions to provision one.
Then, you will be able to install a MySQL client by running sudo yum install mysql.
| About this Tutorial | |
|---|---|
| Time | 10-20 minutes |
| Cost | Less than $1 |
| Use Case | Databases |
| Products | Amazon Aurora, Amazon RDS |
| Audience | Database administrators, Developers |
| Level | Intermediate |
| Last Updated | July 17, 2019 |
Step 1: Create an Aurora DB cluster
1.1 - Open a browser and navigate to Amazon RDS console. If you already have an AWS account, login to the console. Otherwise, create a new AWS account to get started.
Already have an account? Log in to your account
1.2 - On the top right corner, select the region where you want to launch the Aurora DB cluster.
1.3 - Click on "Create database" in the Amazon Aurora window.
Before continuing, switch to the new database creation flow:
Engine options
1.4 - On Database engine, select “Amazon Aurora”.
1.5 - On Edition, select “Amazon Aurora with MySQL compatibility”.
1.6 - On Version, select the latest Aurora version compatible with MySQL 5.6. That's because Backtrack is available only for that version.
1.7 - On Database Location, select “Regional”.
Database features
1.8 - Select “One writer and multiple readers”.
Templates
1.9 - Select “Production”.
Settings
1.10 - Choose an identifier for your Aurora DB cluster, e.g. "database-1".
DB instance size
1.11 - For the DB instance size, select a large instance (ending with .large).
Availability & durability
1.12 - Choose “Create an Aurora Replica/Reader”.
Connectivity
1.13 - Select the VPC where you want to create the database.
Note that once created, a database can't be migrated to a different VPC.
1.14 - Click on “Additional connectivity configuration”.
1.15 - Select the default value for Subnet group.
1.16 - On Publicly accessible, select “No”.
This means you will have to connect to the database from an EC2 instance within the same VPC.
1.17 - On VPC security group, select “Create new”. If you happen to have a security group that allows incoming TCP connections on port 3306, you can choose it instead.
1.18 - In New VPC security group name, type “aurora-tutorial”.
Additional configuration
1.19 - Enable Backtrack.
1.20 - On “Target Backtrack window”, enter 24. The Backtrack window determines how far back in time you could go, and Aurora will try to retain enough log information to support that window of time.
1.21 - On “deletion protection”, uncheck “Enable deletion protection”.
The best practice is to enable the deletion protection, but if you want to delete the database at the end of the tutorial you can leave the option unchecked.
Review and create
After a quick review of all the fields in the form, you can proceed.
1.22 — Click on “Create database”.
While the instances are being created, you will see a banner explaining how to obtain your credentials. This is a good opportunity to save the credentials somewhere, as this is the only time you will be able to view this password.
1.23 — Click on “View credential details”.
1.24 - Save the username, password, and endpoint.
Step 2: Populate your database
Open a terminal window, log into your application instance, and use the mysql command line client to connect to your Aurora DB cluster. Then you will be able to create a database, create a table, and write some records.
2.1 - Connect to your primary database:
$ mysql -h endpoint -P 3306 -u admin -p
Each time you read endpoint in a command, you must enter the endpoint hostname you saved in step 1.24.
Similarly, when prompted for a password, enter the password you saved in the step 1.24.
2.2 - Create a database and write some data.
mysql> CREATE database tutorial;
Query OK, 1 row affected (0.01 sec)
At this point you can use the tutorial database, create tables, and add some records.
Step 3: Backtrack your database
3.1 - Click on your Aurora DB cluster.
3.2 - Click on “Actions” and select “Backtrack”.
Backtrack DB cluster
3.3 - Select a date and time. Aurora will rewind the DB cluster to the specified point in time without creating a new DB cluster. You can select a time before the point when you created a database or a table.
3.4 - Click on “Backtrack DB cluster”.
You will see a screen with a blue banner explaining that a backtrack operation is running.
After a while, you will see a green banner indicating that the backtrack operation finished.
You can now connect to your database like you did in step 2 and verify that your database returned to a previous state. You can repeat the backtrack operation and move forward in time.
Step 4: Clone your database
4.1 - Select your database.
4.2 - Click on “Actions” and select “Create clone”.
4.3 - Choose an identifier for, e.g. “database-1-clone”.
4.4 - Click on “Create Clone”.
You will see the list of databases with your cloned Aurora DB cluster.
Anything you do with this clone won't affect the original DB cluster. You can find the endpoints of the cloned DB and its replica by clicking on the name of the cloned cluster.
Then you will see the endpoints under the “Endpoints” section.
You can now connect to your cloned database like you did in step 2 and verify that your database is identical to the one you cloned: check the databases, tables, users, and records you created to see they are included in the cloned database. You can modify it and experiment with it all you want, and check the original database to verify that it is not affected.
Step 5: Delete your cluster
To finish this tutorial and to avoid any additional charges, you will learn how to delete your Aurora DB cluster when it's not needed anymore. In order to delete your Aurora DB cluster, go to the RDS Dashboard and follow these instructions.
5.1 - Select “Databases” on the left pane.
This will show you a list of all your Aurora DB clusters. You will start by deleting the cloned database.
5.2 - Select the writer instance.
5.3 - Click on “Actions” and select “Delete”.
You will be asked if you want to create a final backup. That's usually a good idea, but it's not necessary for this tutorial.
5.4 - Uncheck the box for “Create final snapshot” and check the box for “I acknowledge...”.
5.5 - Type “delete me” and click on “Delete”.
The status of your cluster will change to “Deleting”.
5.6 - Select the reader instance of the Aurora DB Cluster you created for this tutorial.
5.7 - Click on “Actions” and select “Delete”.
5.8 - You will be asked to confirm the action. Type “delete me” and click on “Delete”.
5.9 - Select the writer instance of the Aurora DB cluster you created for this tutorial.
5.10 - Click on “Actions” and select “Delete”.
You will be asked if you want to create a final backup. That's usually a good idea, but it's not necessary for this tutorial.
5.11 - Uncheck the box for “Create final snapshot” and check the box for “I acknowledge...”.
5.12 - Type “delete me” and click on “Delete”.
The status of your cluster will change to “Deleting”.
Congratulations
You have created an Aurora DB cluster with backtrack enabled. You have learned how to configure the time range that allows you to move back and forth in time with your database. Finally, you learned how to clone an Aurora DB cluster.