Getting Started with MariaDB on EC2 with Ubuntu: A Step-by-Step Tutorial configure remote access - Tools To Learning: Mastering IT and Everyday Tools for Success

MariaDB, born from MySQL and developed by the project’s founder following its acquisition by Oracle, is a powerful database management system. This tutorial guides you through the process of setting up MariaDB on an EC2 server, covering key steps from installation to advanced configurations. Learn how to remotely access your EC2 server, secure your installation, and perform advanced tasks to optimize your database setup.

Installation: To install MariaDB on your EC2 server, follow these steps:

Update the repository:
```
sudo apt update
```
Install MariaDB:
```
sudo apt install -y mariadb-server
```
Secure your installation:
```
sudo mysql_secure_installation
```
Follow the on-screen prompts to enhance security, including setting a strong root password and removing anonymous users.

Accessing Your Database Remotely: To access your MariaDB remotely, use SSH to connect to your EC2 server. Follow these steps:

Connect to the server:
```
sudo ssh -i key.pem user@ip
```
Install necessary packages:
```
sudo apt install -y mariadb-server
```

Testing Your Database: After installation, test your MariaDB instance:

Check the status:
```
sudo systemctl status mariadb
```
Verify the version:
```
sudo mysqladmin version
```

Start/Stop/Reload and System Initialization: Manage MariaDB services on your server:

Start the database:
```
sudo systemctl start mariadb
```
Enable automatic startup:
```
sudo systemctl enable mariadb
```
Stop the database:
```
sudo systemctl stop mariadb
```
Restart the database:
```
sudo systemctl restart mariadb
```

Advanced Configurations: Enable remote access and configure advanced settings:

Allow remote access:
```
sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf
```
Comment out the “bind-address” line to accept connections from any IP address.
Save the changes and restart MariaDB:
```
sudo systemctl restart mariadb
```

Accessing the Database: Connect to your MariaDB instance:

mysql -u root -p

allow access for user on ip 18.234.94.191

GRANT ALL PRIVILEGES ON *.* TO 'remoteuser'@'18.234.94.191' IDENTIFIED BY 'password' WITH GRANT OPTION;

FLUSH PRIVILEGES;

Modifications: Manage user privileges and test connections – In this example we only allow ip 3.237.183.135 or a full network 192.168.1.0/24:

Revoke user privileges:

REVOKE ALL PRIVILEGES ON *.* FROM 'remoteuser'@'3.237.183.135';
FLUSH PRIVILEGES;

Grant access to a network:

GRANT ALL PRIVILEGES ON *.* TO 'remoteuser'@'192.168.1.0/24' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

Testing Connection with Cloud9 or Visual Studio Code: Ensure a successful connection using a Python script. Install the required library:

pip install mysql-connector-python

In the security group of the EC2 machine, do not forget to allow the IP of the remote machine or network. Use port 3306.

Create a Python file with the following code and execute it:

python

import mysql.connector

config = {
  'user': 'remoteuser',
  'password': 'password',
  'host': 'machine-ip',
  'database': 'tutorial'
}

try:
    conn = mysql.connector.connect(**config)
    print("Connection successful.")
except mysql.connector.Error as err:
    print(f"Connection failed: {err}")

conn.close()

Review the output to confirm a successful or failed connection.

Conclusion: Congratulations! You’ve successfully set up and configured MariaDB on your EC2 server, allowing for secure remote access. Use this tutorial as a foundation to explore advanced configurations and optimize your database for seamless integration with your applications.