Working with databases like MySQL on the cloud may seem challenging for beginners, but it's a highly valuable skill for anyone looking to work in cloud computing or DevOps. In this blog, I will walk you through the entire process of setting up MySQL on AWS EC2, connecting to it using the terminal, and accessing it with MySQL Workbench. By the end of this guide, you will have a fully functional MySQL instance running on AWS EC2.

Prerequisites

Before we begin, make sure you have the following:

• An AWS account

• A basic understanding of how to work with the Linux terminals (for running commands)

• A PEM key file for SSH access to your EC2 instance

• Finally, we assume that you have launched your EC2 instance. If not, the first part of this article explains how to do so.

Let's get to work:

Step 1: Installing MySQL on EC2

Now that you're connected to your EC2 instance, you need to install MySQL. Here are the steps:

1. To update the server: Run the following commands to ensure that your EC2 instance is up to date:

<sudo apt update>

<sudo apt upgrade>

2. Install MySQL: Install MySQL using the following command:

<sudo apt install mysql-server>

3. Start the MySQL Service: After installation, you need to start MySQL

<sudo systemctl start mysql>

4. Check MySQL Status: You can check if MySQL is running with this command:

<sudo systemctl status MySQL>

Step 2: Securing MySQL Installation

Once MySQL is installed, you should run the following security script provided by MySQL to improve its security configuration.
<sudo mysql_secure_installation>

1. This script will prompt you to:

a. Set a root password.

b. Remove anonymous users.

c. Disable root login remotely.

d. Remove test databases.

e. Reload the privilege tables.

2. Follow the prompts and set up your MySQL securely.

Step 3: Configuring MySQL for Remote Access

1. By default, MySQL is configured to accept connections only from localhost (i.e., the server it's running on). You’ll need to enable remote access so that you can connect from your machine via MySQL Workbench.

a. Open the MySQL configuration file to edit it:

<sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf>

b. Look for the following line:

bind-address = 127.0.0.1

c. Change 127.0.0.1 to 0.0.0.0, which allows connections from any IP address:

bind-address = 0.0.0.0

Save the file and exit.

2. Grant Remote Access to the Root User:

a. Login to MySQL as the root user:

<sudo mysql -u root -p>

b. Once inside the MySQL shell, run the following commands:

<GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'yourPassword';>

<FLUSH PRIVILEGES;>

This grants the root user access from any IP address ('%').

3. Restart MySQL to apply the changes:

<sudo systemctl restart MySQL>

Step 4: Configure Security Group

In this stage, you will need to configure your security group to open up ports for inbound traffic.

1. Add a rule for SSH on port 22 to connect to your instance via your terminal.

2. Add a rule for MySQL/Aurora on port 3306 to allow MySQL Workbench access to your MySQL database.

Step 5: Connecting to MySQL Using MySQL Workbench

Once you have MySQL running on EC2 and configured for remote access, you can connect to it using MySQL Workbench.

1. Download and Install MySQL Workbench on your local machine

2. Create a New Connection:

a. Open MySQL Workbench and click the + icon to create a new connection.

b. Enter the following details:

Connection Name: Any name you prefer (e.g., EC2-Mysql)

Hostname: The public IP address of your EC2 instance

Port: 3306 (the default MySQL port).

Username: root

Password: Enter the MySQL root password you set earlier.

3. Test the Connection: Click Test Connection to ensure everything is set up correctly. If successful, you’ll see a confirmation message.

Step 6: Basic MySQL Commands via Terminal

Once you’re connected to MySQL, either via SSH or Workbench, here are some essential MySQL commands:

1. Login to MySQL:

<mysql -u root -p>

Enter the password for the root user.

2. Create a Database:

<CREATE DATABASE myDatabase;>

This creates a new database named myDatabase.

3. Create a User:

<CREATE USER 'newUser'@'localhost' IDENTIFIED BY 'password';>

This creates a new user newUser with the password password.

4. Grant Privileges to a User:

<GRANT ALL PRIVILEGES ON myDatabase.* TO 'newUser'@'localhost';>

This grants the new user newUser all privileges on the myDatabase database.

5. View All Databases:

<SHOW DATABASES;>

6. Exit MySQL:

<exit>

You have successfully set up and connected MySQL to your EC2 instance. While this setup works well for development and testing, it's important to implement additional security measures and optimizations in a production environment to enhance database safety and performance.