MySQL on Amazon RDS

Filed Under: AWS
Mysql On Amazon Rds

MySQL on Amazon RDS is the most popular and used database service of AWS Cloud. I will recommend to read our post on Amazon RDS – AWS Relational Database Service to get an understanding of AWS RDS.

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.

Major Version Minor Version
MySQL 5.5
  • MySQL 5.5.46
  • MySQL 5.5.53
  • MySQL 5.5.54
  • MySQL 5.5.57
  • MySQL 5.5.59
  • MySQL 5.5.61
MySQL 5.6
  • MySQL 5.6.34
  • MySQL 5.6.35
  • MySQL 5.6.37
  • MySQL 5.6.39
  • MySQL 5.6.40
  • MySQL 5.6.41
MySQL 5.7
  • MySQL 5.7.16
  • MySQL 5.7.17
  • MySQL 5.7.19
  • MySQL 5.7.21
  • MySQL 5.7.22
  • MySQL 5.7.23
  • MySQL 5.7.24
  • MySQL 5.7.25
MySQL 8.0
  • MySQL 8.0.11
  • MySQL 8.0.13
  • MySQL 8.0.15

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

    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:

    Easy Create Database

    Easy Create Database

  7. Choose MySQL in engines option, see screenshot for your reference:
    Storage Engine Mysql

    Storage Engine Mysql

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

    Mysql Edition And Version

    Mysql Edition And Version

    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.
    Database Templates

    Database Templates

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

  9. 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

    Database Settings

  10. Select DB Instnace Size, the selection is completely based on your application’s need. We will choose db.m4.large (2 vCPUs, 8 GiB RAM, EBS: 450Mbps).
    Db Instnace Size

    Db Instnace Size

  11. Choose Storage type and allocate memory. Make sure that you are doing right selection otherwise choosing less storage may results into latency issues.
    Storage

    Storage

    Amazon RDS is designed to auto-scale, you can enable auto-scaling of the database based on the storage capacity used for your database. In the screenshot above, the database will scale from a minimum of 21 GiB to maximum 1000 GiB.

    Note: You can always mention the maximum storage threshold in auto-scaling.

  12. Availability & durability: This is default applicable when you will choose production template while creating instance. You can choose to create a standby instance. This will create a Replica in Different Zone to have Amazon RDS maintain a synchronous standby replica in a different Availability Zone than the DB instance.
    Availability And Durability

    Availability And Durability

  13. 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.
    Connectivity And Vpc

    Connectivity And Vpc

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

  14. 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
  15. 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.

    Estimated Monthly Cost

    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

    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

    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.

deepak@localhost> 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:

Monitoring Matrices

Monitoring Matrices

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:

Stop Database

Stop Database

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

Delete Database

Delete Database

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

Delete Database Options

Delete Database Options

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

Delete Your Database

Delete Your Database

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

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages