What is Amazon RDS?
Amazon Web Service offers a relational database service known as RDS. RDS allows you to use MySQL, PostgreSQL, MariaDB, Oracle, Microsoft SQL server and Amazon Aurora. RDS offers backups, recovery, software updates / patching and automatic failure detection.
Why choose Amazon RDS over a self-hosted solution?
Configuring and managing a database or cluster of databases can be a large pain point of an application / service. When you manage your own database, there are many things you need to consider: hardware, backups / recovery, security, software updates, performance, availability and more. Amazon RDS offers a solution to many of these points. You can easily scale your database down or up depending on traffic, storage requirements, or performance needs. RDS comes with security options but also allows you to control security through AWS IAM to handle users and permissions.
Disadvantages of Amazon RDS
RDS does not provide shell access to the RDS instances. RDS also restricts access to some system procedures and tables that need advanced privileges.
Getting started with Amazon RDS
We are going to set up a simple RDS instance which will be part of the AWS free tier. To get started, you need to log into the AWS dashboard and click on the ‘ Services’ drop down menu. Find the ‘ Database’ section and click on the ‘ RDS’ item:
You will now be within the RDS dashboard. Click on the ‘ Instances’ link. This is the instances dashboard which will allow you to see any running RDS instances. They should be empty, so let’ s click on the “Launch DB Instance” button to get started.
When creating our instance, the first configuration option is choosing a database engine. To learn more about each engine and the support AWS offers (such as maximum memory), click on the tab. For this guide, we will be selecting MySQL.
dashboard which will allow you to see any running RDS instances. They should be empty, so let’ s click on the “Launch DB Instance” button to get started. When creating our instance, the first configuration option is choosing a database engine. To learn more about each engine and the support AWS offers (such as maximum memory), click on the tab. For this guide, we will be selecting MySQL.
We can see that it’ s ‘ free tier eligible’ and supports database sizes up to 6 TB. Click on the blue ‘ Select’ button to continue. Next, we will be asked to select our environment type – production or development.
To help keep this guide straight forward, we will be using the ‘ Dev/Test’ environment type as this also part of the AWS free tier.
We now need to specify the database details.
We’ ll be creating a free tier RDS instance so make sure to tick the ‘ Only show options that are eligible for RDS Free Tier’ option. The form will then update to match that option.
Unless your application has any specific requirements, such as the MySQL DB engine version or the hardware (DB Instance class), then you can change them here before continuing.
Here is where we will set up our database credentials. We need to set a ‘ DB Instance Identifier’ to easily identify this instance, a master username and master password.
Make a note of your master username and password as you will require this later.
Please note: to keep this guide simple, we will not look into advanced security. We will only be using the bare minimum credentials to get us started and connected to our RDS instance. I recommend that you take some time to configure your security settings. You can learn more here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/UsingWithRDS.html
For the network and security, this will require you to choose a few things that will be different to my setup. For this case, I will be using the Default VPC, default subnet group, make the instance publicly accessible (this is required for us to access the instance from our home machine), and select a VPC security group. For this instance, I will be creating a new security group that will add a rule to allow our IP address access into the instance.
Our next step is to configure our database. We need to select a database name, a database port and to keep things straightforward. We will use the default parameter and option group. We do not need to enable the ‘ copy tags to snapshots’ option or the encryption option unless your application requires it. Make a note of your database name as we will need it later.
Backing up with RDS
RDS instances currently only support automatic backups if you are using the InnoDB storage engine. In this section, you can select the ‘ backup retention period’ , which is how long AWS will save your instances backups. This will depend on your application’ s requirements, but I will be setting it to 7 days for now. The backup window is used by AWS to help schedule the instances backup. A this may take up more resources, it can be worth setting this to a window when your application has the least users.
Within the monitoring section, there is a single option – ‘ enable enhanced monitoring’ . EEM allows you to get access to over 50 metrics about your RDS instance, including CPU, memory, and file system. It works on all instances types except t1.micro and m1.small and requires MySQL version 5.6+.
Within the monitoring section, we can control whether our RDS instances gets upgraded by minor versions automatically or not. This can be good for security as keeping MySQL update will patch any known exploits or security holes. The maintenance window is used by AWS to install any patches or handle any maintenance issues.
Once configured, we can launch the RDS instance. Press the blue “Launch DB Instance” to continue. AWS will now not begin launching the instance:
Click on the “Instances” item on the left side menu and our new instance should appear in the list (with the status of ‘ creating’ ):
This can take 5 – 10 minutes to configure and back up but the status will change once complete and you will see the CPU usage and current activity:
When you connect later in this guide, the current activity will update in real time:
We now need to get the hostname and port number to continue onto the next section. You can get this information by simply clicking on the downwards arrow (˅) next to your instance. You should then see an “Endpoint” value which is your instance’s hostname, an example would be:
Let’ s now install October CMS and connect to our RDS instance.
Laravel Application – October CMS
Our Laravel application of choice will be October CMS. October is a content management
system powered by Laravel and uses MySQL as the database. This will be a good test of Amazon’ s RDS service since October uses many tables and schemas to handle the different aspects of the system, such as admin management, page management, and system customization.
How to install October CMS
Before you get started, you must have the following installed on your system: a minimum version of PHP 5.5.9, the PHP mbstring extension, ext-dom, ext-curl, mysql/pdo, unzip, and have access to composer.
To make development and deployments easier, we will use composer (todo – add link) to handle our dependencies for October CMS and Laravel. To create the project scaffolding, we can run the following command:
composer create-project october/october rds-demo
We then need to update composer and install any project dependencies. Make sure you have moved into the folder by running the following command:
This make take a few minutes depending on your internet connection. Once composer has been installed and updated, we will have access to a new set of artisan commands. We now need to finish off the October CMS and update it:
php artisan october:update
October CMS is now installed and up to date. You can run the following command to get it up and running:
php artisan serve
Artisan will now start serving the software on port 8000. Visit this address in your browser and you should see the start / demo page:
We now need to finish off the installation by configuring the database. Run the following command:
php artisan october:install
Select your database type. Let’ s select 0 (which should be MySQL). We now need your credentials from earlier – database name, master username, master password and hostname.
Set the host as your instance’ s host address and continue by pressing enter. We can now choose the port for MySQL, if you did not change this when setting up your instance then we can use the default port, which is 3306.
The next steps:
- Database name
- MySQL login
- MySQL password
Now we set up a user for October CMS:
- First Name
- Last Name
- Email Address
- Admin Login
- Admin Password
- Information correct?
- Application URL
- Configure advanced options?
We now need to run our database migrations that will allow October CMS to add in tables and data into our RDS database instance. You can do this by running the following command:
php artisan october:up
If this command was successful, then your RDS instance will have 24 tables available. Let’ s log in to the instance and check for them.
Logging into RDS via the command line
Since we have set up a MySQL powered database on RDS, we can interact with it using the mysql command line tool in Linux (you must have MySQL installed to access this). You can run the following command to get connected:
mysql -h [HOST] -u [USERNAME] -p
(We need to use the username that we added when we configured the RDS instance. The host will be available once the RDS has fully complete setting up and is live. You can find the host by accessing the RDS dashboard and going into the instances dashboard.)
Once you press enter, you will be prompted to enter your password. Type it, then press enter again. If your credentials are correct, then you should now be within the MySQL monitor.
Interacting with RDS data
To list the databases available, run the following command:
Here, we can see the ‘ octobercms_dev_db’ database that I set up during the RDS configuration:
Let’ s access that table to view the data. Run the following commands:
You should now see the tables that October CMS added in. Here is a snippet:
Let’ s view backend / admin users. We can do this by running the following command:
SELECT * FROM backend_users;
Depending on your initial configuration (when running the php artisan october:install command), you should see 1 user within the ‘ backend_users’ table. This is the default admin added in by October CMS.
Let’ s log in to October CMS using the default admin account. Visit the following url to access the admin backend:
Depending on your setup, this may be another IP address a different port, such as localhost:8000.
Click on the ‘ settings’ menu from the top, and then click the ‘ Administrators’ item from the left hand side menu. At the top of the page, click the ‘ New Administrator’ button and fill out the form. When you are happy with the settings, click the ‘ create’ button to add a new administrator. If you go back into the MySQL cli tool and run the same command again as above, you should now see two results returned: the admin account and the new one that you just added.