Exporting Amazon RDS for MySQL and MariaDB Databases to Amazon S3 Using a Custom API

More Info

A frequent task for database administrators (DBAs) involves backing up production databases and transferring them to lower environments such as development, quality assurance, and staging. As organizations migrate to AWS Cloud to leverage managed database services like Amazon Relational Database Service (Amazon RDS) for MySQL, Amazon RDS for MariaDB, and Amazon Aurora MySQL-Compatible Edition, they often seek to automate these administrative responsibilities.

While collaborating with a client on a data center migration, we identified a specific set of requirements for their database backup strategy. This client was moving from Microsoft SQL Server databases to Amazon Aurora MySQL, Amazon RDS for MySQL, and RDS for MariaDB as part of their transition from on-premises to the cloud, with a primary focus on minimizing disruptions to their operations team during this migration.

The operations teams expressed a need to back up individual databases from production environments for restoration in lower environments. As newcomers to cloud technology, they aimed to maintain a backup process that mirrored their existing on-premises systems.

After carefully assessing their needs, we established the following requirements:

• Retain existing Aurora MySQL endpoint names when restoring backups to eliminate the need for application redeployments during restore activities.
• Ensure the capability to refresh lower environments, including development and testing, using backups from production databases.
• Avoid the necessity of employing infrastructure as code (IaC) to restore snapshots from different AWS accounts.
• Implement a minimal learning curve for the operations team, who were still familiarizing themselves with Aurora MySQL.
• Utilize an API-friendly approach to minimize reliance on Amazon Elastic Compute Cloud (Amazon EC2) or similar services for initiating backup requests.
• Leverage Amazon Simple Storage Service (Amazon S3) and pre-signed URLs to facilitate backup transfers by the operations team.

Solution Overview

This post outlines how a DBA or any authorized user can request MySQL and MariaDB backups using a custom API. The deployment process is simplified through Infrastructure as Code (IaC) using the AWS Cloud Development Kit (AWS CDK).

Amazon API Gateway provides an interface for initiating the backup process, while Amazon Elastic Container Service (Amazon ECS) executes the backup. Backups are stored in Amazon S3, and Amazon Simple Notification Service (Amazon SNS) is utilized to notify users upon completion.

The following diagram illustrates the high-level user interaction with the solution and the functioning of its components.

The process consists of the following steps:

1. The user invokes the API Gateway endpoint using bash or Python, providing the hostname and database to back up.
2. API Gateway receives the payload containing the hostname and database name, forwarding it to an AWS Lambda function.
3. The Lambda function extracts this data and issues a command to run an Amazon ECS task, passing the values as environment variables.
4. The ECS task is initiated and accesses the environment variables to obtain the hostname and database name.
5. It utilizes AWS Secrets Manager to fetch the database credentials associated with the specified hostname. The TCP port for the given MySQL or MariaDB instance is retrieved via the Amazon RDS API.
6. Using mysqldump, the task connects to the MySQL or MariaDB instance and begins the backup process. If operating in a MariaDB environment, consider switching from mysqldump to mariadb-dump.
7. During execution, the backup is created, compressed, and streamed directly to Amazon S3. A new pre-signed URL is generated for the file, which is uploaded into the Amazon S3 Standard-Infrequent Access storage class, recommended for backup storage.
8. The user email specified in the SNS topic during the CDK deployment receives a notification.

Prerequisites

To follow along, ensure you have the following:

• An active AWS account with a VPC and at least one of the following database platforms:
  • Amazon Aurora MySQL-Compatible Edition
  • Amazon RDS for MySQL
  • Amazon RDS for MariaDB
• The AWS Command Line Interface (AWS CLI) installed and configured.
• AWS CDK v2 set up on your local machine. For more details, see this excellent resource on getting started with AWS CDK. Since this project uses TypeScript, ensure that is also installed.
• Docker on your machine to build the container initially.
• The GitHub repository downloaded to your local machine, which includes a template for creating an AWS Identity and Access Management (IAM) role.

Limitations and Items Out of Scope

This post does not cover a demonstration of the database restoration process. Additionally, this solution is most effective for databases within the same AWS Region as the supporting backup infrastructure. If your databases span multiple Regions, you can deploy the backup solution independently for each.

Create Database Credentials

To proceed, a database user with permissions to execute schema dumps is required. Prior to deploying the solution, create this user in your environments. To create a new user in your selected database engine, run the following script:

-- Change UserNameToBeDefined and PasswordToBeDefined to your preferred values
CREATE USER 'UserNameToBeDefined'@'%' IDENTIFIED BY 'PasswordToBeDefined';
GRANT SELECT, SHOW DATABASES, LOCK TABLES, EVENT ON *.* TO 'UserNameToBeDefined'@'%';

Store Database Credentials in Secrets Manager

Once the database credentials are created, they must be stored within your AWS account. For this purpose, we use Secrets Manager, which aids in managing and securely retrieving credentials. The ECS task will access these secrets during runtime to perform the database backups. To create the secret, follow these steps:

1. In the Secrets Manager console, click on Secrets in the navigation pane.
2. Select Store a new secret.
3. For Secret type, input the RDS database credentials.
4. For User name, enter NameOfUser.
5. For Password, input PasswordOfUser.
6. For Database, select the DB instance associated with the credentials.
7. Click Next.
8. For Secret name, input backup/EndpointIdentifierName/user. For Aurora MySQL instances, the EndpointIdentifierName should refer to the writer or reader endpoint.
9. Click Next, then select Next again.
10. Finally, select Store.

Your secret will now appear on the Secrets page.

Deploy the Solution in the AWS Account

After completing the prerequisites and configuring the IAM user and AWS CDK, it’s time to deploy the solution to your AWS account.

1. Download the code from the GitHub repository and extract the contents of the .zip file.
2. Navigate to the project folder you downloaded and open a terminal session there.

In the terminal, ensure you are connected to the right AWS account. For more insights on AWS solutions, check out this blog post that discusses similar topics. You can also find authoritative insights on these subjects at this source.