Building continuous read & write availability applications

with Amazon Aurora Multi-Master

Amazon Aurora is a MySQL and PostgreSQL-compatible relational database that combines the performance and availability of commercial databases with the simplicity and cost-effectiveness of open source databases.

Amazon Aurora Multi-Master allows you to create multiple read-write instances of your Aurora database across multiple Availability Zones, which enables uptime-sensitive applications to achieve continuous write availability in the event of instance failure. The feature is now available on Aurora MySQL 5.6 in US East (N. Virginia), US East (Ohio), US West (Oregon), and EU (Ireland). In this tutorial, you will learn how to create an Amazon Aurora Multi-Master database and verify that you can write and read to and from any instance.

Pre-requisites

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 minutes      
Cost Less than $1
Use Case Databases
Products Amazon Aurora, Amazon RDS
Audience Database administrators, Developers
Level Intermediate
Last Updated August 29, 2019

Step 1: Create your database

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 with MySQL compatibility”.

Database features

1.5 — Select “Multiple writers”.

Settings

1.6 — Choose an identifier for your Aurora DB cluster, e.g. “database-1”.

Credentials settings

1.7 — Select a username and password for your database.

Connectivity

1.8 — 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.9 — Click on “Additional connectivity configuration”.

1.10 — Select the default value for Subnet group. Alternatively, you can select a custom-created subnet group of your choice where you want the cluster to be launched.

1.11 — 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.12 — In New VPC security group name, type “aurora-tutorial”.

Additional configuration

1.13 — On “Deletion protection” uncheck “Enable deletion protection”.

The best practice is to enable the Deletion protection. Doing so means you will need to disable the deletion protection prior to deleting a cluster. For the purpose of this tutorial, you can disable the Deletion protection.

Review and create

After a quick review of all the fields in the form, you can proceed.

1.14 — 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.

Copy endpoints

1.15 — Once the database is created, select the first instance and copy its endpoint.

1.16 — Select the second instance and copy its endpoint.

Step 2: Connect to your database

In the previous step you copied the database endpoints. Let's call them endpoint1 and endpoint2. Log into your EC2 instance and follow the instructions below. 

2.1 — Connect to the first endpoint.

$ mysql -h endpoint1 -u admin -p

When prompted for a password, enter the password you created in step 1.7.

If the command hangs, chances are you are being blocked by the Security Group settings. Verify that your EC2 instance has access to the security group assigned to your Aurora MySQL instance. For example, let's say your EC2 instance was assigned to the default security group. You can now modify the security group of your Aurora MySQL instance, edit the Inbound rules and add a MYSQL/Aurora rule allowing connections on port 3306 from any instance in the default security group:

In Source, you can start typing the name of the security group and you'll be able to click on the Security Group ID. If you need to learn more about Security Groups, you can check the documentation or the Security Group Rules Reference

2.2 — Once you are connected, create a database.

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.

2.3 — Change to the tutorial database.

mysql> USE tutorial;
Database changed

2.4 — Create the planet table.

mysql> CREATE TABLE planet (
    -> id INT UNSIGNED AUTO_INCREMENT,
    -> name VARCHAR(30),
    -> PRIMARY KEY(id));
Query OK, 0 rows affected (0.057 sec)

2.5 — Insert one record.

mysql> INSERT INTO planet (name) VALUES ("Mercury");
Query OK, 1 row affected (0.008 sec)

Now you will close this connection to your database, and connect to the other instance.

2.6 — Disconnect from your database.

mysql> quit
Bye

2.7 — Connect to the second endpoint.

$ mysql -h endpoint2 -u admin -p

When prompted for a password, enter the password you created in step 1.7.

2.8 — Change to the tutorial database.

mysql> USE tutorial;
Database changed

2.9 — Read from your database.

mysql> SELECT * FROM planet;
+----+---------+
| id | name	|
+----+---------+
|  2 | Mercury |
+----+---------+
1 row in set (0.00 sec)

Everything looks normal so far, but the cluster was created as a Multi-Master and that means you can write to any instance. Let's try that:

2.10 — Insert two records.

mysql> INSERT INTO planet (name) VALUES ("Venus");
Query OK, 1 row affected (0.008 sec)

mysql> INSERT INTO planet (name) VALUES ("Earth");
Query OK, 1 row affected (0.008 sec)

2.11 — Read from your database.

mysql> SELECT * FROM planet;
+----+---------+
| id | name	|
+----+---------+
|  2 | Mercury |
|  3 | Venus   |
| 19 | Earth   |
+----+---------+
3 rows in set (0.00 sec)

And just to verify, you will disconnect from this instance and connect to the primary again:

2.12 — Disconnect from your database.

mysql> QUIT
Bye

2.13 — Connect to the first endpoint.

$ mysql -h endpoint1 -u admin -p

2.14 — Change to the tutorial database.

mysql> USE tutorial;
Database changed

2.15 — Read from your database.

mysql> SELECT * FROM planet;
+----+---------+
| id | name	|
+----+---------+
|  2 | Mercury |
|  3 | Venus   |
| 19 | Earth   |
+----+---------+
3 rows in set (0.00 sec)

As you can see, you can read from and write to both instances. That's extremely powerful because if any instance in your cluster goes down, the application can instantly switch over to any remaining healthy instance and start using it. In fact, you can simulate an instance crash and verify that the other instance stays up and unaffected:

2.16 — Crash the instance with the following command:

mysql> ALTER SYSTEM CRASH;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> QUIT
Bye

2.17 — Connect to the second endpoint.

$ mysql -h endpoint2 -u admin -p

2.18 — Change to the tutorial database.

mysql> USE tutorial;
Database changed

2.19 — Read from your database.

mysql> SELECT * FROM planet;
+----+---------+
| id | name	|
+----+---------+
|  2 | Mercury |
|  3 | Venus   |
| 19 | Earth   |
+----+---------+
3 rows in set (0.00 sec)

As you can see, the second instance remained unaffected even though the first one crashed.

Step 3: Clean up

To finish this tutorial, you will learn how to delete your Aurora DB cluster when it's not needed anymore.

3.1 — Go to the Amazon RDS console and select the last instance of the database you created for this tutorial.

3.2 — Click on “Actions > Delete”.

3.3 — You will be asked for confirmation. Type “delete me” and click on “Delete”.

3.4 — Now select the primary instance.

3.5 — Click on “Actions > Delete”.

3.6 — When asked for confirmation, select “No” to the question of whether to create a final snapshot. Then check “I acknowledge…”, type “delete me” and click on “Delete”.

Congratulations

You have created an Amazon Aurora Multi-Master database, performed reads and writes to and from any instance, and verified that in the event of an instance failure, the other instance stays up and unaffected.

Was this tutorial helpful?

Thank you
Please let us know what you liked.
Sorry to disappoint you
Is something out-of-date, confusing or inaccurate? Please help us improve this tutorial by providing feedback.

Learn more about Amazon Aurora features

Find out more about the features of Amazon Aurora with the Amazon Aurora User Guide.

Best practices with Amazon Aurora

Learn about general best practices and options for using or migrating data to an Amazon Aurora DB cluster.

Learn more about Amazon Aurora Multi-Master