In order to maintain the reliability, availability, and performance of Amazon RDS, we need to collect monitoring data so that we can easily debug a multi-point failure. With Amazon RDS, you can monitor network throughput, I/O for read, write, and/or metadata operations, client connections, and burst credit balances for your DB instances. We should also consider storing historical monitoring data. This stored data will give you a baseline to compare against with current performance data.

Below are examples of some monitoring data and how they help in maintaining healthy RDS instances.

• High CPU or RAM consumption – High values for CPU or RAM consumption might be appropriate, provided that they are in keeping with your goals for your application (like throughput or concurrency) and are expected.

• Disk space consumption – Investigate disk space consumption if space used is consistently at or above 85 percent of the total disk space. See if it is possible to delete data from the instance or archive data to a different system to free up space.

• Network traffic – For network traffic, talk with your system administrator to understand what expected throughput is for your domain network and Internet connection. Investigate network traffic if throughput is consistently lower than expected.

• Database connections – Consider constraining database connections if you see high numbers of user connections in conjunction with decreases in instance performance and response time.

• IOPS metrics – The expected values for IOPS metrics depend on disk specification and server configuration, so use your baseline to know what is typical. Investigate if values are consistently different than your baseline. For best IOPS performance, make sure your typical working set will fit into memory to minimize read and write operations.

Monitoring with Amazon CloudWatch

Amazon RDS sends metrics and dimensions to Amazon CloudWatch every minute. We can monitor these metrices from the AWS console as shown in the below diagrams.

Amazon RDS creates automated backups of your DB instance during the backup window of your DB instance and stores them as volume snapshots.

Automated Backup

For Automated backup to work properly, followings are the criteria.

• Your DB instance must be in the ACTIVE state for automated backups to occur. Automated backups don”t occur while your DB instance is in a state other than ACTIVE, for example STORAGE_FULL.

• Automated backups and automated snapshots don”t occur while a copy is executing in the same region for the same DB instance.

The first snapshot of a DB instance contains the data for the full DB instance. Subsequent snapshots of the same DB instance are incremental, which means that only the data that has changed after your most recent snapshot is saved.

The following diagram shows how we can configure the automated backup window for a DB instance using the AWS console. To disable the automated backup, set the number of days to zero.

Manual Backup

We can also take backups manually by using snapshots. To take a snapshot manually we use the instance action option after selecting the instance as shown below.

We can aslo take manual backup using the CLI with the below command.

aws rds create-db-snapshot /
    --db-instance-identifier sourcedbinstance /
    --db-snapshot-identifier dbsnapshotinstance

In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica to provide data redundancy, eliminate I/O freezes, and minimize latency spikes during system backups. Running a DB instance with high availability can enhance availability during planned system maintenance and help protect your databases against DB instance failure and Availability Zone disruption.

Creating Multi-AZ deployment

You can choose the option of selecting a multi-AZ deployment when creating a DB instance or you can also choose the option of modifying an existing db instance to become a multi-AZ DB instance. The following diagram shows the option where we are modifying an existing option. But the same option is also available during the creation of the DB instance.

How Multi-AZ works

In the event of a planned or unplanned outage of your DB instance, Amazon RDS automatically switches to a standby replica in another Availability Zone if you have enabled Multi-AZ. The time it takes for the failover to complete depends on the database activity and other conditions at the time the primary DB instance became unavailable. Failover times are typically 60-120 seconds. However, large transactions or a lengthy recovery process can increase failover time.

Multi-AZ deployments for Oracle, PostgreSQL, MySQL, and MariaDB DB instances use Amazon”s failover technology. SQL Server DB instances use SQL Server Mirroring.

Throughout the life cycle of amazon RDS DB instances, many DB events occur which are important to be known beforehand. For example – A backup of the DB instance has started, or an error has occurred while restarting MySQL or MariaDB.

Notification Categories

Based on the nature of the event, notifications can be classified into following categories.

Creating Event Notifications

Below are the steps to create event subscriptions through which the notifications are sent to the subscriber.

Step-1

Choose the Event subscription tab from the RDS dashboard.

Step-2

We give a name to the event and choose the subscription source.

Now choosing the source.

Step-3

In the next step we see the details of the source type of a subscription type chosen for the event.

To access the Amazon RDS DB instance the user needs specific permissions. This is configured using AWS IAM (Identity and Access management). In this tutorial we will see how this configuration is done.

The configuration involves two parts.

• Authentication

• Access Control

Authentication

It involves creating the username, password and generating the access keys for the user. With help of access key, it is possible to make programmatic access to the AWS RDS service. The SDK and CLI tools use the access keys to cryptographically sign in with the request.

We can aslo use an IAM Role to authenticate a user. But the role is not attached to any specific user, rather any user can assume the role temporarily and complete the required task. After the task is over the role can be revoked and the user loses the authentication ability.

Access Control

After a user is authenticated, a policy attached to that user determines the type of tasks the uer can carry on. Below is an example of policy which allows the creation of a RDS DB instance, on a t2.micro instance for the DB Engine MySQL.

{
    "Version": "2018-09-11",
    "Statement": [
        {
            "Sid": "AllowCreateDBInstanceOnly",
            "Effect": "Allow",
            "Action": [
                "rds:CreateDBInstance"
            ],
            "Resource": [
                "arn:aws:rds:*:123456789012:db:test*",
                "arn:aws:rds:*:123456789012:og:default*",
                "arn:aws:rds:*:123456789012:pg:default*",
                "arn:aws:rds:*:123456789012:subgrp:default"
            ],
            "Condition": {
                "StringEquals": {
                    "rds:DatabaseEngine": "mysql",
                    "rds:DatabaseClass": "db.t2.micro"
                }
            }
        }
    ]
}

Action on Any RDS Resource

In the below example we see a policy that allows any describe action on any RDS resource. The * symbol is used to represent any resource.

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Sid":"AllowRDSDescribe",
         "Effect":"Allow",
         "Action":"rds:Describe*",
         "Resource":"*"
      }
   ]
}

Disallow deleting a DB Instance

The below policy disallows a user from deleting a specific DB instance.

{
   "Version":"2012-10-17",
   "Statement":[
      {
         "Sid":"DenyDelete1",
         "Effect":"Deny",
         "Action":"rds:DeleteDBInstance",
         "Resource":"arn:aws:rds:us-west-2:123456789012:db:my-mysql-instance"
      }
   ]
}

To connect to Amazon RDS PostgreSQL DB we need a client software. In this case we use pgAdmin. Install it using the link .

After it is successfully installed we follow the steps below to connect it to the Amazon RDS.

Step-1

From the DB instance details get the end point.

Step-2

As it is a browser based interface, choose the add server option as shown in the below diagram.

Step-3

Use the end point and the master user credentials as the connection details.

Step-4

Once connected, we get the following window.

Amazon RDS PostgreSQL provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the PostgreSQL database we can use CLI tools to run the import and export commands to get the data from other sources in and out of the RDS database.

Below are the steps through which the PostgreSQL data migration happens using the export and import mechanisms.

Importing from an Amazon EC2 Instance

When there is a PostgreSQL server on an Amazon EC2 instance and it needs to be moved to a RDS – PostgreSQL DB instance, we use the below steps to do that.

Export The Data

Create a file using pg_dump that contains the data to be loaded. A dump file containing data and all the meta data of the database is created using the pg_dump utility. The following command in the psql utility cerates the dump file from the database named mydbname.

pg_dump dbname=mydbname -f mydbnamedump.sql

Create Target DB Instance

Next, we create the target DB instance and restore the data into it using the pg_restore command.

createdb [new database name]
pg_restore -v -h [endpoint of instance] -U [master username] -d [new database name] [database].dump

Create Target Database

Use psql to create the database on the DB instance and load the data.

psql
   -f mydbnamedump.sql
   --host awsdbpginstance.d34f4mnfggv0.us-west-2.rds.amazonaws.com
   --port 8199
   --username awsdbuser
   --password awsdbpassword
   --dbname mynewdb

Amazon RDS MySQL provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the MySQL database we can use CLI tools to run the import and export commands to get the data from other sources in and out of the RDS database. Below are the scenarios to consider when deciding on the approach to the import the data into the Amazon RDS – MySQL database.

From an Existing MySQL database

An existing MySQL DB can be present on premise or in another EC2 instance. Diagrammatically what we do is shown below.

Creating a backup from On-Premise DB

As a first step we create a backup of the on-premise database using the below command.

mysqldump -u user -p[user_password] [database_name] > backupfile.sql

A file with name backupfile.sql is created which contains the table structure along with the data to be used.

Storing the backup file in S3.

Upload the backup file created above to a pre-decided Amazon S3 bucket in the same region where the target RDS MySQL DB database is present. You can follow to learn about how to upload.

Import data from Amazon S3 to RDS- MySQL database

You can use the following Amazon CLI command to import the data from S3 to MySQL DB.

aws rds restore-db-instance-from-s3
--allocated-storage 125
--db-instance-identifier tddbidentifier
--db-instance-class db.m4.small
--engine mysql
--master-user-name masterawsuser
--master-user-password masteruserpassword
--s3-bucket-name tpbucket
--s3-ingestion-role-arn arn:aws:iam::account-number:role/rolename
--s3-prefix bucketprefix
--source-engine mysql
--source-engine-version 5.6.27

From Another RDS-MySQL Instance

There may be scenarios when you want data from an existing RDS MYSQL DB to be taken into another RDS MYSQL DB. For example, to cerate a Disaster recovery DB or create a DB only for business reporting etc. In such scenario, we create read replicas which are a copy of their source DB and then promote that read replica to a new DB instance. They are used to prevent direct heavy read from the original source DB when we want to copy the data.

create a read-replica

aws rds create-db-instance-read-replica
    --db-instance-identifier myreadreplica
    --source-db-instance-identifier mydbinstance

Promote a Read replica to DB Instance

Now as we have the replica, we can promote it to a standalone DB instance. This will serve our end need of importing data from o RDS – Mysql DB to a new one. The following command is used to complete the promotion of a read replica to a db instance.

aws rds create-db-instance-read-replica
    --db-instance-identifier readreplica_name
    --region target_region_name
    --db-subnet-group-name subnet_name
    --source-db-instance-identifier arn:aws:rds:region_name:11323467889012:db:mysql_instance1

From Any Database

In order to import data from any other database to Amazon RDS – MySQL, we have to use the amazon Data Migration Service also called Amazon DMS. It uses Schema conversion tool to translate the existing data base to a the MYSQL platform. The below diagram explains the overall process. Also it works on the similar principle of replication as described in the previous section.

Exporting Data from MySQL

Exporting of data from Amazon RDS Mysql DB is a straight forwards process where it works on the same replication principle we have seen above. Below are the steps to carry out the export process.

• Start the instance of MySQL running external to Amazon RDS.
• Designate the MySQL DB instance to be the replication source.
• Use mysqldump to transfer the database from the Amazon RDS instance to the instance external to Amazon RDS.

Below is the code for the mysqldum command

mysqldump -h RDS instance endpoint
    -u user
    -p password
    --port=3306
    --single-transaction
    --routines
    --triggers
    --databases  database database2
    --compress
    --compact | mysql
        -h MySQL host
        -u master user
        -p password
        --port 3306

As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a MariaDB can be done in three ways. Using AWS management console, AWS CLI or AWS API. We will look at each of these approaches one by one.

Using AWS management Console

AWS management console is the most convenient way to get started with RDS. You login to the AWS console using your AWS account details, locate the RDS service and then follow the steps shown below to create a MariaDB instance.

Step-1

Select the MariaDB Engine form the console.

Step-2

Specify the required DB details.

Step-3

In this step you decide on the db instance class, amount of storage allocated also set the master password along with few other details.

Stpe—4

This is the final step when you mention the vpc and security settings, encryption, backup options and log export etc. For brevity the screen shot has been shortened showing only the final options.

Stpe—5

In the final step we choose the create Data base option.

Using CLI

To create a MariaDB instance by using the AWS CLI, call the create-db-instance command with the parameters below.

aws rds create-db-instance
    --db-instance-identifier mydbinstance
    --db-instance-class db.m4.xlarge
    --engine mariadb
    --allocated-storage 20
    --master-username masteruser
    --master-user-password masteruserpassword
    --backup-retention-period 3

Using API

To create a MariaDB instance by using the Amazon RDS API, we call the CreateDBInstance action with the parameters as shown below.

https://rds.us-west-2.amazonaws.com/
    ?Action=CreateDBInstance
    &AllocatedStorage=20
    &BackupRetentionPeriod=3
    &DBInstanceClass=db.m4.xlarge
    &DBInstanceIdentifier=mydbinstance
    &DBName=mydatabase
    &DBSecurityGroups.member.1=mysecuritygroup
    &DBSubnetGroup=mydbsubnetgroup
    &Engine=mariadb
    &MasterUserPassword=masteruserpassword
    &MasterUsername=masterawsuser
    &Version=2014-10-31
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140213/us-west-2/rds/aws4_request
    &X-Amz-Date=20140213T162136Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=8052a76dfb18469393c5f0182cdab0ebc224a9c7c5c949155376c1c250fc7ec3

As a cloud platform AWS gives you very minimal number of steps to setup a DB in RDS. Creating a MYSqlDB can be done in three ways. Using AWS management console, AWS CLI or AWS API. We will look at each of these approaches one by one.

Using AWS management Console

AWS management console is the most convenient way to get started with RDS. You login to the AWS console using your AWS account details, locate the RDS service and then follow the steps shown below to create a MariaDB instance.

Step-1

Select the MSSql Engine form the console.

Step-2

Specify the required DB details.

Step-3

In this step you decide on the db instance class, amount of storage allocated also set the master password along with few other details.

Stpe—4

The final step is to click create database, after which the MySql DB is created with a available end point as shown below.

Using CLI

To create a MySql DB instance by using the AWS CLI, call the create-db-instance command with the parameters below.

aws rds create-db-instance
    --db-instance-identifier mydbinstance
    --db-instance-class db.m1.small
    --engine MySQL
    --allocated-storage 20
    --master-username masterawsuser
    --master-user-password masteruserpassword
    --backup-retention-period 3

Using API

To create a MariaDB instance by using the Amazon RDS API, we call the CreateDBInstance action with the parameters as shown below.

https://rds.us-west-2.amazonaws.com/
    ?Action=CreateDBInstance
    &AllocatedStorage=20
    &BackupRetentionPeriod=3
    &DBInstanceClass=db.m3.medium
    &DBInstanceIdentifier=mydbinstance
    &DBName=mydatabase
    &DBSecurityGroups.member.1=mysecuritygroup
    &DBSubnetGroup=mydbsubnetgroup
    &Engine=mysql
    &MasterUserPassword=masteruserpassword
    &MasterUsername=masterawsuser
    &Version=2014-10-31
    &X-Amz-Algorithm=AWS4-HMAC-SHA256
    &X-Amz-Credential=AKIADQKE4SARGYLE/20140213/us-west-2/rds/aws4_request
    &X-Amz-Date=20140213T162136Z
    &X-Amz-SignedHeaders=content-type;host;user-agent;x-amz-content-sha256;x-amz-date
    &X-Amz-Signature=8052a76dfb18469393c5f0182cdab0ebc224a9c7c5c949155376c1c250fc7ec3