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

data science

Run and manage MySQL databases remotely

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

published in

Towards Data Science


(Video) MySql Server on AWS EC2 Instance | EC2 Instance | Demo | MySql Server in AWS | Run MySql on AWS EC2

9 min read


March 22, 2021


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

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.

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

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

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

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

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

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.

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

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.

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

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

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

Step A5: Add tags

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

(Video) AWS: Hosting a MySQL DB on Amazon EC2 - Step by Step Tutorial

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.

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

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.

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

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

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

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.

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

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”

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.

(Video) AWS RDS MySQL Database Setup | Step by Step Tutorial

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:

Lesson 2:

Lesson 3:

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.

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

Click + next to MySQL Connection.

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

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.

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

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

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

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;
Running a MySQL Database on AWS EC2 Beginners Tutorial (18)

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.


How to deploy MySQL database on AWS EC2? ›

Steps to Set up AWS MySQL
  1. AWS MySQL Setup Step 1: The first step is to search for RDS from the AWS Management Console Search Bar.
  2. AWS MySQL Setup Step 2: Select RDS and click Create Database as shown below.
  3. AWS MySQL Setup Step 3: You should now select the required configurations for the database.
Oct 28, 2021

How do I start MySQL on Amazon EC2? ›

Installing MySQL on Amazon Linux 2 EC2 Instance and configure it for remote connections
  1. Create an Amazon Linux 2 EC2 instance. ...
  2. Connect with your Linux EC2 using SSH. ...
  3. Installing MySQL on Amazon Linux 2 EC2 instance. ...
  4. Verifying the installation. ...
  5. Configure MySQL to accept remote connections.
Jan 20, 2023

How to create MySQL database for beginners? ›

Using a GUI

Open the MySQL Workbench as an administrator (Right-click, Run as Admin). Click on File>Create Schema to create the database schema. Enter a name for the schema and click Apply. In the Apply SQL Script to Database window, click Apply to run the SQL command that creates the schema.

Can I run a database on EC2? ›

Amazon EC2 supports a self-managed SQL Server database. That is, it gives you full control over the setup of the infrastructure and the database environment. Running the database on Amazon EC2 is very similar to running the database on your own server.

How do I connect to a SQL database on an EC2 instance? ›

Connect to a Microsoft SQL Server on Amazon EC2
  1. Connect to your instance as a local administrator using Remote Desktop Protocol (RDP).
  2. Open SQL Server Management Studio (SSMS). ...
  3. Choose Connect.
  4. In Object Explorer, expand Security.
  5. Open the context menu (right-click) for Logins then select New Login.

How to connect to MySQL hosted on EC2? ›

  1. Open MySQL Workbench.
  2. Select MySQL New Connection and enter a connection name.
  3. Choose the Connection Method, and select Standard TCP/IP over SSH.
  4. For SSH Hostname, enter the public IP address of your EC2 instance.
  5. For SSH Username, enter the default SSH user name to connect to your EC2 instance.

How do I manually start MySQL server? ›

Windows – Start and Stop Server
  1. Open 'Run' Window by using Win key + R.
  2. Type 'services.msc'
  3. Now search for MySQL service based on the version that is installed.
  4. Click on 'stop', 'start' or 'restart' the service option.
Mar 10, 2021

Does EC2 have MySQL? ›

Some of these features may require OS access to MySQL or advanced privileges to access certain system procedures and tables. MySQL on Amazon EC2 is an alternative to Amazon RDS and Aurora for certain use cases.

Is MySQL beginner friendly? ›

Simple and straightforward. MySQL is user-friendly. Basic knowledge of SQL and its statements will allow efficient interaction with MySQL Server. You can query and update data as well as administer the databases.

Is it easy to learn MySQL for beginners? ›

Although PHP and MySQL are simple to learn, they still offer challenges. However, many challenges you will face are not particular to PHP and MySQL but rather all languages. Choosing a simple language to learn first allows you to face these challenges in a more forgiving environment.

What is the basics of MySQL database? ›

MySQL is a relational database management system (RDBMS) developed by Oracle that is based on structured query language (SQL). A database is a structured collection of data. It may be anything from a simple shopping list to a picture gallery or a place to hold the vast amounts of information in a corporate network.

How to run RDS in EC2? ›

  1. Connect to your instance. Set up to connect. Connect using SSH. Connect using EC2 Instance Connect. Set up EC2 Instance Connect. ...
  2. Connect your instance to a resource. Tutorial: Connect an EC2 instance to an RDS database. Option 1: Automatically connect – EC2 console. Task 1: Create an RDS database – optional.

What is difference between EC2 and RDS? ›

Both Amazon RDS and Amazon EC2 offer different advantages for running Oracle Database. Amazon RDS is easier to set up, manage, and maintain than running Oracle Database on Amazon EC2, and lets you focus on other important tasks, rather than the day-to-day administration of Oracle Database.

How to create SQL Server on EC2 instance? ›

Open the Amazon EC2 console at .
  1. In the navigation pane, choose Key Pairs.
  2. Choose Create key pair.
  3. For Name, enter a descriptive name for the key pair. ...
  4. For Key pair type, choose either RSA or ED25519. ...
  5. For Private key file format, choose the format in which to save the private key.

How to connect MySQL database server? ›

Establishing a Connection to MySQL
  1. In the Server name box, enter the MySQL server name. In the Server port box, enter the port number to be 3306. It is the default port.
  2. In the User name box, enter a MySQL account that has the necessary permissions.
  3. In the Password box, enter the password for the specified user name.
Feb 28, 2023

How do I connect to a SQL database instance? ›

Connect using the SSMS Object Explorer
  1. In SSMS, select Connect Object Explorer from the File menu.
  2. Enter the following values in the Connection dialog: For Server Type, enter Database Engine. For Server Name, enter 127.0. 0.1 as the IP address of your SQL Server instance. ...
  3. Click the Connect button.

How to connect MySQL with host and port? ›

Use the TCP/IP connection tab and enter the following:
  1. Name: optional.
  2. Host: your MySQL hostname:
  3. Username: your database user name.
  4. Password: your database user password.
  5. Database: optional.
  6. Port: 3306.
Dec 27, 2022

Can we host MySQL on AWS? ›

Amazon offers a fully managed relational database service, Amazon RDS for MySQL, available for trial at no cost with the AWS Free Tier. Amazon RDS makes it easy to set up, operate, and scale MySQL deployments in the cloud.

How do I allow a host to connect to MySQL server? ›

How to Allow Remote Connections to MySQL
  1. Step 1: Edit MySQL Config File.
  2. Step 2: Set up Firewall to Allow Remote MySQL Connection. Option 1: UFW (Uncomplicated Firewall) Option 2: FirewallD. Option 3: Open Port 3306 with iptables.
  3. Step 3: Connect to Remote MySQL Server.
Mar 26, 2020

What is the difference between MySQL instance and database? ›

The database is the set of files where application data (the reason for a database) and meta data is stored. An instance is the software (and memory) that Oracle uses to manipulate the data in the database. In order for the instance to be able to manipulate that data, the instance must open the database.

What tool opens MySQL database? ›

Top MySQL GUI tools
  • MySQL Workbench. MySQL Workbench is a visual schema and query builder that is currently the only SQL client supported and developed by MySQL. ...
  • BeeKeeper Studio. ...
  • dbForge Studio for MySQL. ...
  • HeidiSQL. ...
  • Navicat for MySQL. ...
  • SQLyog. ...
  • DBeaver. ...
  • phpMyAdmin.
Mar 18, 2022

How to backup MySQL database in AWS? ›

Enabling automated backups
  1. Open the Amazon RDS console.
  2. From the navigation pane, choose Databases.
  3. Select the DB instance that you want to modify.
  4. Choose Modify. ...
  5. For Backup retention period, choose a positive nonzero value (such as "3").
  6. Choose Continue.
  7. Choose Apply immediately.

How to activate MySQL database? ›

Launch the MySQL Command-Line Client. To launch the client, enter the following command in a Command Prompt window: mysql -u root -p . The -p option is needed only if a root password is defined for MySQL. Enter the password when prompted.

How do I start MySQL commands? ›

To Start or Stop MySQL
  1. To start MySQL: On Solaris, Linux, or Mac OS, use the following command: Start: ./bin/mysqld_safe --defaults-file= install-dir /mysql/mysql.ini --user= user. ...
  2. To stop MySQL: On Solaris, Linux, or Mac OS, use the following command: Stop: bin/mysqladmin -u root shutdown -p.

Is MySQL free on AWS? ›

The AWS Free Tier provides free use of Amazon RDS for MySQL for up to 750 instance hours per month. You also receive 20 GB of database storage and 20 GB of backup storage for free per month.

Is EC2 just a server? ›

An Amazon EC2 instance is a virtual server in Amazon's Elastic Compute Cloud (EC2) for running applications on the Amazon Web Services (AWS) infrastructure.

Is EC2 just a VM? ›

Amazon Elastic Compute Cloud (EC2) is the Amazon Web Service you use to create and run virtual machines in the cloud (we call these virtual machines 'instances').

How many hours does it take to learn MySQL? ›

Key Takeaways. MySQL takes an average of 6 to 7 months to learn. You can learn both simultaneously in less than a year. It can take up to 4 years to learn several different back end languages.

What should I know before learning MySQL? ›

It is very important to understand the database before learning MySQL. A database is an application that stores the organized collection of records. It can be accessed and manage by the user very easily. It allows us to organize data into tables, rows, columns, and indexes to find the relevant information very quickly.

How long does it take to learn MySQL from scratch? ›

On its own, SQL isn't hard to learn. You can learn SQL in as little as two to three weeks. However, it can take months of practice before you feel comfortable using it. Determining how long it takes to learn SQL also depends on how you plan to use it.

Can you learn MySQL in a week? ›

You could feasibly put together a simple application with a data store using MySQL within the very first week of learning. But to build more complex applications and write advanced queries, you will need to devote a month or two to understand the fundamentals of the technology.

Do I need to learn SQL before learning MySQL? ›

Since SQL is a data query language, you must master the SQL language first to work on any database management system. Knowledge of SQL is a must for storing, manipulating and retrieving data in any RDBMS. Once you have learnt SQL, you can move on to learning the fundamentals of RDBMS, such as MySQL.

Can I learn MySQL without any programming knowledge? ›

Whether you want to become a back end developer or simply familiarize yourself with databases, you'll need to learn SQL. However, programming languages can seem a little daunting for beginners. Fortunately, you can start learning SQL even without coding experience.

What are the 3 categories of MySQL? ›

The data type is a guideline for SQL to understand what type of data is expected inside of each column, and it also identifies how SQL will interact with the stored data. In MySQL there are three main data types: string, numeric, and date and time.

What is MySQL for dummies? ›

MySQL is an open-source relational database management system with support for Structured Query Language (SQL). It helps in the development of a wide range of web-based applications and online content. MySQL runs on all OS platforms like Linux/UNIX, macOS, Windows and is an important component of the LAMP stack.

What language is used in MySQL? ›

SQL is the preferred language for most relational database management systems, including Oracle, MySQL, PostgreSQL, Microsoft SQL Server, and IBM DB2. IBM developed SQL in the late 1970s.

Can I install MySQL on EC2 instance? ›

MySQL Server can easily be installed in the EC2 instance of AWS running a Linux OS or Ubuntu OS through very basic Linux commands.

How to install SQL Server on Amazon EC2? ›

Use Amazon FSx share.
  1. Connect to the first virtual machine by using the remote desktop protocol (RDP).
  2. In Failover Cluster Manager, make sure that all core cluster resources are on the first virtual machine. ...
  3. Locate the installation media. ...
  4. Select Setup. ...
  5. Select New SQL Server failover cluster installation.

How to deploy MySQL database on cloud? ›

Create a Database Instance
  1. Open your Google Cloud account and click SQL in the left menu.
  2. Click Create Instance.
  3. Choose your database engine. ...
  4. Choose an Instance ID. ...
  5. Select a root password.
  6. Choose the region. ...
  7. Click Show configuration options to expand the configuration options.

What is the difference between MySQL and MariaDB? ›

MariaDB is an open-source fork of MySQL created in 2009. MariaDB is a backward-compatible improved version of MySQL. It comes with various inbuilt capable features and many security and execution improvements missing in MySQL. MariaDB supports the same features that MySQL does but offers additional ones too.

How to install MySQL in virtual machine? ›

Installing MySQL
  1. Start the virtual machine, if you haven't already.
  2. Log in to the virtual machine as the root user.
  3. At the command prompt, type the following command to install MariaDB: ...
  4. At the Is this ok prompt, type y and then press Enter. ...
  5. After installation completes, type the following command to start MariaDB:

How to create instance in MySQL? ›

  1. In the Google Cloud console, go to the Cloud SQL Instances page. ...
  2. Click Create instance.
  3. On the Choose your database engine panel of the Create an instance page, click Choose MySQL.
  4. In the Instance ID field of the Instance info pane, enter an ID for your instance. ...
  5. Set a password for the root user.

Is MySQL stored or virtual? ›

The VIRTUAL or STORED keyword indicates how column values are stored, which has implications for column use: VIRTUAL : Column values are not stored, but are evaluated when rows are read, immediately after any BEFORE triggers. A virtual column takes no storage.

What is the equivalent of MySQL in AWS? ›

Amazon Aurora is a relational database management system (RDBMS) built for the cloud with full MySQL and PostgreSQL compatibility.

How do I deploy a database to AWS? ›

You can follow these 9 simple steps to set up your AWS SQL Server Deployment:
  1. Step 1: Accessing the RDS Management Console.
  2. Step 2: Create your Database.
  3. Step 3: Choosing the Database Creation Method.
  4. Step 4: Configure the Username & Password.
  5. Step 5: Select the Database Instance Type.
Mar 30, 2022

How to connect AWS MySQL database in Python? ›

This package serves as the vital link between the Python application and MySQL database.
  1. (2) Setup Amazon RDS. ...
  2. Step 2 — Modify Instance Settings. ...
  3. Step 3 — Edit security group configuration. ...
  4. 3) Setup MySQL Server. ...
  5. (5) Create Database Schema and Table. ...
  6. Step 2 — Create Table. ...
  7. Step 5 — Validate Database Changes.
Feb 9, 2022

How to convert SQL Server to MySQL in AWS? ›

To edit SQL Server to MySQL conversion settings, in AWS SCT choose Settings, and then choose Conversion settings. From the upper list, choose SQL Server, and then choose SQL Server – MySQL. AWS SCT displays all available settings for SQL Server to MySQL conversion.

Does MySQL need a server? ›

The answer is yes, you do. MySQL Workbench is a powerful graphical tool for database design and administration. It is a great tool for managing and developing databases, but it requires a web server to be able to access the database. Without a web server, you won't be able to access the database from the Workbench.

Where to host MySQL database? ›

Some of the best budget web hosting plans with unlimited MySQL databases are:
  1. Bluehost — $2.95 per month.
  2. Hostinger — $1.99 per month.
  3. A2 Hosting — $2.99 per month.
  4. SiteGround — $1.99 per month.
  5. HostGator — $2.64 per month.
  6. InMotion Hosting — $2.29 per month.
May 5, 2023


1. MySQL : Deploy , Configure and remote access the MySQL on EC2
(E-MultiSkills Database Tutorials)
2. How to Setup MySQL on AWS ec2
(AWS With Atiq)
3. AWS RDS Hands on Lab with MYSQL Database for beginners
4. How to Set Up MySQL on AWS EC2 | Step-by-Step Guide
5. AWS RDS with MySQLWorkbench
6. AWS - Configure and Connect a MySQL Database Instance with a WebServer.
(UmeshRai GamingYt)


Top Articles
Latest Posts
Article information

Author: Ms. Lucile Johns

Last Updated: 15/06/2023

Views: 5267

Rating: 4 / 5 (61 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Ms. Lucile Johns

Birthday: 1999-11-16

Address: Suite 237 56046 Walsh Coves, West Enid, VT 46557

Phone: +59115435987187

Job: Education Supervisor

Hobby: Genealogy, Stone skipping, Skydiving, Nordic skating, Couponing, Coloring, Gardening

Introduction: My name is Ms. Lucile Johns, I am a successful, friendly, friendly, homely, adventurous, handsome, delightful person who loves writing and wants to share my knowledge and understanding with you.