Moving MariaDB database to Amazon RDS

It's no secret. I love Amazon Web Services. AWS allows me to deploy my applications in the cloud with minimal effort and cost which equals maximum flexibility, benefit and uptime. Plus, AWS is very affordable. You can get started with a micro-T1 tier for free. Try and beat that.

There are lots of cloud providers.

I like Microsoft Azure, Digital Ocean, Linode and Python Anywhere too, but for my day-to-day work, it's AWS all day every day.

Today, I am moving my production MariaDB database to Amazon's RDS.

Amazon Relational Database Service (Amazon RDS) is a web service that makes it easy to set up, operate, and scale a relational database in the cloud. It provides cost-effective resizing capability while managing time-consuming database management tasks. This frees you up to focus on your software and run your business.

Amazon RDS provides most well-known database software giants, such as MySQL, Oracle, SQL Server and the PostgreSQL database engines.

This means that your project's applications, code and the tools you already use today with your existing databases architecture can be used with Amazon RDS. Amazon RDS automatically updates the database software and backs up your database. Your database backups are stored for a user-defined period. You can also enable point-in-time recovery. You applications benefit from the flexibility of being able to scale the computing resources and storage capacity associated with your relational database instance via a single API call.

Amazon RDS database engines offers two replication features:

  • Multi-AZ deployments and Read Replicas; these can be used alone or in conjunction to gain enhanced database availability and protect your latest database updates against unplanned outages.

  • Read Replicas can also help you scale beyond the capacity constraints of a single DB Instance for read-heavy database workloads.

Amazon's Relation Database Service is ideal for developers who need to:

  • Run existing or new applications, code, or tools that require a relational database.

  • Want native access to a MySQL, Oracle, SQL Server, or PostgreSQL database engines, but prefer to offload the infrastructure management and database administration to AWS.

  • Want to exploit the Multi-AZ and Read Replica features to achieve enhanced database availability and read scalability.

  • Like the flexibility of being able to scale their database compute and storage resources with an API call, and only pay for the infrastructure resources they actually consume.

I have been using Maria DB server with my applications for the past 2 years.

In this article, I am going to backup my existing production database and restore the database to Amazon RDS.

Step 1

Backup your production database.

$ mysqldump -u root -p --databases <database-name> -r database-name.sql

This will drop a database-name.sql backup file in your user's home path (for you Ubuntu fans).

Create a Amazon RDS Instance

From your Amazon AWS account dashboard, click Databases, RDS. Then Launch Database Instance.

In the first screen, select your database engine. For my project, I am using MariaDB server.

Amazon RDS Instance

Next, select where you plan to use this RDS instance. Dev, Staging/Testing or Production.

Amazon RDS Instance 2

Now, finalize your instance's named properties...

AWS RDS 3

Make sure to add a DB instance identifier and master username and passwords. Confirm the password.

Click Next Step. This will take you to the Network Settings page for your new RDS instance. You should always protect your WS instances by using s virtual private cloud.

AWS RDS 4

Launch instance. This next step will take a few minutes to complete. Once your RDS instance is ready, the first thing we need to do is connect to the instance via the AWS CLI.

myinstance.instanceID.us-east-1.rds.amazonaws.com:3306  

Now we are ready to copy our backup file to our new instance.

Start a new terminal and begin a new Secure Copy.

scp "your-aws-security.pem" path/to/database-name.sql ubuntu@ec2-52.x.x.x.us-east-1.rds.amazonaws.com ~/home/tech-walker/backups/database-name.sql  

You will see the dataabse backup file getting copied to your remote RDS instance.

Now that we have a backup of our database on the new instance, SSH to your new RDS instance and begin the restore.

$ mysqldump -u root -p database-name < database-name.sql
Wrap Up

Amazon Web Services and the AWS Relational Database Service is a great way to get your database to the cloud quickly, effectively and most importantly, affordably... Once you are working in Amazon's cloud, you will never switch back.

Craig Derington

Veteran full stack web dev focused on deploying high-performance, modern applications using Python, Go and Node; featuring industry leading frameworks, Django & Flask, and backends MySQL and MongoDB.

comments powered by Disqus