MySQL vs. MariaDB: Setting Up a Secure Database Server on Ubuntu

Setting up a database server is a crucial step in building scalable applications. Whether you’re deploying web applications or managing enterprise workloads, MySQL and MariaDB are two widely used database solutions that offer reliability and performance. This guide walks you through installing, configuring, securing, and comparing MySQL and MariaDB on an Ubuntu server.

Table of Contents

1. Introduction
2. Prerequisites
3. MySQL vs. MariaDB: Key Differences
4. Installing MySQL or MariaDB
5. Configuring the Database Server
6. Securing Your Database Server
7. Managing Your Database Server
8. Conclusion

Introduction

Databases are the backbone of modern applications, enabling efficient data storage and retrieval. Ubuntu, being one of the most popular Linux distributions, makes it easy to deploy database servers like MySQL and MariaDB. This guide will help you set up, configure, secure, and manage a database server to meet your application needs.

Prerequisites

Before you begin, ensure the following:

• Ubuntu Server: A running instance of Ubuntu (20.04 or later is recommended).
• Administrative Access: You’ll need sudo privileges to install and configure the database server.
• Internet Connection: Required for downloading packages and updates.

MySQL vs. MariaDB: Key Differences

While MySQL and MariaDB share the same roots, they have diverged in recent years to offer distinct features. Here’s a comparison to help you choose the right database server for your needs:

Recommendation:

• Choose MySQL if you need enterprise-grade features and compatibility with proprietary tools.
• Go with MariaDB if you prefer open-source software with enhanced features and performance.

Installing MySQL or MariaDB

Step 1: Update the Package Repository

Update your system to ensure you’re working with the latest package versions:

sudo apt update && sudo apt upgrade

Step 2: Install MySQL or MariaDB

You can install either MySQL or MariaDB depending on your preference.

Installing MySQL:

sudo apt install mysql-server

Installing MariaDB:

sudo apt install mariadb-server

Step 3: Verify Installation

After installation, ensure the database server is running:

For MySQL:

sudo systemctl status mysql

For MariaDB:

sudo systemctl status mariadb

Configuring the Database Server

Step 1: Basic Configuration

Edit the configuration file to optimize performance and security.

For MySQL:

sudo nano /etc/mysql/my.cnf

For MariaDB:

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

Key settings to adjust:

• bind-address: Set this to 0.0.0.0 if you want remote access.
• max_connections: Increase this value for high-traffic applications.

After making changes, restart the service:

sudo systemctl restart mysql   # For MySQL
sudo systemctl restart mariadb # For MariaDB

Step 2: Test the Database Server

Log in to the database server to ensure it’s working:

mysql -u root -p

Securing Your Database Server

Step 1: Run the Security Script

Use the built-in security script to harden your database server.

sudo mysql_secure_installation

Follow the prompts to:

• Set a strong root password.
• Remove anonymous users.
• Disable remote root login.
• Remove test databases.

Step 2: Configure Firewall Rules

Restrict access to the database server using ufw:

sudo ufw allow from <your-ip-address> to any port 3306

Replace <your-ip-address> with the IP address that requires access.

Step 3: Enable SSL/TLS

For enhanced security, enable SSL/TLS connections. Refer to the MySQL documentation or MariaDB documentation for setup instructions.

 (Example for MySQL)

1. Generate certificates:

sudo mysql_ssl_rsa_setup --uid=mysql  

2. Enable in config:

[mysqld]  
ssl-ca = /var/lib/mysql/ca.pem  
ssl-cert = /var/lib/mysql/server-cert.pem  
ssl-key = /var/lib/mysql/server-key.pem  

Step 4: Install Fail2Ban (Optional)

sudo apt install fail2ban  

Configure jail rules for MySQL/MariaDB in /etc/fail2ban/jail.local.

Managing Your Database Server

Step 1: Create a Database

Create a database for your application:

CREATE DATABASE my_application_db;

Step 2: Create a User and Grant Privileges

Create a user and assign privileges:

CREATE USER 'app_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON my_application_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

Step 3: Monitor Performance

# Analyze slow queries  
sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log  

# Use MySQLTuner  
wget https://mysqltuner.pl -O mysqltuner.pl  
perl mysqltuner.pl  

Step 4: Backup and Restore

Regular backups are essential for data safety.

Backup:

mysqldump -u root -p my_application_db > my_application_db.sql

Restore:

mysql -u root -p my_application_db < my_application_db.sql

Automated Backups (Cron Job)

# Daily backup at 2 AM  
0 2 * * * /usr/bin/mysqldump -u root -p PASSWORD app_db > /backups/app_db_$(date +\%F).sql  

Conclusion

Both MySQL and MariaDB are excellent choices for setting up a database server on Ubuntu. While MySQL is known for its enterprise-grade features, MariaDB stands out with its open-source approach and performance enhancements. By following this guide, you can install, configure, secure, and manage your database server effectively.

Coming Up Next:
In our next guide, “Mastering LVM on Ubuntu: Flexible Storage for Database Servers”, you’ll learn how to:

• Create and manage logical volumes for dynamic database storage.
• Resize disks without downtime.
• Implement snapshots for consistent backups.

Don’t miss it – follow our blog to stay updated!