Run and manage MySQL databases remotely
published in · 9 min read · March 22, 2021
9 min read
March 22, 2021
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.
AWS Free Tier
Get free hands-on experience with the AWS platform, products, and services. Explore over 85 products and start...
This article explains these steps in more detail.
How to Create an Amazon AWS Free Tier Account
Amazon AWS Free Tier
accountAmazon AWS Free Tierdan852.medium.com
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 named
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, like
Active: active (running). Let's log in as root.
You must now set a password for your root. replace
your 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.
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 to
Ubuntuand find your SSH key file (the .pem file you got from AWS to connect via SSH). Finally, make sure your username is
root.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 content
mysql_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? ›
- AWS MySQL Setup Step 1: The first step is to search for RDS from the AWS Management Console Search Bar.
- AWS MySQL Setup Step 2: Select RDS and click Create Database as shown below.
- AWS MySQL Setup Step 3: You should now select the required configurations for the database.
- Create an Amazon Linux 2 EC2 instance. ...
- Connect with your Linux EC2 using SSH. ...
- Installing MySQL on Amazon Linux 2 EC2 instance. ...
- Verifying the installation. ...
- Configure MySQL to accept remote connections.
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.
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 your instance as a local administrator using Remote Desktop Protocol (RDP).
- Open SQL Server Management Studio (SSMS). ...
- Choose Connect.
- In Object Explorer, expand Security.
- Open the context menu (right-click) for Logins then select New Login.
- Open MySQL Workbench.
- Select MySQL New Connection and enter a connection name.
- Choose the Connection Method, and select Standard TCP/IP over SSH.
- For SSH Hostname, enter the public IP address of your EC2 instance.
- For SSH Username, enter the default SSH user name to connect to your EC2 instance.
- Open 'Run' Window by using Win key + R.
- Type 'services.msc'
- Now search for MySQL service based on the version that is installed.
- Click on 'stop', 'start' or 'restart' the service option.
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? ›
- Connect to your instance. Set up to connect. Connect using SSH. Connect using EC2 Instance Connect. Set up EC2 Instance Connect. ...
- 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.
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? ›
- In the navigation pane, choose Key Pairs.
- Choose Create key pair.
- For Name, enter a descriptive name for the key pair. ...
- For Key pair type, choose either RSA or ED25519. ...
- For Private key file format, choose the format in which to save the private key.
- 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.
- In the User name box, enter a MySQL account that has the necessary permissions.
- In the Password box, enter the password for the specified user name.
- In SSMS, select Connect Object Explorer from the File menu.
- 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. ...
- Click the Connect button.
- Name: optional.
- Host: your MySQL hostname: mysql.example.com.
- Username: your database user name.
- Password: your database user password.
- Database: optional.
- Port: 3306.
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? ›
- Step 1: Edit MySQL Config File.
- 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.
- Step 3: Connect to Remote MySQL Server.
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? ›
- 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. ...
- Open the Amazon RDS console.
- From the navigation pane, choose Databases.
- Select the DB instance that you want to modify.
- Choose Modify. ...
- For Backup retention period, choose a positive nonzero value (such as "3").
- Choose Continue.
- Choose Apply immediately.
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 MySQL: On Solaris, Linux, or Mac OS, use the following command: Start: ./bin/mysqld_safe --defaults-file= install-dir /mysql/mysql.ini --user= user. ...
- To stop MySQL: On Solaris, Linux, or Mac OS, use the following command: Stop: bin/mysqladmin -u root shutdown -p.
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? ›
- Connect to the first virtual machine by using the remote desktop protocol (RDP).
- In Failover Cluster Manager, make sure that all core cluster resources are on the first virtual machine. ...
- Locate the installation media. ...
- Select Setup. ...
- Select New SQL Server failover cluster installation.
- Open your Google Cloud account and click SQL in the left menu.
- Click Create Instance.
- Choose your database engine. ...
- Choose an Instance ID. ...
- Select a root password.
- Choose the region. ...
- Click Show configuration options to expand the configuration options.
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? ›
- Start the virtual machine, if you haven't already.
- Log in to the virtual machine as the root user.
- At the command prompt, type the following command to install MariaDB: ...
- At the Is this ok prompt, type y and then press Enter. ...
- After installation completes, type the following command to start MariaDB:
- In the Google Cloud console, go to the Cloud SQL Instances page. ...
- Click Create instance.
- On the Choose your database engine panel of the Create an instance page, click Choose MySQL.
- In the Instance ID field of the Instance info pane, enter an ID for your instance. ...
- Set a password for the root user.
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? ›
- Step 1: Accessing the RDS Management Console.
- Step 2: Create your Database.
- Step 3: Choosing the Database Creation Method.
- Step 4: Configure the Username & Password.
- Step 5: Select the Database Instance Type.
- (2) Setup Amazon RDS. ...
- Step 2 — Modify Instance Settings. ...
- Step 3 — Edit security group configuration. ...
- 3) Setup MySQL Server. ...
- (5) Create Database Schema and Table. ...
- Step 2 — Create Table. ...
- Step 5 — Validate Database Changes.
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? ›
- Bluehost — $2.95 per month.
- Hostinger — $1.99 per month.
- A2 Hosting — $2.99 per month.
- SiteGround — $1.99 per month.
- HostGator — $2.64 per month.
- InMotion Hosting — $2.29 per month.