Working with RDS in Python using Boto3

Kelvin Galabuzi

Kelvin Galabuzi

0
(0)

Amazon Relational Database Service (Amazon RDS) is a relational fully-managed database service that makes it easy to set up, operate, and scale a relational database in the cloud. With RDS, you can easily set up six familiar database engines databases, including Amazon Aurora, PostgreSQL, MySQL, MariaDB, Oracle Database, and SQL Server. This service has additional features such as automated patching, monitoring, backups, enterprise-grade security, high availability, scaling, simplified storage management, and many others. This article will cover how to create and manage Amazon RDS databases using the Boto3 library (AWS SDK for Python).

Prerequisites

To start working with Amazon RDS using Boto3, you need to set up your Python environment on your laptop.

In summary, this is what you will need:

  • Python 3
  • Boto3
  • AWS CLI tools

Alternatively, you can set up and launch a Cloud9 IDE.

Introduction

Amazon RDS reduces management and operational overhead for the typical database administration tasks and enables your team to focus on developing and optimizing applications to achieve faster business results. This article will focus on the automation of the most common database operations in Amazon RDS using Python and Boto3 library.

Create an Amazon RDS instance using Boto3

To create an RDS instance, you need to use the create_db_instance() method of the Boto3 library.

In the example below, we will create a new MySQL RDS instance inside the default VPC.

You can also set a different VPC and Security Group for your new instance by specifying the DBSecurityGroups and VPCSecurityGroupsIds parameters.

import boto3

client = boto3.client('rds')

response = client.create_db_instance(
    AllocatedStorage=5,
    DBInstanceClass='db.t2.micro',
    DBInstanceIdentifier='database-instance-01',
    Engine='MySQL',
    MasterUserPassword='testpw0021',
    MasterUsername='admin01',
)

print(response)

Here is the execution output:

Create an Amazon RDS instance using Boto3

Managing RDS Instances using Boto3

Stop RDS instance

To stop an RDS instance, you need to use the stop_db_instance() method of the Boto3 library. You can provide the DBSnapshotIdentifier argument to create a snapshot of the database during the stop operation.

import boto3

client = boto3.client('rds')

response = client.stop_db_instance(
    DBInstanceIdentifier='database-instance-01',
    DBSnapshotIdentifier='stop-snapshot001'
)
print(response)

Here is the execution output.

Stop RDS instance using Boto3

Start RDS instance

To start a stopped RDS instance, you need to use the start_db_instance() method of the Boto3 library and provide the name of your database to the DBInstanceIdentifier parameter.

import boto3

client = boto3.client('rds')

response = client.start_db_instance(
    DBInstanceIdentifier='database-instance-01'
)
print(response)

Here is the execution output.

Start RDS Instance using Boto3

Reboot RDS instance

To reboot an RDS instance, you need to use the reboot_db_instance() method with the DBInstanceIdentifier containing the database server name that you want to reboot.

import boto3

client = boto3.client('rds')

response = client.reboot_db_instance(
    DBInstanceIdentifier='database-instance-01'
)
print(response)

Here is the execution output:

Reboot RDS Instance using Boto3

Modify RDS instance configuration

To modify an RDS instance configuration, you need to use the modify_db_instance() method of the Boto3 library.

To a list of allowed configurations, you need to use the describe_valid_db_instance_modifications() method of the Boto3 library. In this example, I will change the MasterUserPassword of the RDS Instance:

import boto3

client = boto3.client('rds')

response = client.modify_db_instance(
    DBInstanceIdentifier='database-instance-01',
    MasterUserPassword='new-pa$$word'
  )  
print(response)

Here is the execution output:

Modify RDS instance configuration

Create RDS instance read-replica

To create an RDS instance read replica, you need to use the create_db_instance_read_replica() method.

Amazon RDS database read replicas allow you to have more than one source for the database read operations to scale your single database to improve the performance of read-intensive database workloads.

import boto3

client = boto3.client('rds')

response = client.create_db_instance_read_replica(
    AvailabilityZone='us-east-2c',
    CopyTagsToSnapshot=True,
    DBInstanceClass='db.t2.micro',
    DBInstanceIdentifier='db-instance-01-readreplica',
    PubliclyAccessible=True,
    SourceDBInstanceIdentifier='database-instance-01',
    StorageType='gp2',
    Tags=[
        {
            'Key': 'ReadreplicaNumber',
            'Value': 'readreplica001',
        },
    ],
)
print(response)

Here is the execution output:

Create RDS instance read-replica

Promote a RDS read-replica to standalone instance

To promote a read-replica to a standalone instance, you need to use the promote_read_replica() method of the Boto3 library.

import boto3

client = boto3.client('rds')
response = client.promote_read_replica(
    BackupRetentionPeriod=5,
    DBInstanceIdentifier='db-instance-01-readreplica',
)
print(response)

Here is the execution output:

Promote a RDS read-replica to standalone instance

Describe RDS instance

To get all the properties of the RDS database instance, you need to use the describe_db_instances() method and pass the DBInstanceIdentifier parameter.

import boto3

client = boto3.client('rds')

response = client.describe_db_instances(
    DBInstanceIdentifier='database-instance-01',
)
print(response)

Here is the execution output:

Describe RDS instance

Filter RDS instances

To filter RDS instances, you need to use the describe_db_instances() method of the Boto3 library with provided Filter argument to return all information about specific database instances.

In the following example, I will use the database engine as the filter to retrieve all information about MySQL databases in my account.

import boto3

client = boto3.client('rds')

response = client.describe_db_instances(
    DBInstanceIdentifier='',
    Filters=[
        {
            'Name': 'engine',
            'Values': [
                'MySQL',
            ]
        }]
)
print(response)

Here is the execution output:

Filter RDS instances

Delete RDS instance

To delete an RDS database instance, you need to use the delete_db_instance() method of the Boto3 library with the DBInstanceIdentifier parameter. The SkipFinalSnapshot parameter prevents the creation of a final snapshot during the database delete operation.

import boto3

client = boto3.client('rds')
response = client.delete_db_instance(
    DBInstanceIdentifier='db-instance-01-readreplica',
    SkipFinalSnapshot=True,
)
print(response)

Here is the execution output:

Delete RDS instance using Boto3

Describe RDS engine versions

To retrieve a list of supported RDS engine versions, you need to use the describe_db_engine_versions() method. This method can also help you identify the database engine versions that you can use to upgrade your current database instance.

import boto3

client = boto3.client('rds')

response = client.describe_db_engine_versions(
    Engine='MySQL',
    EngineVersion='8.0.23'
)
print(response)

Here is the execution output:

Describe RDS engine versions

Add Tags to RDS resources

To add tags to an RDS resource (instance or cluster), you need to use the add_tags_to_resource() method of the Boto3 library and provide it the Amazon Resource Name(ARN) of the RDS resource.

import boto3

client = boto3.client('rds')

response = client.add_tags_to_resource(
    ResourceName='arn:aws:rds:us-east-2:585584209241:db:database-instance-01',
    Tags=[
        {
            'Key': 'Environment',
            'Value': 'Test',
        },
    ],
)
print(response)

Here is the execution output:

Add Tags to RDS resources

Create RDS parameter group

To create a new database parameter group, you need to use the create_db_parameter_group() method of the Boto3 library. Database parameter groups contain the database engine configuration values that are assigned to specific database instances.

To add a database instance to the parameter group, you need to use the modify_db_instance() method with provided DBParameterGroupName parameter.

import boto3

client = boto3.client('rds')

response = client.create_db_parameter_group(
    DBParameterGroupFamily='mysql8.0',
    DBParameterGroupName='MySQLParameterGroup',
    Description='For RDS Instances running 8.0',
)
print(response)

Here is the execution output:

Create RDS parameter group

Describe RDS parameter group

To retrieve information about the RDS database parameter group, you need to use the describe_db_parameters() method of the Boto3 library and provide it the DBParameterGroupName parameter.

import boto3

client = boto3.client('rds')
response = client.describe_db_parameters(
    DBParameterGroupName='mysqlparametergroup',
    MaxRecords=30
)
print(response)

Here is the execution output.

Describe RDS parameter group

Create an RDS option group

To create an RDS option group, you need to use the create_option_group() method of the Boto3 library. Option groups allow you to specify a set of configuration options for a specific RDS database engine.

You can apply the RDS option group to the already existing RDS database by using the modify_db_instance() method with the OptionGroupName parameter assigned.

import boto3

client = boto3.client('rds')
response = client.create_option_group(
    EngineName='MySQL',
    MajorEngineVersion='8.0',
    OptionGroupDescription='My MySQL 8.0 option group',
    OptionGroupName='mysql-group01',
)
print(response)

Here is the execution output:

Create an RDS option group

Managing Amazon RDS backups using Boto3

By default, AWS creates automated backups of your RDS database instance. In addition to that, you can create database snapshots to have the ability to restore your database to a specific point in time. The following section describes how you can do that using Python and Boto3.

Create a RDS snapshot

To create an RDS snapshot, you need to use the create_db_snapshot() method of the Boto3 library and pass the required parameters, such as the DBInstanceIdentifier and DBSnapshotIdentifier.

import boto3

client = boto3.client('rds')
response = client.create_db_snapshot(
    DBInstanceIdentifier='database-instance-01',
    DBSnapshotIdentifier='snapshot002',
)
print(response)

Here is the execution output.

Create a RDS snapshot using Boto3

Describe RDS snapshot

To retrieve information about an RDS snapshot, you need to use the describe_db_snapshot() method of the Boto3 library with the DBInstanceIdentifier and DBSnapshotIdentifier parameters.

import boto3

client = boto3.client('rds')
response = client.create_db_snapshot(
    DBInstanceIdentifier='database-instance-01',
    DBSnapshotIdentifier='snapshot003',
)
print(response)

Here is the execution output.

Describe RDS snapshot using Boto3

Additionally, you can get information about all the snapshots of the database instance.

import boto3

client = boto3.client('rds')
response = client.describe_db_snapshots(
    DBInstanceIdentifier='database-instance-01'
)
print(response)

Here is the execution output.

Describe all RDS instance snapshots using Boto3

Delete RDS snapshot

To delete an RDS snapshot, you need to use the delete_db_snapshot() method of the Boto3 library with the DBSnapshotIdentifier parameter.

import boto3

client = boto3.client('rds')
response = client.delete_db_snapshot(
    DBSnapshotIdentifier='snapshot003',
)
print(response)

Here is the execution output:

Delete RDS snapshot using Boto3

Describe RDS instance automated backups

To retrieve information about RDS instance automated backups, you need to use the describe_db_instance_automated_backups() method of the Boto3 library with the DbiResourceId and DBInstanceIdentifier parameters.

import boto3

client = boto3.client('rds')
response = client.describe_db_instance_automated_backups(
    DbiResourceId='db-WXNUQNZSZJDHOQHGQ4TY6Y7IZY',
    DBInstanceIdentifier='database-instance-01'
)
print(response)

Here is the execution output:

Describe RDS instance automated backups using Boto3

Restore RDS database to point-in-time

To restore a database to a point-in-time, you need to use the restore_db_instance_to_point_in_time() method of the Boto3 library with the RestoreTime, SourceDBInstanceIdentifier, and TargetDBInstanceIdentifier parameters.

import boto3
from datetime import datetime

client = boto3.client('rds')
response = client.restore_db_instance_to_point_in_time(
    RestoreTime=datetime(2021, 9, 15),
    SourceDBInstanceIdentifier='database-instance-01',
    TargetDBInstanceIdentifier='restored-db-01'
)
print(response)

Here is the execution output:

Restore RDS database to point-in-time using Boto3

Manage access to Amazon RDS database using Boto3

Amazon RDS provides various options for managing access to your database instances or clusters. The following section will describe some of those options and provide guidance on how to set them up for your Amazon RDS DB instance.

Create a RDS security group

To create a new RDS security group, you need to use the create_db_security_group() method of the Boto3 library. Security groups control networking access to your database instances or clusters using security group rules. For example, you can set up a security group rule that allows connections to your databases from a predefined list of IP addresses.

To replace a security group for an already existing database instance or cluster, you need to use the modify_db_instance() method.

import boto3

client = boto3.client('rds')

response = client.create_db_security_group(
    DBSecurityGroupDescription='MySQL DB security group',
    DBSecurityGroupName='mysqldbsecuritygroup',
)
print(response)

Here is the execution output:

Create a RDS security group using Boto3

Create RDS subnet group

To create an RDS subnet group, you need to use the create_db_subnet_group() method of the Boto3 library. Subnet groups allow you to place your Amazon RDS database instances to specific VPC subnets in different AWS Regions.

Like the security groups, you can modify the subnet groups in your RDS database instance using the modify_db_instance() method.


import boto3

client = boto3.client('rds')

response = client.create_db_subnet_group(
    DBSubnetGroupDescription='MySQL Databases Subnet Group',
    DBSubnetGroupName='mysqldbsubnetgroup',
    SubnetIds=[
        'subnet-e44a10a8',
        'subnet-6710950c',
    ],
)

print(response)

Here is the execution output:

Create RDS subnet group using Boto3

Create Amazon RDS Proxy

To create an Amazon RDS Proxy, you need to use the create_db_proxy() method of the Boto3 library. The Amazon RDS Proxy is a service that allows handling multiple connections to your Amazon RDS instance without overwhelming the instance itself. In addition to that, the Amazon RDS Proxy improves the security of your application’s access to the database using the IAM authentication and AWS Secrets Manager integration.

Note: The SecretArn and RoleArn parameters contain sensitive values. Replace those values in your code.

import boto3

client = boto3.client('rds')
response = client.create_db_proxy(
    DBProxyName='MySQLDBProxy',
    EngineFamily='MYSQL',
    Auth=[
        {
            'Description': 'UserLogin',
            'UserName': 'admin01',
            'SecretArn': ''
        },
    ],
    RoleArn='',
    VpcSubnetIds=[
        'subnet-e44a10a8',
        'subnet-6710950c'
    ],
    VpcSecurityGroupIds=[
        'sg-6dbc5f1b',
    ],
    RequireTLS=True,
    IdleClientTimeout=123,
    DebugLogging=False
)
print(response)

Here is the execution output:

Create Amazon RDS Proxy using Boto3

AWS RDS Instance Maintenance using Boto3

This section of the article will cover some of the most commonly used methods for RDS DB operations.

Apply pending database maintenance action

To apply a pending maintenance action, you need to use the apply_pending_maintenance_action() method of the Boto3 library. This method allows you to make changes in the RDS DB at a specific moment or immediately.

import boto3

client = boto3.client('rds')

response = client.apply_pending_maintenance_action(
    ApplyAction='db-upgrade',
    OptInType='immediate',
    ResourceIdentifier='arn:aws:rds:us-east-2:585584209241:db:database-instance-01'
)
print(response)

List RDS instance log files

To get a list of database log files for an AWS RDS instance, you need to use the describe_db_log_files() method.

import boto3

client = boto3.client('rds')
response = client.describe_db_log_files(
    DBInstanceIdentifier='database-instance-01',
    MaxRecords=100
)
print(response)

Here is the execution output.

List RDS instance log files using Boto3

Download RDS instance log files

To download the database log files, you can use the download_log_file_portion() method with such parameters as the DBInstanceIdentifier and LogFileName.

import boto3

client = boto3.client('rds')
response = client.download_db_log_file_portion(
    DBInstanceIdentifier='database-instance-01',
    LogFileName='error/mysql-error-running.log',
)
print(response)

Here is the execution output.

Download RDS instance log files using Boto3

Create event subscription for RDS instance

To create a new event subscription for Amazon RDS, you need to use the create_event_subscription() method of the Boto3 library with required parameters that specify the type of notifications you need to receive. You can create event subscriptions for AWS RDS to receive notifications from the DB, including availability, failure, replication, and backup operations. Event subscriptions use Amazon SNS Topics and subscriptions to send out notifications.

The following will create a subscription to any changes in DB instance availability.

import boto3

client = boto3.client('rds')
response = client.create_event_subscription(
    Enabled=True,
    EventCategories=[
        'availability',
    ],
    SnsTopicArn='arn:aws:sns:us-east-2:585584209241:mysql',
    SourceIds=[
        'database-instance-01',
    ],
    SourceType='db-instance',
    SubscriptionName='mysqleventsubscription',
)
print(response)

Here is the execution output.

Create event subscription for RDS instance using Boto3

List RDS events

To list RDS events related to your database instance or cluster, you need to use the describe_events() method of the Boto3 library.

import boto3

client = boto3.client('rds')
response = client.describe_events(
    Duration=10080,
    EventCategories=[
        'backup',
    ],
    SourceIdentifier='database-instance-01',
    SourceType='db-instance',
)
print(response)

Here is the execution output:

List RDS events using Boto3

Summary

This article covered how to use Python to programmatically interact with Amazon Relational Database Service (Amazon RDS) service and create, manage, tag, backup, and perform maintenance operations for AWS RDS DB instances.

How useful was this post?

Click on a star to rate it!

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Top rated Udemy Courses to improve you career

Subscribe to our updates

Like this article?

Share on facebook
Share on Facebook
Share on twitter
Share on Twitter
Share on linkedin
Share on Linkdin
Share on pinterest
Share on Pinterest

Want to be an author of another post?

We’re looking for skilled technical authors for our blog!

Leave a comment

If you’d like to ask a question about the code or piece of configuration, feel free to use https://codeshare.io/ or a similar tool as Facebook comments are breaking code formatting.