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

```bash
# update everytime before installing anything
sudo apt update

sudo apt install tmux # debian based linux

# start tmux
tmux
```

Install mysql-server package

```bash
sudo apt install mysql-server
```

## Step 2: Register services

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

```bash
# 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.

```bash
# let's connect to it fisrt
sudo mysql
```

Set password for root user.

```sql
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.

```bash
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)

```sql
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.

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

Then you need to type your password after the prompt:

```sql
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.
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1737827052858/019adc08-23a5-407e-959d-483372905eb4.jpeg align="center")

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