This is the first of a series on how to take advantage of Amazon RDS databases as a .NET developer. Let’s start with one of the oldest open-source relational databases out there, MySQL. Originally released in 1995, MySQL has gone through a series of “owners” since then, with it currently being primarily developed by Oracle. As mentioned, MySQL is free and open-sourced under the terms of the GNU General Public License (GPL). That AWS does not have to pay any licensing fee is one of the primary reasons that the cost for MySQL on Amazon RDS is the lowest in the chart; you are only paying for hardware and management rather than hardware, licensing, and management.
MySQL may not be as fully featured or high-powered as some of the commercial systems such as SQL Server and Oracle, however, that does not mean that it is not of use to a .NET developer. One of the reasons that MySQL became popular was because of its relative simplicity and ease of use, so if all you are looking for is the ability to easily persist data in a relational database then more than likely MySQL will support your need at a fraction of the cost of SQL Server.
MySQL and .NET
Before we dig into the RDS support for MySQL, let us first briefly go over using MySQL during .NET development. The primary use case when thinking about .NET and database products is the support for Entity Framework, .NET’s default object-relational mapping (ORM) system. If there is support for that database, then using that database in your .NET application will come down to the features of the database rather than its interaction with .NET. With that in mind, let’s look at what you need to do to use MySQL in your application.
The first thing you need to do is to include the necessary NuGet package, MySql.EntityFrameworkCore. Once you have the package, next is configuring your application to use MySQL. You do this by calling the UseMySQL method when overriding the OnConfiguring method in the context class as shown below:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseMySQL("connection string here");
}
A connection string for MySQL has four required fields:
- server – server with which to connect
- uid – user name
- pwd - password
- database – database with which to connect
From here on out, it is just like working with Entity Framework and SQL Server. Kinda anti-climactic, isn't it? Let’s now go create a MySQL database.
Setting up a MySQL Database on Amazon RDS
Now that we know how to setup our .NET application to access MySQL, let’s go look at setting up MySQL. Log into the console, go to RDS, select Create database. On the Create database screen, select Standard create and then MySQL. Doing this will show you that there is only one Edition that you can select, MySQL Community. You then have a lot of different release versions that you can select from, however, the NuGet packages that we used in our earlier example require a reasonably modern version of MySQL, so unless you have any specific reason to use an older version you should always use the default, most updated version.
Once you have defined the version of MySQL that you will use, your next option is to select the Template that you would like to use. You have three different templates to choose from:
- Production – defaults are set to support high availability and fast, consistent performance.
- Dev/Test – defaults are set in the middle of the range.
- Free tier – defaults are set to the minimal, free version.
We are going to select the Free tier version to limit our costs for this walkthrough! This will create many of the default system values that you will see during the server configuration, more on these later.
The next section is Settings. Here you will create the DB instance identifier and the Master username and Master password, the login credentials as shown in Figure 1. Note that the DB instance identifier needs to be unique across all the account’s DB instances in this region, not just MySQL database instances. We used “prodotnetonaws” as both the instance identifier and the Master username. If you choose to Auto generate a password, you will get an opportunity to access that password immediately after the database is created.
Figure 1. Naming the DM instance and creating the master user
Scrolling down to the next section, DB instance class, will show that the instance class being used is the db.t2.micro (or comparable, depending upon when you are reading this) which is the free tier-compatible instance type. The next section down the page, Storage, is also filled out with the free version of storage, defaulting to 20 GiB of Allocated storage. Do not change either of these values to stay within the “free” level.
There are four additional sections. The first of these is Availability & durability, where you can create a replica in a different availability zone. Amazon RDS will automatically fail over to the standby in the case of a planned or unplanned outage of the primary. If you selected the “Free” template, then this whole section will be greyed out and you will not be able to create a Multi-AZ deployment. The second section is Connectivity. This is where you assign your Virtual private cloud (VPC) and Subnet group, determine whether your RDS instance has Public access, and assign a VPC security group. You can also select an Availability zone if desired. We left all these values at their default.
The third section is Database authentication. You have three options in this section, with the first being Password authentication (the default value) where you manage your database user credentials through MySQL’s native password authentication features. The second option in this section is Password and IAM database authentication where you use both MySQL’s password authentication features and IAM users and roles, and the last is Password and Kerberos authentication, where you use both MySQL’s password authentication features and an AWS Managed Microsoft Active Directory (AD) created with AWS Directory Service.
The last section when creating an RDS database is Additional configuration. This allows you to add any Database options, configure Backup, add Monitoring, and configure Logging, Maintenance, and to turn on Deletion protection. When deletion protection is enabled, you are not able to delete the database without first editing the database to turn off that setting. Select the Create database button when completed. This will bring you back to the Databases screen where you will likely see a notice that the database is being created as shown in Figure 2.
Figure 2. Notice that an RDS database is being created
If you had selected to auto generate a password, the button shown in Figure 2 also shows the View credential details button that you would need to click to see the generated value. Once the database is Available, you can interact with it like any other database, using endpoint and port values that are shown in the Connectivity & security tab in the database details, as shown in Figure 3, for the connection string in your .NET application.
Figure 3. MySQL database details screen showing endpoint and port
MySQL is the most used open-source relational database in the world. However, as mentioned earlier, Oracle took control of the project in 2010 and this created some angst amongst MySQL users. This led to a forking of the source code by some of the original MySQL developers and a creation of a new open-source relational database based upon the MySQL code, MariaDB. We will look at that next.
Top comments (0)