I’ve recently started to explore MySQL as a potential backend option for my custom web application pet project. Earlier, I’ve already wrote an article about installing AMP stack on Ubuntu Server, and as a logical next step I decided to explore PaaS option which allows you to get MySQL as a service from Amazon as potentially better option for large scale projects or for those who focusing on building “serverless” architectures on AWS platform. In this little article I will cover basics of Amazon RDS MySQL offering and explain how to create your first MySQL instance and connect to it.

Amazon offers their Relational Database Service (aka Amazon RDS) which provides you with partially managed relational database management systems (RDBMS) instances. At initial stages Amazon RDS was mainly intended for small and medium workloads (with data size limit of up to 3 TB), but with introduction of Amazon Aurora in 2014 larger workloads can be supported too.

Amazon RDS is a Platform as a Service (PAAS), or, if you want to be more specific/explicit, you may call it Database as a Service (DAAS), which allows you to avoid installing and managing RDBMS on Amazon EC2 instance (or any other type of server/VM) managed by you and instead get the following from Amazon:

• Partially managed relational database service instance ready to use in no time
• Reduced need in DBA resources – most of the RDBMS administration tasks are hidden away from you, so that you can focus on actual development and work with your data
• Routine tasks like back up and others greatly simplified and can be scheduled
• Ability to guarantee performance (e.g. set Provisioned IOPS volumes)
• Option to use different popular RDBMSs / database engines (MySQL, Oracle, Microsoft SQL, PostgreSQL, MariaDB, Aurora)

Let’s focus om Amazon RDS for MySQL specifically. MySQL was first (and at initial stage only one) database engine available when Amazon RDS was released in October 2009, so by now it should be well tested and very stable option 😊 Along with PostgreSQL, MySQL offering falls into Open Source bucket of Amazon RDS as those two RDBSs do not require commercial licenses (= no licensing costs).

MySQL itself is easily one of the world’s most popular open source relational database engines and in case of Amazon RDS you can provision MySQL Community Edition of different versions in no time and scale it as necessary. This database engine powers lots of high-load and high-profile web sites (check logos under humble “Some of our customers” at mysql.com), and if we speak specifically about Amazon RDS MySQL it powers such web sites as Airbnb, Flipboard to name just a few.

In addition to rapid provisioning and easy scaling Amazon RDS MySQL allows you to use all standard MySQL tools (MySQL Workbench etc.) while hiding from you underlying OS instance and removing management and support burden associated with it. You connect to, and work with database instance itself. Amazon RDS also hugely alleviates advanced database administration task associated with setting up redundancy or resource scaling. The only downside here is that this service has its price 😊 But, honestly, prices are quite competitive, and if you do some math taking into account hardware costs along with associated operational expenditures (don’t forget taking into account risks and complexities) you may find them quite interesting (especially as you start doing something resource and availability sensitive).

Leaving theory and introductions aside, let’s switch to practical aspects, and see how we can create and connect to Amazon RDS MySQL database.

Open AWS Management Console to in your browser to start your instance creation process. But, before creating our first MySQL instance, let’s take care about security group which will enable external connection to this instance (i.e. will allow to connect to this instance outside of AWS cloud). To do this, navigate to EC2 Dashboard and click on Security Groups. From there we can click on Create Security Group button which opens Create Security Group dialogue:

There we just specify Security group name and click on Add Rule to create new inbound rule. From the rule type we select MySQL/Aurora predefined type and specify source. I selected Anywhere as I’m doing this just for demo/test purposes, but normally you will be limiting this access to specific IP addresses (let’s say of your external web frontend server or your developers’ machines), once we specified all settings, click Create to create Security Group:

With security group in place, we now can start actual MySQL instance creation process. Click on Services in the upper part of AWS console and next click on RDS under Database (alternatively you can type in RDS into Find Services search box):

This will open Amazon RDS console for you (you will notice that Amazon tries to promote its Aurora database engine putting it first and forefront almost everywhere, and to make this offer more compelling they always reiterate that it is MySQL-compatible and more 😊). Here we just need to click on Create Database (which you can do in few places):

On the Create Database page you can select MySQL engine type and configure your RDS instance:

Note that if you enable Easy create toggle, majority of options will be hidden, and you will only need to choose between 3 DB instance size options and specify DB instance identifier along with Master username:

Let’s create our instance reviewing all available settings without using Easy create option. First of all, after selecting MySQL as engine type we can select specific MySQL version (starting from 5.5.46, which is selected by default, and all the way up to newest versions). I will select latest available version at this moment – 8.0.15:

Under Known Issues/Limitations link below version selection drop down you may find useful information to look through, especially if you plan to use this instance for production workloads and/or just start working with this service.

Our next step is to select template, and options here are Production, Dev/Test and Free Tier. Below you can see comparison table highlighting template/tier differences:

Let’s select Free tier as the most appropriate option when our goal is just to familiarize ourselves with the RDS MySQL service:

Next, we need to set DB instance identifier (must be unique for all instances owned by the same AWS account), customize (if we want to) master user name and decide whether we want to go with autogenerated password or specify it ourselves (I opted out for typing in my own password):

As Free Tier template has been selected, we have nothing to select under DB instance size as it can only be db.t2.micro Burstable class:

In terms of storage, Free tier template allows us to use only General Purpose (SSD) type and I will leave allocated size on its default setting of 20 GiB:

Again, as Free Tier template option was selected, we cannot enable Multi-AZ deployment under Availability & durability, it is greyed out, but this is not something we need for now:

In case you are curious, Multi-AZ deployment allows you to create and maintain a synchronous standby replica in a different Availability Zone with automatic failover in case of planned or unplanned outage of the primary.

For Connectivity options we can leave everything on defaults, except for enabling Publicly accessible option and selecting existing VPC security group we have created earlier under Additional connectivity configuration > VPC security group:

Under Additional configuration specify Initial database name to let Amazon RDS create new database along with your new MySQL instance:

You can leave remaining settings on their defaults and click on Create database. As you can see, even though we have selected Free Tier there is some costs for storage and DB instance (so it is free license wise, but compute and storage resources are still billed):

Once you clicked on Create database, wait for creation and status checks completion. You will see that initial status will be Creating with no region assigned, next you will see assigned instance region and after some minutes instance status will be changed to Backing-up (initial backup gets created immediately), and finally status will change to Available:

Once instance is created, we have handy link which says View credential details in green bar which appears in the upper part of the console. This link allows you to see database connection details (master username and password and endpoint name).

We can now use endpoint name to connect to our RDS MySQL instance using mysql command line tool or MySQL Workbench.

To connect using MySQL Workbench create new connection and specify your endpoint name (to find it you can also click on the database name in AWS console and look at Connectivity & Security section), leave default port 3306 without change and type in your master user name and password.

If all settings were specified correctly you will be able to connect to your RDS MySQL DB from the external client over the internet (i.e. outside of AWS cloud).

In case you are experiencing troubles to connect to the instance outside of AWS cloud, make sure you assigned to target instance public access security group and enabled public accessibility. To check this, navigate to Amazon RDS console > Databases (it’s a bit confusing but Amazon uses term “database” for instances) and in Databases list click on your instance to view all associated details and settings. You can find information you need under Connectivity & security as shown below:

Once connected, you can check instance MySQL version and see your initial database listed under schemas:

You can also connect to your instance using mysql CLI interface as shown below:

One important thing to note is that you can stop your RDS MySQL instance, albeit only for 7 days period, after which it will be started automatically:

This is not full control over instance state, but last time I checked Azure SQL there was no possibility to stop instance at all. Stop DB instance option allows you to save at least some expenses by means of keeping unused instance stopped. Storage costs still will be charged, but EC2 resources will be kept reserved for you without incurring costs – at least for 7 days until instance will auto start again.

As you can see from this short description, setting up and managing MySQL instance with AWS RDS is extremely easy and convenient process. By way of conclusion, I would say that not everyone may need this service for small scale development project, but it really starts to shine as you scale up your project – HA and other advanced administrative tasks become too costly to maintain and manage yourself and AWS RDS takes this pain away. Personally, for my tiny project which will require Ubuntu server to run front end web app I will start with EC2 instance running both MySQL and web server and when project grow big enough, I can easily switch to RDS MySQL instance when needed. For now, managing my own MySQL instance within EC2 Ubuntu VM will be probably a bit cheaper and more convenient as I can fully stop EC2 instance for any amount of time and switch it on whenever I need it, instead of worrying about switching off EC2 instance and RDS instance all the time and managing two entities. I hope this introductory walkthrough was interesting and informative for you, and in case you have any questions or comments feel free to leave them in the comments section below.

Return to all posts

Filed under:
Services, Software by Mikhail Rodionov