MySQL on Amazon RDS With Examples [Latest]

MySQL is one of the most popular open-source databases in the world. MySQL on RDS offers the rich features of the MySQL community edition with the flexibility to easily scale compute resources or storage capacity for your database.

Supported Versions of MySQL on AWS RDS

Amazon RDS supports several versions of MySQL. Following major versions are supported by AWS:

• MySQL 5.5
• MySQL 5.6
• MySQL 5.7
• MySQL 8.0

MySQL maintains versions of database engines in the form of X.Y.Z. In general, X.Y denotes the major version of MySQL which is being maintained by AWS. For example, MySQL 5.6 and 5.7 are major versions whereas 5.6.40 and 5.6.41 are the minor versions.

Here is the list of all minor and major MySQL versions supported by Amazon RDS.

Please check supported and unsupported features and plugins of MySQL on AWS RDS before getting started.

Storage Engines of MySQL on Amazon RDS

MySQL supports a number of database storage engines, you can find the complete list here at MySQL official documentation https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html.

Amazon RDS doesn’t support all MySQL database storage engines as not all of them are optimized for data durability and recovery. Amazon RDS fully supports InnoDB storage engines for MySQL RDS instances. It also supports MyISAM database storage engine but not all features are supported fully. For example, the MyISAM storage engine does not support reliable recovery and may result in data loss and/or corruption of data when MySQL is restarted after recovery for user-created schemas. These issues prevent Point-In-Time restore or snapshot restore from working as intended.

DB instance on RDS running MySQL

We took an example of creating a database instance on Amazon RDS running MySQL in Amazon RDS – AWS Relational Database Service. Here, we will explore each option in detail.

Create Instance

Let’s create a database instance running MySQL on Amazon RDS. We will use the AWS management console for this:

1. Sign in to AWS Management Console at https://aws.amazon.com/console/
2. Open Amazon RDS service from console https://console.aws.amazon.com/rds/
3. Please make sure that you have choosen correct region from top right corner.
4. Click on link database in left navigation panel
5. Choose Create database, see the below screenshot for your referenece:

    

   

   Create Database

6. You will see an option “Easy create”, turn it off to understand most of the available options. Please see the screenshot below for your reference:
7. Choose MySQL in engines option, see screenshot for your reference:

    

   

   Storage Engine Mysql

   Choose specific version for MySQL as per your requirements, We will choose 5.7.22 in this tutorial:

   Note: Never forgot to read known issues and limitation of the selected edition and version of MySQL. It’s important to know these things as you may need any of them in your production instance.

8. In Templates, choose that fits well in your use case. If you will select production then many options like Multi-AZ failover option and Provisioned IOPS storage option will default come selected in later stages.
9. 

    

   Database Templates

   We will choose Dev/Test template, if you are eligible for free teir then you can choose “Free tier” template.

10. Settings: Give a unique DB instnace identifier. In the second section open credentials section.
    • Do not select “Auto generate a password” as we would like to give our own password.
    • Enter master username in case you want different user name than generated.
    • Choose a Password and Confirm Password
    • 

     

    Database Settings

11. Connectivity: If you have created VPC then you can choose one from the dropdown OR AWS will create a VPC for you. Please learn more about VPC – Virtual Private Cloud if you are a beginner.
12. 

     

    Connectivity And Vpc

    Note: Once the database has been created, you cannot change the VPC.

13. Additional COnfigurations: There are several other configurations is available, you can choose as per your use case. For example, Database options, encryption enabled, backup enabled, backtrack disabled, Performance Insights enabled, Enhanced Monitoring enabled, maintenance, CloudWatch Logs, delete protection disabled
14. Now, it’s time to see the estimated cost of the setup that we are going to do. If you think it’s too higher than expected then you can change the storage and DB class. You can also calculate the cost from AWS Cost calculator before creating the instance.
15. 

     

    Estimated Monthly Cost

16. Choose Create database. AWS will take sometime to create your database, In Meantime, you will see the below screen:

    

    Creating Database

    You can view the credentials by clicking on the button in the top right.

17. Once the creation is done, you will see the status updated as available.

    

    Database Created

Connect to DB Instance

We have learned how to connect to the database from the console in our post Amazon RDS – AWS Relational Database Service. I am going to use the same way here as well:

Please replace the endpoint, username, and password from your instance details. You can get these details from connectivity & Security. I cannot add screen here for security reasons.

[email protected]> mysql -h endpoint -P 3306 -uusername  -ppassword

You will see the below details upon successful connection.

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 200
Server version: 5.7.22-log MySQL Community Server (GPL)
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
mysql>

Monitoring

There are several CloudWatch matrices available to monitor various aspect of your database instance. Few important matrices are given below:

You can also check Read and Write IOPS, Read and Write throughput, etc.

Delete your Database

You can delete your database if you are no longer using it but make sure that you have taken a snapshot or backup of the database before deleting it.

Make sure that your database instance is not running, otherwise, AWS will not allow you to delete the database instance. For this, go to actions and click on stop and you will see the below screen:

Now, Go to the database, select it and choose delete from actions.

Click on delete and it will ask you to create final snapshot, retain automated backups, etc. Please see the screenshot below for your reference:

AWS will take some time to delete your instance, you will see the below screen in meantime.

In our next post, we will discuss backing up ad restoring database instance on AWS RDS.