Step-by-Step Setup: MySQL Database Server on AWS EC2

When setting up a MySQL database on an EC2 instance using Ubuntu as your AMI (Amazon Machine Image), it's essential to grasp some key concepts first:

Key Concepts:

Database: A collection of organized information, made up of database
objects.

Database Object: Fundamental components that help organize, control, and access data effectively. Examples include tables, queries, forms, reports, etc.

Database Management System (DBMS): Software used to manage databases. Examples include MySQL, MongoDB, PostgreSQL, Oracle, and Microsoft SQL.

Relational Database: A database where data is stored in tables, with relationships between tables based on shared data.

Relational Database Management System (RDBMS): Software that maintains relational databases. Examples include MySQL, PostgreSQL, Microsoft SQL, and Oracle.

Database interaction occurs through web applications or directly through SQL commands.

The four core database operations (CRUD) are:
C - Create
R – Read

U - Update
D - Delete

Setting Up a MySQL Database Server on EC2:

Now that we understand these key concepts, let's walk through the steps to set up MySQL on an EC2 instance:

1. Launch the EC2 Instance
   Start by launching your EC2 instance and selecting Ubuntu as your AMI. Once the instance is running, update the operating system by executing:
   $ sudo apt update -y

2. Install MySQL Server
   Install MySQL by running the following command:
   $ sudo apt install mysql-server -y

3. Verify Installation
   Confirm that MySQL is installed by checking the version:
   $ mysql –version or locate the installation: $ which mysql

4. Check MySQL Status
   Ensure that the MySQL service is running with this command
   $ sudo systemctl status mysql

Securing MySQL:

After installing MySQL, the next best practice is to secure the installation. This involves setting a root password, removing anonymous users, and disabling remote root login. Run the secure installation script:
$ sudo mysql_secure_installation

This script will guide you through the following steps:
Setting a root password
Removing anonymous users
Disallowing root login remotely
Removing the test database
Once secured, you’re ready to start using MySQL.

Interacting with MySQL:

MySQL uses Structured Query Language (SQL) to manage data. To interact with the MySQL server, enter the MySQL shell:
$ sudo mysql

Within the MySQL shell (denoted by mysql>), here are a few useful commands:
mysql> SHOW DATABASES;
mysql> USE mysql;
mysql> SHOW TABLES;
_
Creating a Database and Table:
Let's now create a database and a table inside the MySQL server.

1. Create a Database To create a new database, use the CREATE DATABASE SQL command. For example, mysql> CREATE DATABASE schoolDB;

1. Create a Table To create a table in a database use the SQL command: CREATE TABLE table_name (     column1 datatype,     column2 datatype,     column3 datatype,    .... ); For example, we create a table named subject in the schoolDB database: mysql> CREATE TABLE subject ( ID int, First_name varchar(10), Last_name varchar(10) );

1. View Tables After creating the table, view the list of tables: mysql> SHOW TABLES;

Inserting and Retrieving Data:

1. Insert Data into the Table Use the INSERT INTO statement to add records. INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); Here's an example:

mysql> INSERT INTO subject (ID, First_name, Last_name)
VALUES (1, 'Qudus', 'Dosunmu');

1. Retrieve Data To view the records you’ve inserted, use the SELECT statement: mysql> SELECT * FROM subject;

NOTE:

1. MySQL is not case-sensitive, so commands can be entered in uppercase or lowercase.
2. Always end SQL commands with a semicolon (;).

Conclusion
With this step-by-step guide, you can now deploy and interact with a MySQL database on an EC2 instance. If you're looking to expand your knowledge or troubleshoot, feel free to reach out or share your experiences!

Sources & Credits:
W3school
Fredrick Achiever
Skill Afrika Cloud Computing Bootcamp For Beginners