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.
sudo apt update
sudo apt install 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.
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.
When installation is complete, the mysql instance is not secure, so we need to make few changes to make it more secure.
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.
Click + icon near MySQL Connections
Click the Connection Method dropdown
Select the Standard TCP/IP over SSH option
Fill EC2 IP as the the SSH Hostname
Fill EC2 SSH username
Click the [...] button on the right of SSH Key File input, then Select .pem file we used to ssh to the EC2
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.