Setting up MariaDB on Ubuntu EC2 instance & configure remote access.

 Introduction:

Welcome to the blog by The Quick Desk!

Today, We are deploying a mariadb-server using AWS EC2 and enabling remote access to it. We will use the Ubuntu LTS 22.04 AMI for deploying our database. AWS already provides RDS services that have various advantages as Amazon RDS handles routine database tasks, such as provisioning, patching, backup, recovery, failure detection, and repair but if we want full control over the architecture and even want to install the database engine ourselves then this tutorial is for you.

MariaDB is an open-source relational database management system that is a fork of the popular MySQL database system. It is a robust and reliable database system widely used by developers and organizations worldwide. In this tutorial, we will install MariaDB on an Ubuntu EC2 instance, create a DBAdmin user, and configure it to allow remote access.

Prerequisites:

1. An Ubuntu running EC2 instance.

2. SSH access to the instance.

3. A user who has root privileges on the server.

4. MySQL port should be opened in inbound rules of EC2 instance.

#If you want a different port then add that port with TCP protocol in the inbound rule of the EC2 security group.

👉Installation of MariaDB on EC2

1. Connect via SSH your EC2 instance by using the IP.

2. Run the following command to update the package :

sudo apt update && sudo apt upgrade

3. Install the mariadb-server by using the following :

sudo apt install mariadb-server 

4. After the installation, start the MariaDB service :

sudo systemctl start mariadb 

5. Check the status of mariadb by using the following :

sudo systemctl status mariadb

 

👉Configure MySQL

1. Secure the MySQL installation on the server.

The "mysql_secure_installation" command is a script provided by MySQL that helps to secure the MySQL installation on the system. For securing the installation run :

• sudo mysql_secure_installation

This command will prompt you to set a root password, remove anonymous users, disable root login remotely, and remove test databases. It is recommended to answer "yes" to all prompts.

2. Configure MySQL Bind Address

By default, MySQL is not configured to allow access to the database from remote login, it serves to the local loopback address or localhost. So we need to make some changes in the configuration file of the MariaDB server to allow connections from the private IP.

#Default installation path:  /etc/mysql/

• Edit the configuration file by using :

              sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf

    Replace the value of bind-address to the Private_IP of the server. 

              For example:

 By default, the server uses port 3306 for communication if you want to use a custom port, like 

              

•       port              = 3406

👉Creating a DB Admin for remote access.

1. Log in to MariaDB as the root user by running the following command:

           sudo mysql -u root -p

 

      2. Create a new user by running the following command:

          CREATE USER 'Dbadmin'@'%' IDENTIFIED BY 'password@123';

      3. Grant the necessary privileges to the user by running the following command:

          GRANT ALL PRIVILEGES on *.* TO 'Dbadmin'@'%' WITH GRANT OPTION;

  #This command grants the user all privileges on all databases. It is advised to create a new user with limited privileges for remote access. For example:

CREATE USER 'dbuser1'@'%' IDENTIFIED BY 'password@123';

GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'dbuser1'@'%';

👉Connect to MariaDB Remotely

1. From your local machine, access the server by any mysql/mariadb supported client. For the command line, we can connect by running:

            mysql -h Public_IP_of_server -u Dbadmin -p 

If the server is configured to use a non-default port, for example, 3406, then you can use the following command:

            mysql -h hostname/IP -u user_name -P port_number -p 

👉Important Configurations and path of MariaDB :

👉Commands for mysql/mariadb service on Ubuntu :

To start mariadb service:

•  sudo systemctl start mariadb 

To start mariadb service:

•  sudo systemctl status mariadb 

To start mariadb service:

•  sudo systemctl stop mariadb

To restart mariadb service: 

•  sudo systemctl restart mariadb

In conclusion, from the above steps, you can easily deploy and host mariadb standalone servers for your project, also various configurations can be done for better security and performance. Stay tuned for updates and you can check out the video tutorial of the steps below: 

If you have any questions or comments, please feel free to leave them below. And don't forget to like, comment, and subscribe to our channel The Quick Desk for more helpful videos and blog posts!