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|
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.
Let’s create a database instance running MySQL on Amazon RDS. We will use the AWS management console for this:
- Sign in to AWS Management Console at https://aws.amazon.com/console/
- Open Amazon RDS service from console https://console.aws.amazon.com/rds/
- Please make sure that you have choosen correct region from top right corner.
- Click on link database in left navigation panel
- Choose Create database, see the below screenshot for your referenece:
- 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:
- Choose MySQL in engines option, see screenshot for your reference:
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.
- 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.
We will choose Dev/Test template, if you are eligible for free teir then you can choose “Free tier” template.
- 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
- 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).
- Choose Storage type and allocate memory. Make sure that you are doing right selection otherwise choosing less storage may results into latency issues.
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.
- 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.
- 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.
Note: Once the database has been created, you cannot change the VPC.
- 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
- 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.
- Choose Create database. AWS will take sometime to create your database, In Meantime, you will see the below screen:
You can view the credentials by clicking on the button in the top right.
- Once the creation is done, you will see the status updated as available.
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>
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.