Running a MySQL Database on AWS EC2 Beginners Tutorial (2023)

data science

It's easy to run a database on your local computer, and sometimes it's enough during the development phase. However, deploying most applications requires a database running on a remote server. There are thousands of solutions for remotely deploying databases. This article shows you how to create a simple database on AWS EC2 service and manage it remotely.

This article is written for beginners with no experience in cloud database deployment. Also, as mentioned earlier, 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 the cloud. We will discuss some of these solutions and compare them in the future. Today, let's deploy a MySQL database on an AWS EC2 instance.

First, we need an AWS account. You can set up an AWS account in minutes for free.

This article explains these steps in more detail.

Once you've created your free AWS account and signed in, click on Services (next to the AWS logo) and select "EC2" (which stands for Amazon Elastic Compute Cloud, which is another name for cloud computing) from the list.

From the page/dashboard that loads, select Launch Instance.

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

Step A1: Choose an Amazon Machine Image (AMI)

First, let's choose an operating system for our project. For this, the best free option is Ubuntu Server 20.04, which qualifies for the free tier.

Step A2: Select an Instance Type

We don't need big machines to test this idea. Let's choose a simple but free option such as t2.micro. Later we can upgrade it as needed.

Click Next to configure instance details

Step A3: Configure Instance Details

Crucial step, but for this demo, there's nothing to change here. You are smashing it!

Step A4: Add Storage

You must set the storage size here. To qualify for free tier pricing, select 8GB (the default). When working with larger databases, you may need to increase this later (you will have to pay for it, of course).

Step A5: Add tags

You can also ignore this part and skip to the next step.

P.S. For the future, check this outassociateLearn about best practices for AWS tagging.

Step A6: Configure Security Groups

In this step, we should configure which ports on our EC2 instance should be exposed to the outside world (both you and your local computer). Currently, we only open port 22 to connect to our systems via SSH (a secure protocol for interaction and data transfer between computers over the Internet or network). You can open different ports like HTTP or many others according to your needs. For our demo purposes, we only need to open the SSH port (i.e. 22).

Warning: For this demonstration, you do not need to open the MySQL port (default 3306) to interact with the MySQL database. It is dangerous to open this port to the outside world without proper authentication and security measures. I'll show you later how to communicate with your database over a more secure connection.

Step A7: View Instance Launches

Double check everything and click the "Launch" button. After a while, a window will pop up asking you to choose a key pair. Key pairs enable you to securely connect to your EC2 system over the Internet. a key is a.pemFiles that you must store in a safe place.

Warning: Anyone with 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 generated a new key pair for this demonstration. I named it and "Download Key Pair" in the secure folder.

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

You'll see a list of your instances (or an instance, if it's your first instance). Make sure your instance is ready (see image below).

Warning: Remember to stop or terminate the instance when you are done testing (unless you decide to keep it). A running instance may be free for the trial period, but if you forget to stop or terminate it, you will be billed from AWS after the trial period ends.

Step A8: Connect to your EC2 instance

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

Select "SSH Client" from the page that opens, and you should see full instructions on how to connect to your instance. Here I follow the same instructions.

Basically, you need a tool called SSH Client to connect securely to your EC2 machine. If your system is Linux, Mac or Windows 10 (or later), you must have an "SSH client" installed and ready to go. Some Windows users should enable/install an SSH client on their computer before getting started. here is oneassociateThis explains how to do it.

Open a terminal and go to the folder containing your AWS key file (.pem file). For me, I saved my key file (my test key.pem) in a namedtest_mysql_aws.

CD test_mysql_aws
chmod 400 my_test_key.pem
ssh -i “my_test_key.pem” ubuntu@ec2SSSSSS214.us-east-2.compute.amazonaws.com

Again, you can find full instructions when you click on a connection in the EC2 Dashboard.

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

sudo updatesudo apt install mysql-server

Once installed, the MySQL server will run automatically. You can check it with the following command.

sudo systemctl status mysql

It must return some information about the MySQL server, likeActive: active (running). Let's log in as root.

sudo mysql

You must now set a password for your root. replaceyour password hereUse strong passwords.

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

Now, let's log out and log in with root credentials.

mysql> exit$ sudo mysql -u root -p

Enter your root password and hopefully you'll be back at your MySQL command line.

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

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

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

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

First, let's make a dummy database (mysql_test) and a virtual table (Test sheet 1) in our database.

Create database mysql_test;use mysql_test;CREATE TABLE test_table1(id INT, name VARCHAR(45));

Let's also insert some dummy data into our database.

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

Finally, let's display all the data in the table to make sure nothing went wrong.

select * from test_table1;

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

+------+----------+
|Number |Name |
+------+----------+
| 1 | Joe |
| 2 | Jane |
| 3 | Jack |
| 4 | Jessica |
+------+----------+
A set of 4 lines (0.00 seconds)

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

Let's use this powerful tool to access and manage the MySQL database on the AWS EC2 instance we just built. You can download and install the application fromhere.Installation is simple and straightforward, just a few clicks away. thisassociateIt can also help you with the installation.

Remember: For this demo, we're installing MySQL Workbench on our local machine, not on an EC2 instance.

After installing MySQL Workbench on your local system, you should see the first page as shown in the image below.

Click + next to MySQL Connection.

Give your connection an arbitrary name (eg,AWS SQL test). From the Connection Method drop-down menu, select Standard TCP/IP over SSH. The SSH hostname is your EC2 instance public IPv4 DNS address. You can find this address on your EC2 dashboard by clicking on your instance and selecting Details from the tab menu.

Also, change the SSH username toUbuntuand find your SSH key file (the .pem file you got from AWS to connect via SSH). Finally, make sure your username isroot.Click OK and your connection should appear on the first page.

Click New Connection and it will ask for your MySQL root password. After entering the password, you can see your database and tables under the Schema tab (see image below).

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

use mysql_test;select * from test_table1;

As you can see it shows your contentmysql_testTable on AWS EC2. MySQL Workbench helps you easily manage your databases securely (via SSH tunneling) on ​​AWS.

In this article, you started by deploying a MySQL database on an AWS EC2 instance. You'll 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 using MySQL Workbench.