Deploying Amazon RDS Multi-AZ and Read Replica, Simulate Failover

Lab Details

1. This lab walks you through the steps to launch an Amazon Aurora RDS DB instance with multi-AZ enabled. We will also simulate a database failover from one AZ to another.
   
2. You will practice using Amazon Aurora.
   
3. Duration: 1 hour
   
4. AWS Region: US East (N. Virginia)
   

Introduction

What is Amazon RDS?

Amazon RDS

1. Amazon Relational Database Service (Amazon RDS)  is a Relational Database service that offers high availability and throughput.
   
2. Amazon RDS comes with great features that include Multi-AZ feature and Read Replica that ensures no data loss.
   
3. Amazon RDS also provides you high scalability where you can scale up and scale down depending on your needs.
   
4. Amazon RDS provides you with six familiar database engines which include MySQL, Amazon Aurora, PostgreSQL, MariaDB, Oracle Database and SQL Server.
   

Multi-AZ

1. Multi-Availability zone ( Multi-AZ ) is a feature that comes with Amazon RDS that  provides you with high availability and durability for Database instances.
   
2. When we are opting for the Multi-AZ database instance, it will automatically create a Primary DB instance and parallelly replicate the data to the standby instances in different availability zones in that region. However, we can’t access the standby instances, unlike primary instances.
   
3. The main purpose of Multi-AZ is to provide a failover option for primary RDS instances.
   
4. Amazon RDS uses the Failover mechanism for Oracle, MYSQL, MariaDB and PostgreSQL instances.
   
5. The RDS Failover process happens automatically and is managed by AWS  without human intervention.
   
6. Amazon RDS uses the concept of SQL Mirroring for Replicating data to standby instances in the different availability zones and both primary and standby instances use the same endpoint.
   

Reasons for Failover

• The failover process will take place due to one of the following reasons occurring in the primary instances:
  
• Host Failure
  
• DB instance class modification.
  
• Instance rebooting
  
• Availability zone failure
  
• RDS maintenance
  

Conditions for enabling Multi-AZ on RDS

• A minimum of two different availability zones should be present in a DB subnet group where you are launching your Primary DB instance.
  

Amazon Aurora and Read Replica

• Amazon Aurora is the DB instance, which is a little different when comparing to the above-mentioned Instance types.
  
• Aurora uses the concept of a DB cluster where the primary instance replicates its data across different availability zones.
  
• In the case of a primary instance failure, Aurora automatically launches its primary instance from the replicated instance in a different availability zone.
  
• Multi-AZ on Aurora clusters makes RDS replicate or provision a replica of the master database to a different availability zone within a Region.
  

 Lab Tasks

1. In this lab session, first we are going to launch an Amazon Aurora RDS DB instance with Multi-AZ enabled.
   
2. Connect to the RDS database instance (using its endpoint) from your local machine.
   
3. Create a test database and table in your Master RDS DB instance.
   
4. Force the Master DB instance to failover.
   
5. After Failover, Master will change to Reader and Reader will change to Master
   
6. Connect to the new Master to test the database replication.
   

Steps

Create an Amazon Aurora database with Multi-AZ enabled

• Make sure you are in N.Virginia Region.
  
• Navigate to RDS under the database section of the Services menu
  
• Click on Databases in the left Panel.
  
• Click on .
  Note: If you are in the original interface, make sure to click on the Switch to the new database creation flow as shown below.
  
  
• Next we’ll configure the database on the Create Database Page
  
  • Choose a Database Creation Method: 
    
    • Select Standard Create
      

  
  

• In Engine options:
  

  
  

  • Engine type             : Choose Amazon Aurora
    
  • Database location : Regional
    
  • Version                    : Default (Aurora (MYSQL)-5.6.10a)
    
  • Edition                     : Choose Amazon Aurora with MySQL compatibility
    
  • In Database features: 
    
    • Select One writer and multiple readers (default)
      

    
    

• Choose Template: Dev/Test
  
• Fill in the required details for the database (Aurora Cluster Settings)
  
  • DB cluster identifier: Specify cluster name MyAuroraCluster
    
  • Give the following details in the credential settings 
    
    • Master Username: WhizlabsAdmin
      
    • Master password: Whizlabs123
      
    • Confirm password: Whizlabs123
      
    • Note: This is the username and password used to log into your database. Please make note of them.
      
    • Choosing DB instance size:
      

  
  

  • DB instance class: Choose Burstable classes (includes t classes) 
    
  • Availability and Durability : Choose Multi-AZ deployment: Create Aurora replica in a different AZ  as shown below:
    

  
  

  • Connectivity
    
    • Choose the Default VPC
      

  
  

  • Additional connectivity configuration
    
    • Subnet group        :  Default
      
    • Publicly accessible    : Yes
      
    • VPC security group    : Chose default
      
    • Availability zone        : Leave as default
      
    • Database port        : 3306                    
      
  • Additional configuration:
    
    • Database options
      
      • DB instance identifier:  whizlabs-cluster
        
      • Initial database name:  whizlabsrds
        
    • Leave other settings as default
      

1. One the details above have been filled in, click on .
   
2. It will take around 10-15 minutes for the database to be created. Please wait untill database status changes from creating to available.
   
3. Once the database has been created, you should see the following page:
   

Similar to the screenshot, you should be able to see that our database launched in multiple AZs, namely us-east-1a and us-east-1e

Connecting to the Aurora (MySQL) database on RDS

Now we have successfully launched Aurora RDS with Multi-AZ enabled. To connect to the new Aurora database, we need the endpoint.

1. Click on the RDS cluster name and then navigate to Connectivity & security to find the endpoint of your Master and Reader instances, with which you can connect to your DB instance.
   

• The endpoints you see to be similar to these examples:
  

  Master: whizlabs-cluster-cdegnvsebaim.us-east-1.rds.amazonaws.com
  

  Reader: whizlabs-cluster.cluster-ro-cdegnvsebaim.us-east-1.rds.amazonaws.com
  

Note: Please carefully look at the role of the DB instance ( reader vs writer ) and their respective availability zones. ( here us-east-1a and us-east-1e )

1. To get the endpoint of the RDS instances, click on the name of the cluster. Then you should click on Endpoints. This will expose the read and write endpoints for the database. See the example below:
   

Creating an EC2 Instance

1. Click on 
   
2. Choose an Amazon Machine Image (AMI): 
   
3. Choose an Instance Type: Select  and then click on the .
   
4. On the Configure Instance Details page:
   

• Network: Select default available VPC
  
• Subnet     : Default selected
  
• Auto-assign Public IP : Enable – It should be enabled because the public IP is needed for connecting to EC2 via SSH.
  
• Click on .
  
• Under the User data section, enter the following script, (which installs MySQL):
  

  #!/bin/bash -ex 
  

  yum install mysql -y
  

• Now click on 
  

1. Add Storage Page : No need to change anything in this step. Click on .
   
2. Add Tags Page
   

• Click on 
  
• Key    : Name
  
• Value    :  MyRdsEc2server
  
• Click on 
  

1. On the Configure Security Group page:
   

• Assign a security group: Create a new security group
  
• Security group name: MyEc2server-SG
  
• Description: Security for ec2 server to connect with RDS
  
• To add SSH:
  
  • Choose Type: 
    
  • Source: Custom (Allow specific IP address) or Anywhere (From ALL IP addresses accessible).
    

1. Review and Launch : Review all your settings and click on .
   
2. Key Pair – Select Create a new key Pair  with name MySSHKey, click on  and save it to your local machine.
   
3. Once the download is complete, click on .
   
4. After 1-2 minutes, the Instance State will become running as shown below:
   

Connecting the EC2 Server to RDS:

1. Now we need to connect the RDS with ec2 server in order to eventually connect with the Aurora database.
   
2. Navigate to RDS available under the database section of the Services menu.
   
3. Click on Master (writer) database and click on rds-maz-SG under VPC security groups as shown below:
   

1.  SSH into the EC2 instance we just created through the following steps in SSH into EC2 Instance.
2. Switch to the root user using the command : sudo -s
   
3. Log into the RDS instance using the below command:
   

• mysql  -h whizlabs-cluster.cdegnvsebaim.us-east-1.rds.amazonaws.com -u WhizlabsAdmin -p
  
• Syntax: mysql -h <Hostname> -u <username> -p
  

  Note: Make sure to change the above Writer Cluster endpoint and Username with your’s.
  

• Host name : whizlabs-cluster.cdegnvsebaim.us-east-1.rds.amazonaws.com (Writer cluster endpoint)
  
• Username : WhizlabsAdmin
  
• Password : Whizlabs123 (Use yours incase you changed the password while creating RDS)
  
• Database name : whizlabsrds
  
• You should now be able to log into the database, as shown below:
  

   
  

1. List all Databases:
   

• Show databases;
  

  Now you will see the database whizlabsrds created while launching the RDS cluster.
  

1. Now create the database in the master RDS as given in the screenshot. We’ll create a demo database named auroro_db.
   

• Create database auroro_db;
  

1. Select the newly-created database:
   

• use  auroro_db;
  

  
  

1. Next we’ll create a table named students and insert few rows of data using list of commands:
   

• CREATE TABLE students ( subject_id INT AUTO_INCREMENT, subject_name VARCHAR(255) NOT NULL,  teacher VARCHAR(255),start_date DATE, lesson TEXT,PRIMARY KEY (subject_id));
  

  
  

1. Insert data into the table:
   

• INSERT INTO students(subject_name, teacher) VALUES (‘English’, ‘John Taylor’);
  
• INSERT INTO students(subject_name, teacher) VALUES (‘Science’, ‘Mary Smith’);
  
• INSERT INTO students(subject_name, teacher) VALUES (‘Maths’, ‘Ted Miller’);
  
• INSERT INTO students(subject_name, teacher) VALUES (‘Arts’, ‘Suzan Carpenter’);
  

1. Now you can view the contents of the table student using the below command:
   

• select * from students;
  

  
  

Forcing a Failover to Test Multi-AZ

1. To test if Multi-AZ is working, we will create a situation where master fails and the read replica has to become the new master.
   
2. On the next screen, confirm the Failover.
   

1. Wait for a few minutes for the RDS instances to failover.
   

• ( i.e Master becomes Reader and Reader becomes Master as shown below )
  

  
  

Testing the Failover Condition

• Now connect to RDS with new Master endpoint
  
  • Copy the endpoint of the new Master cluster and replace it with your endpoint link.
    

  Mysql -h <endpoint> -u <username> -p    and press [Enter]
  

• mysql  -h whizlabs-cluster-instance-1-us-east-1d.cdegnvsebaim.us-east-1.rds.amazonaws.com -u WhizlabsAdmin -p
  

  password: Whizlabs123
  

1. You will be able to Log into MySQL and check for the database and table created in the master DB instance before the failover.
   

You can notice the resources created on the original master db are present, implying that the Failover worked successfully.

• Show databases;
  
• use  auroro_db;
  

  
  

  • Now check the existence of table named students and data  (that we created earlier in the lab):
    
    • show tables;
      
    • select * from students;
      

   
  

Completion and Conclusion

• You have successfully used AWS management console to create Amazon Aurora MySQL database with Multi-Az enabled.
  
• You have created an Amazon Aurora database on RDS.
  
• You have successfully connected to the Amazon Aurora database and tested the failover condition with Multi-AZ enabled.