Setup your self-hosted MySQL on AWS EC2 Ubuntu

Introduction

AWS has a MySQL compatible database but it's expensive and sometimes the cost doesn't justify the convenience we get from having such a service for personal projects or projects that don't make money.

The alternative solution is to have an EC2 instance that runs MySQL instance which is going to be way cheaper but needs your maintenance. You'll need to do backups and other chores such scaling all the manual tasks.

Prerequisites

You need to either have secret .pem file to ssh to the EC2. Also make sure the port 3306 is enabled in your EC2 Security group rules. We assume your EC2 is running Ubuntu Linux.

Step 1: Install packages

Before we do anything I'm going to install tmux because it's great for running long-running process without canceling the process. For example, you are in the airport want to initiate some task that takes 15 minutes but the gate closes in 10 minutes so you need to start the process and close your terminal and move away. If you're not convinced, you can skip the following commands.

# update everytime before installing anything
sudo apt update

sudo apt install tmux # debian based linux

# start tmux
tmux

Install mysql-server package

sudo apt install mysql-server

Step 2: Register services

Add mysql to the background services so whenever the server restarts it will start too.

# start the service
sudo systemctl start mysql.service

Warning: you might get error Failed with result 'oom-kill' if that happens, you might need to upgrade to an EC2 instance with more RAM. I couldn't make it work on t4g.nano instance because it has less than 1GB RAM. To upgrade the EC2 instance, you'll need to stop it first by doing the following, (1) click in the top right Instance state dropdown > click Stop instance. (2) Click the next dropdown Actions > Instance Settings > Change instance type.

Step 3: Configure MySQL

When installation is complete, the mysql instance is not secure, so we need to make few changes to make it more secure.

# let's connect to it fisrt
sudo mysql

Set password for root user.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your-password'
mysql> exit;

So, now we have set the password for the root user of our database, we need to run a built-in script for completing the steps to securing the database.

The mysql_secure_installation script takes you through steps by prompting you available security options.

sudo mysql_secure_installation

Make sure you choose your security options wisely.

Create User & Database

To access database it's good idea to use non-root user, which is why I prefer to use different user(s) for different databases/schemas. So, let's create a user db_user with a password (make sure you replace it with your password)

CREATE USER `db_user`@`%` IDENTIFIED WITH caching_sha2_password BY 'your-password';

CREATE DATABASE `db_name`;

GRANT ALL PRIVILEGES ON `db_name`.* TO `db_user`@`%`;

The % part in db_user@% means we can connect to this database from any machine that can connect to our EC2 instance.

Step 4: Connect from other EC2

You need to get your EC2 domain or IP to connect to the database.

mysql -h YOUR-EC2-IP -P 3306 -u db_user -p

Then you need to type your password after the prompt:

Enter password:

If you are using MySQL Workbench to access your databases, you can connect through SSH.

1. Click + icon near MySQL Connections

2. Click the Connection Method dropdown

3. Select the Standard TCP/IP over SSH option

4. Fill EC2 IP as the the SSH Hostname

5. Fill EC2 SSH username

6. Click the [...] button on the right of SSH Key File input, then Select .pem file we used to ssh to the EC2

7. The rest are Database Hostname, Username and password. These are the same as if you were connecting locally.

Conclusion

This solution is great for hosting your personal databases or projects that don't make money for you. If you make enough money from your project and the price is not too much for your revenue, I would suggest you use managed database server such as RDS or similar offerings from other cloud providers.