AWS – Creating an RDS database with automatic failover

Install PHP on CentOS 8

In this recipe, we’re going to create a MySQL Relational Database Service (RDS) database instance configured in multi-AZ (Availability Zone) mode to facilitate automatic failover. This is a best practice and should be done for all production databases. The standby database in a separate AZ is not the same thing as a read replica—its sole purpose is to enable High Availability (HA).

Getting ready

The default VPC will work fine for this example. Once you are comfortable with creating databases, you may want to consider a VPC containing private subnets that you can use to segment your database away from the internet and other resources (in the style of a three-tier application). Either way, you’ll need to note down the following:

  • The ID of the VPC
  • The CIDR range of the VPC
  • The IDs of at least two subnets in your VPC—these subnets need to be in different AZs, for example, us-east-1a and us-east-1b

How to do it…

Create a new CloudFormation template. We’re going to add a total of 12 parameters to it:

  1. The first three parameters will contain the values we mentioned in the Getting ready section:
    Type: AWS::EC2::VPC::Id 
    Description: VPC where DB will launch 
    Type: List<AWS::EC2::Subnet::Id> 
    Description: Subnets where the DB will launch (pick at least 2) 
    Type: String 
    Description: Allow this CIDR block to access the DB 
    Default: ""
  1. We’re also going to add the database credentials as parameters. This is better than storing credentials in our infrastructure source code, but you should also investigate AWS Secret Manager and Parameter Store for even more secure options. Note that the password contains the NoEcho parameter set to true. This stops CloudFormation from outputting the password wherever the CloudFormation stack details are displayed:
    Type: String 
    Description: Username to access the database 
    MinLength: 1 
    AllowedPattern: "[a-zA-Z][a-zA-Z0-9]*" 
    ConstraintDescription: must start with a letter, must
      be alphanumeric 
    Type: String 
    Description: Password to access the database 
    MinLength: 1 
    AllowedPattern: "[a-zA-Z0-9]*" 
    NoEcho: true 
    ConstraintDescription: must be alphanumeric
  1. The next block of parameters pertains to cost and performance. They should be mostly self-explanatory. Refer to the AWS documentation on database instance types should you wish to change the instance class for this example. We’re supplying a default value of 10 GB for the storage size and choosing a magnetic (standard) volume for the storage type. gp2 offers better performance, but it costs a little more:
    Type: String 
    Description: The instance type to use for this database 
    Default: db.t2.micro 
    Type: Number 
    Description: Amount of storage to allocate (in GB) 
    Default: 10 
    Type: String 
    Description: Type of storage volume to use
      (standard [magnetic] or gp2) 
    Default: standard 
      - standard 
      - gp2

  1. We need to set some additional parameters for our database. These are the MySQL engine version and port. Refer to the AWS documentation for a list of all of the available versions. We are setting a default value for this parameter as the latest version of MySQL at the time of writing:
    Type: String 
    Description: DB engine version 
    Default: "5.7.26" 
    Type: Number 
    Description: Port number to allocate 
    Default: 3306 
    MinValue: 1150 
    MaxValue: 65535
  1. Finally, we are going to define some parameters relating to backup and availability. We want our database to run in multi-AZ mode; we set this to true by default. We also set a backup retention period of 1 day by default; you might want to choose a period larger than this. If you set this value to 0, backups will be disabled (not recommended!):
    Type: String 
    Description: Should this DB be deployed in Multi-AZ configuration? 
    Default: true 
      - true 
      - false 
    Type: Number 
    Description: How many days to keep backups (0 disables backups) 
    Default: 1 
    MinValue: 0 
    MaxValue: 35
  1. We’re done with the parameters for this template; we can now go ahead and start defining our Resources parameters. First of all, we want a security group for our DB to reside in. This security group allows inbound access to the database port from the CIDR range we’ve defined:
    Type: AWS::EC2::SecurityGroup 
      GroupDescription: Example security group for inbound access to DB 
        - IpProtocol: tcp 
          CidrIp: !Ref SecurityGroupAllowCidr 
          FromPort: !Ref DBPort 
          ToPort: !Ref DBPort 
      VpcId: !Ref VPCId
  1. Next, we need to define a DBSubnetGroup resource. This resource is used to declare which subnet(s) our DB will reside in. We define two subnets for this resource so that the primary and standby servers will reside in separate AVs:
    Type: AWS::RDS::DBSubnetGroup 
      DBSubnetGroupDescription: Example subnet group for example DB 
        - Fn::Select: [ 0, Ref: SubnetIds ] 
        - Fn::Select: [ 1, Ref: SubnetIds ]
  1. Finally, we define our RDS instance resource. We specify it as being a MySQL database and the rest of the properties are made up of the parameters and resources that we’ve defined previously. Lots of !Ref is required here:
    Type: AWS::RDS::DBInstance 
      AllocatedStorage: !Ref DBStorageAmount 
      BackupRetentionPeriod: !Ref DBBackupRetentionPeriod 
      DBInstanceClass: !Ref DBInstanceClass 
      DBSubnetGroupName: !Ref ExampleDBSubnetGroup 
      Engine: mysql 
      EngineVersion: !Ref DBEngineVersion 
      MasterUsername: !Ref DBUsername 
      MasterUserPassword: !Ref DBPassword 
      MultiAZ: !Ref DBMultiAZ 
      StorageType: !Ref DBStorageType 
        - !GetAtt ExampleDBSecurityGroup.GroupId
  1. For good measure, we can add an output to this template that will return the hostname for this RDS database:
    Value: !GetAtt ExampleDBInstance.Endpoint.Address

  1. You can provision the database via the CloudFormation web console or use a CLI command like so:
 aws cloudformation create-stack \
 --stack-name rds1 \
 --template-body \
 file://06-create-database-with-automatic-failover.yaml \
 --parameters \
 ParameterKey=DBUsername,ParameterValue=<username> \
 ParameterKey=DBPassword,ParameterValue=<password> \
 ParameterKey=SubnetIds,"ParameterValue='<subnet-id-a>, \
 <subnet-id-b>'" \

Be sure to delete the stack if you have no further use for it, to avoid any future charges.

How it works…

In a multi-AZ configuration, AWS will provision a standby MySQL instance in a separate AZ. Changes to your database will be replicated to the standby DB instance synchronously. If there is a problem with your primary DB instance, AWS will automatically fail over to the standby, promote it to be the primary DB, and provision a new standby.

You don’t have access to query standby databases directly. So, you can’t use it to handle all of your read queries, for example. If you wish to use additional database instances to increase read capacity, you’ll need to provision a read replica. We’ll cover those in a separate recipe.

Backups will always be taken from the standby instance, which means there is no interruption to your DB availability. This is not the case if you opted against deploying your DB in multi-AZ mode.

When you deploy this example, it will take roughly 20 minutes or more for the stack to report completion. This is because the RDS service needs to go through the following process to provide a fully working multi-AZ database:

  • Provision the primary database.
  • Back up the primary database.
  • Provision the standby database using the backup from the primary.
  • Configure both databases for synchronous replication.
Be careful about making changes to your RDS configuration after you’ve started writing data to it, especially when using CloudFormation updates. Some RDS configuration changes require the database to be re-provisioned, which can result in data loss. We’d recommend using CloudFormation changesets, which will allow you to see which changes are about to cause destructive behavior. The CloudFormation RDS documentation also provides some information on this.

There’s more…

You can define a maintenance window for your RDS instance. This is the period when AWS will perform maintenance tasks such as security patches or minor version upgrades. If you don’t specify a maintenance window (which we don’t in this example), one is chosen for you.

Comments are closed.