Can I run MySQL on AWS?

AWS or Amazon web services is a cloud service platform that provides on-demand computational services, databases, storage space, and many more services. EC2 or Elastic Compute Cloud is a scalable computing service launched on the AWS cloud platform. In simpler words, EC2 is nothing but a virtual computer on which we can perform all our tasks and we have the authority to configure, launch or even dissipate this virtual computer.

In this article, we will learn how to install MySQL on AWS EC2.

Prerequisite:

1. AWS account.
2. EC2 Instance.
3. User with privileges to create Instance.

Implementation:

Follow the steps below to install MySQL on AWS EC2:

Step 1: Create an AWS Elastic Cloud Compute Instance..

Step 2: Start the EC2 instance that you have created in Step 1.

Step 3: Connect to your EC2 Instance by clicking on Connect Button.

Step 4: A prompt will pop up after connecting.

Step 5: If MySQL is not installed on your virtual machine then install the MySQL server using the following command.

sudo apt install mysql-server

Step 6: A prompt will appear asking you for confirmation, press ‘y’ to confirm.

Step 7: We have successfully installed MySQL server on our EC2  instance, to check if the MySQL server is running or not, verify using the following command.

sudo systemctl status mysql

In this way, we can install MySQL on our EC2 instance using EC2 Instance Connect. And if you also use a free tier account, make sure you delete all the resources you have used before logging out. 

Running databases on local computers is easy and sometimes sufficient enough at the development step. But, deploying most applications needs to run databases on a remote server. There are thousands of solutions for deploying databases remotely. This article shows you how to create a simple database on AWS EC2 services and remotely manage it.

This article is written for beginners who have no cloud database deployment experience. Also, as said, there are many cloud-based and non-cloud-based solutions to deploy databases. For example, AWS has a dedicated service, called AWS RDS, for deploying databases on Cloud. We will discuss some of these solutions in the future and compare them. For today, let’s go with deploying a MySQL database on an AWS EC2 instance.

To start, we need an AWS account. You can set up an AWS account for FREE in a few minutes.

AWS Free Tier

Gain free, hands-on experience with the AWS platform, products, and services. Explore more than 85 products and start…

aws.amazon.com

This article explained the steps in more detail.

How to create an Amazon AWS Free Tier account

Amazon AWS Free Tier

account Amazon AWS Free Tierdan852.medium.com

After creating your FREE AWS account and logging in, click on Services (next to AWS logo), and from the list, select “EC2” (i.e., stands for Amazon Elastic Compute Cloud which is a fancy word for a cloud computer).

From the loaded page/dashboard, select “Launch instance.”

AWS shows you that you must follow 7 steps to launch an EC2 instance.

Step A1: Choose an Amazon Machine Image (AMI)

First, let’s choose an OS for our project. The best free choice for this purpose is Ubuntu Server 20.04, which is eligible for FREE Tiers.

Step A2: Choose an Instance Type

We don’t need a big machine to test the idea. Let’s go with a simple but FREE option like t2.micro. Later we can upgrade it if we want.

Click on Next to configure instance details

Step A3: Configure Instance Details

A crucial step, but for this demo, there is nothing really to change here. You are CRUSHING IT!!!

Step A4: Add Storage

Here you must set your storage size. To be eligible for Free Tier pricing, select 8GB (the default value). You may need to increase it later when working with bigger databases (and of course, you must pay for it).

Step A5: Add Tags

You can ignore this part too and jump to the next step.

P.S. For the future, take a look at this link for the best practices in AWS tagging.

Step A6: Configure Security Group

At this step, we should configure what ports on our EC2 instance should be exposed to the outside world (including you and your local computer). For the time being, we only open port 22 for connecting to our system through SSH (a secure protocol for interaction and data transfer between computers over the internet or a network). You can open different ports, such as HTTP or many others, based on your need. For our demo's purpose, we only need to open the SSH port (i.e., 22).

WARNING: For this demo, you don’t need to open the MySQL port (default 3306) to interact with the MySQL database. Opening this port to the outside world withough proper authentication and security in place is danegrous. I’ll show you later how to communicate with your database via a more secure connection.

Step A7: Review Instance Launch

Double-check everything and click on the Launch button. In a second, a window will pop up and ask you to choose a key pair. A key pair enables you to connect to your EC2 system over the internet securely. A key is a

sudo apt updatesudo apt install mysql-server

WARNING: Anyone who has access to this file can connect to your EC2 machine and use it.

You have two options here. First, if you already have a key pair, you can use your existing key. The second option is to choose a new key pair. Here I generate a new key pair for this demo. I give it a name and “Download Key Pair” in a secure folder.

It takes a few seconds (sometimes minutes) that your new EC2 instance is ready to use. To check the status of your EC2 instance, click on Services (again next to AWS logo) >>> EC2 >>> Instances.

You will see a list of your instances (or one instance, if it is your first instance). Make sure that your instance is ready (see figure below).

WARNING: Remember to stop your instance or terminate it after you did your test (unless you decided to keep it). A running instance might be FREE for the trial period, but if you forgot to stop or terminate it, you will get a bill from AWS after your trial period.

Step A8: Connecting to your EC2 Instance

If your EC2 system is running, now you can select your instance and from the top menu select Actions >>> Connect.

Choose “SSH client” from the opened page, and you should see full instructions on how to connect to your instance. Here, I am following the same instruction.

Basically, you need a tool called SSH Client to connect to your EC2 machine securely. If you have a system with Linux, Mac, or Windows 10 (or higher), you must have “SSH Client” installed and ready to go. Some Windows users should enable/install SSH Client on their computer before start using it. Here is a link that shows how to do it.

Open a terminal and go to the folder that contains your AWS key file (.pem file). For me, I saved my key file (

sudo apt updatesudo apt install mysql-server

sudo apt updatesudo apt install mysql-server

cd test_mysql_aws
 
chmod 400 my_test_key.pem
 
ssh -i “my_test_key.pem” [email protected]

Again, you can find full instructions when you click on Connect in your EC2 dashboard.

If you successfully built and connected to your EC2 instance, it is time to install the MySQL Server on your instance. My preferred way is to use APT package repository management tool.

sudo apt updatesudo apt install mysql-server

As soon as your installation is complete, the MySQL server should run automatically. You can check it with the following command.

sudo systemctl status mysql

It must return some information about the MySQL server, such as

sudo apt updatesudo apt install mysql-server

sudo mysql

You must set a password for your root now. Replace

sudo apt updatesudo apt install mysql-server

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password_here';mysql> FLUSH PRIVILEGES;

Now, let’s exit and log in with the root credentials.

mysql> exit$ sudo mysql -u root -p

Enter your root password, and hopefully, you get back to your MySQL command line.

Although it is not necessary for this section, I assume that you are familiar with SQL commands. If you are not familiar with SQL, I highly recommend taking these three courses, but you can continue for now.

Course 1: https://academy.vertabelo.com/course/sql-queries

Course 2: https://academy.vertabelo.com/course/sql-insert-update-delete

Course 3: https://academy.vertabelo.com/course/creating-tables-in-sql

First, let's make a dummy database (

sudo apt updatesudo apt install mysql-server

sudo apt updatesudo apt install mysql-server

CREATE DATABASE mysql_test;USE mysql_test;CREATE TABLE test_table1 (id INT, name VARCHAR(45));

Let’s insert some dummy data into our database too.

INSERT INTO test_table1 VALUES(1, 'Joe'), (2, 'Jane'), (3, 'Jack'), (4, 'Jessica');

And finally, let’s display all data in the table to make sure that nothing went wrong.

SELECT * FROM test_table1;

You must see a small table like the following in your MySQL application.

+------+---------+
| id   | name    |
+------+---------+
|    1 | Joe     |
|    2 | Jane    |
|    3 | Jack    |
|    4 | Jessica |
+------+---------+
4 rows in set (0.00 sec)

MySQL Workbench is a visual tool for database administration. It helps you to do complex database management tasks in a short time without sacrificing any flexibility. We can install this application on our local computer and manage any local or remote databases.

Let’s use this powerful tool to access and manage our MySQL database on the AWS EC2 instance that we just built. You can download and install the application from here. The installation is easy, straightforward, and just a few clicks away. This link also can help you to complete the installation.

REMEMBER: For this demo, we install MySQL Workbench on our local computer and not on the EC2 instance.

After installing MySQL Workbench on your local system, you should see a first page like the following figure.

Click on + next to MySQL Connections.

Give your connection an arbitrary name (e.g.,

sudo apt updatesudo apt install mysql-server

Besides, change the SSH Username to

sudo apt updatesudo apt install mysql-server

sudo systemctl status mysql

Click on the new connection, and it will ask for your MySQL root password. After entering the password, you can see your databases and tables under the Schema tab (see the figure below).

In the query area, you can write and run your SQL commands. Type the following commands, highlight them, and then click on the Execute button (shown with the red arrow in the figure below).

sudo apt updatesudo apt install mysql-server

As you see, it shows the content of your

sudo apt updatesudo apt install mysql-server

In this article, you start deploying a MySQL database on an AWS EC2 instance. You learn how to set up your EC2 instance, connect to it, install MySQL Server, configure your server, create some databases and tables, and finally manage them with MySQL Workbench.

Is MySQL free on AWS?

How do I deploy MySQL in AWS?

How to connect MySQL database in AWS?

How to create MySQL database on AWS?