How to Install and Secure MariaDB on an Ubuntu VPS Print

How to Install and Secure MariaDB on an Ubuntu VPS

MariaDB is an open-source relational database server widely used by WordPress, PHP applications, content management systems, and web hosting platforms. It provides a reliable database backend for websites and applications running on Linux servers.

This guide explains how to install and secure MariaDB on an Ubuntu VPS, create databases and users, configure access permissions, perform backups, and apply basic performance optimizations.

The instructions apply to Ubuntu 24.04 LTS, Ubuntu 26.04 LTS, and other supported Ubuntu releases. The installed MariaDB version depends on your Ubuntu release and configured repositories.

Prerequisites

• An Ubuntu VPS with root or sudo access.
• A supported Ubuntu release.
• SSH access to your server.
• Updated system packages.

Need a database server? Explore Owned Networks KVM VPS hosting for full root access and flexible server resources.

1. Update Ubuntu and Install MariaDB

Update the package repositories and install MariaDB Server:

sudo apt update && sudo apt upgrade -y
sudo apt install -y mariadb-server mariadb-client

Enable MariaDB and verify that the service is running:

sudo systemctl enable --now mariadb
sudo systemctl status mariadb --no-pager
mariadb --version

2. Secure the MariaDB Installation

MariaDB includes an interactive security utility that helps review authentication settings, remove anonymous accounts, disable unnecessary remote administrative access, and remove test databases.

Run:

sudo mariadb-secure-installation

Review the prompts and apply the appropriate security settings for your environment.

On modern Ubuntu installations, MariaDB commonly uses Unix socket authentication for the local root database account. You do not necessarily need to configure a separate root database password.

Verify administrative access:

sudo mariadb

Exit the MariaDB console:

EXIT;

3. Create a MariaDB Database and User

Applications should use dedicated database accounts instead of the administrative root account.

Open the MariaDB console:

sudo mariadb

Create a database and application user:

CREATE DATABASE app_database
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

CREATE USER 'app_user'@'localhost'
IDENTIFIED BY 'REPLACE_WITH_A_STRONG_PASSWORD';

GRANT ALL PRIVILEGES
ON app_database.*
TO 'app_user'@'localhost';

EXIT;

Replace the example database name, username, and password with your application's credentials.

The example grants the application user permissions only on its own database. Avoid granting global privileges unless explicitly required.

4. Test Database Authentication

Verify that the application account can connect to MariaDB:

mariadb -u app_user -p app_database

Enter the database password when prompted.

Inside the MariaDB console, run:

SELECT DATABASE();
SHOW TABLES;
EXIT;

If the connection succeeds, the application account is ready to use.

5. Configure MariaDB Network Access

For security, MariaDB should generally remain accessible only from the local server unless remote database connections are required.

Check the current listening address:

sudo ss -tlnp | grep 3306

Review the MariaDB server configuration:

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

For a database accessed only by applications on the same VPS, use:

bind-address = 127.0.0.1

Restart MariaDB after changing the configuration:

sudo systemctl restart mariadb

6. Configure Remote MariaDB Access (Optional)

If an application hosted on another server requires database access, configure MariaDB to listen on an appropriate private network interface.

For example:

bind-address = 10.0.0.10

Replace the example address with the actual private IP address assigned to your database server.

Create a database user restricted to the application's source IP:

sudo mariadb

CREATE USER 'remote_app'@'10.0.0.20'
IDENTIFIED BY 'REPLACE_WITH_A_STRONG_PASSWORD';

GRANT ALL PRIVILEGES
ON app_database.*
TO 'remote_app'@'10.0.0.20';

EXIT;

Allow connections only from the authorized application server:

sudo ufw allow from 10.0.0.20 to any port 3306 proto tcp

Restart MariaDB and verify the listening address:

sudo systemctl restart mariadb
sudo ss -tlnp | grep 3306

Security recommendation: Do not expose MariaDB directly to the public internet. Use private networking, VPN connectivity, or an SSH tunnel. Configure TLS when database traffic crosses an untrusted network.

7. Configure Basic MariaDB Performance Settings

MariaDB performance depends on available RAM, database size, query patterns, and concurrent connections.

Create a dedicated configuration file:

sudo nano /etc/mysql/mariadb.conf.d/60-owned-tuning.cnf

For a small VPS running a modest database workload, consider these initial settings:

[mysqld]
innodb_buffer_pool_size = 256M
max_connections = 100
slow_query_log = ON
long_query_time = 2

These values are examples, not universal recommendations. A VPS running both MariaDB and a web server must reserve sufficient memory for the operating system and other services.

Validate the configuration and restart MariaDB:

sudo mariadbd --validate-config
sudo systemctl restart mariadb

Review the MariaDB service status after applying changes:

sudo systemctl status mariadb --no-pager

8. Back Up a MariaDB Database

Regular database backups are essential for protecting application data.

Create a backup directory:

sudo mkdir -p /var/backups/mariadb
sudo chmod 700 /var/backups/mariadb

Export the application database:

sudo mariadb-dump \
--single-transaction \
--routines \
--triggers \
--events \
app_database \
| sudo tee /var/backups/mariadb/app_database.sql > /dev/null

The --single-transaction option provides a consistent snapshot for transactional tables such as InnoDB without requiring a full table lock.

For large databases, consider compression and dedicated backup solutions.

9. Restore a MariaDB Database

To restore a database from a SQL backup, first ensure the destination database exists.

Then import the backup:

sudo mariadb app_database < /var/backups/mariadb/app_database.sql

Warning: Restoring a SQL dump can overwrite or modify existing data. Test restoration procedures before using them in production.

10. Automate MariaDB Backups

For recurring backups, create a simple backup script:

sudo nano /usr/local/sbin/mariadb-backup.sh

Add:

#!/bin/bash
set -euo pipefail

BACKUP_DIR="/var/backups/mariadb"
DATABASE="app_database"
DATE=$(date +%Y-%m-%d_%H-%M-%S)

umask 077
mkdir -p "$BACKUP_DIR"

mariadb-dump \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  "$DATABASE" | gzip > "$BACKUP_DIR/${DATABASE}_${DATE}.sql.gz"

find "$BACKUP_DIR" \
  -type f \
  -name "${DATABASE}_*.sql.gz" \
  -mtime +7 \
  -delete

Make the script executable:

sudo chmod 700 /usr/local/sbin/mariadb-backup.sh

Test it manually:

sudo /usr/local/sbin/mariadb-backup.sh
sudo ls -lh /var/backups/mariadb/

Schedule a daily backup using the root crontab:

sudo crontab -e

Add:

0 3 * * * /usr/local/sbin/mariadb-backup.sh

This runs the backup every day at 03:00 server time.

Important: Local backups alone are not sufficient for disaster recovery. Store additional encrypted backups outside the VPS, monitor backup execution, and periodically test database restoration.

11. Useful MariaDB Management Commands

# Check MariaDB version
mariadb --version

# Check service status
sudo systemctl status mariadb --no-pager

# Restart MariaDB
sudo systemctl restart mariadb

# View recent MariaDB logs
sudo journalctl -u mariadb -n 50 --no-pager

# Check listening ports
sudo ss -tlnp | grep 3306

# Open the MariaDB console
sudo mariadb

# Check database server variables
sudo mariadb -e "SHOW VARIABLES LIKE 'version%';"

12. Troubleshooting Common MariaDB Problems

Access denied: Verify the username, password, authentication method, and permitted connection host.

MariaDB fails to start: Check the service logs and validate configuration changes.

sudo systemctl status mariadb --no-pager
sudo journalctl -u mariadb -n 100 --no-pager

Remote connections fail: Verify the bind-address setting, firewall rules, and database user host restrictions.

Slow queries: Review the slow query log, database indexes, and available server resources.

Out of memory: Reduce excessive connection limits or memory allocation settings and monitor system RAM usage.

Related Ubuntu VPS Tutorials

• How to Set Up and Secure a New Ubuntu VPS
• How to Install and Configure Nginx on an Ubuntu VPS
• How to Install and Configure Apache on an Ubuntu VPS
• How to Install PHP and PHP-FPM on an Ubuntu VPS
• How to Host WordPress on an Ubuntu VPS with Nginx and Free SSL

Deploy Your Database Server with Owned Networks

Host MariaDB, WordPress databases, PHP applications, and custom database workloads on an Owned Networks KVM VPS with full root access and flexible server resources.

A VPS gives you control over database configuration, storage, backups, and application performance.

Explore Owned Networks VPS Hosting and deploy your database server today.