How to Install MySQL/MariaDB on an Ubuntu VPS for Production

Every web application, CMS, and API backend needs a reliable database. MariaDB — the community-driven fork of MySQL — has become the default relational database on Ubuntu, offering better performance, more storage engines, and true open-source governance. This guide covers the complete production setup: installing MariaDB 11.x from the official repository, securing the installation, creating databases and users with proper privileges, tuning performance for your workload, configuring automated backups, setting up basic replication, enabling remote access with SSL, and monitoring query performance.

Whether you're running WordPress, Laravel, Django, or a custom application, following this guide will give you a database server that's fast, secure, and ready for production traffic.

Prerequisites

Before starting, you need:

• An Ubuntu 24.04 VPS. For most web applications with a single database, a Cloud VPS with 2 vCPU / 4 GB RAM provides plenty of headroom. MariaDB's InnoDB buffer pool benefits directly from available memory.
• Root or sudo access. If you haven't secured your server yet, follow our Ubuntu VPS setup guide and security hardening guide first.
• A firewall configured. Port 3306 should be closed to the public by default. We'll open it selectively only if remote access is required.

Why MariaDB over MySQL? MariaDB is a drop-in replacement for MySQL. It uses the same client tools (mysql, mysqldump), the same wire protocol, and the same SQL syntax. But MariaDB includes performance improvements like thread pool, Aria storage engine, and better query optimizer. Ubuntu 24.04 ships MariaDB as the default mysql-server package. For this guide, we install the latest 11.x release from MariaDB's official repository for the newest features and fixes.

MariaDB vs MySQL: Which Should You Install?

Both are excellent production databases. Here's how they compare on Ubuntu 24.04:

For the vast majority of VPS deployments, MariaDB is the better choice. It's faster out of the box, includes more features in the free community edition, and is the default on Ubuntu. This guide uses MariaDB, but every command and configuration applies identically to MySQL unless noted otherwise.

Installing MariaDB 11.x from the Official Repository

Ubuntu 24.04's default repositories include MariaDB 10.11. For the latest 11.x release with improved performance and features, add MariaDB's official repository.

Step 1: Add the MariaDB Repository

Install the prerequisites and add the MariaDB signing key:

sudo apt update
sudo apt install -y curl gnupg2 software-properties-common

Add the MariaDB repository key and source:

curl -fsSL https://mariadb.org/mariadb_release_signing_key.pgp | sudo gpg --dearmor -o /usr/share/keyrings/mariadb-keyring.gpg

echo "deb [signed-by=/usr/share/keyrings/mariadb-keyring.gpg] https://dlm.mariadb.com/repo/mariadb-server/11.6/repo/ubuntu noble main" | sudo tee /etc/apt/sources.list.d/mariadb.list

Step 2: Install MariaDB Server

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

Verify the installation:

mariadb --version
# mariadb  Ver 15.1 Distrib 11.6.x-MariaDB

sudo systemctl status mariadb

MariaDB should be active and running. Enable it to start on boot if it isn't already:

sudo systemctl enable mariadb

Running mysql_secure_installation

The mariadb-secure-installation script removes default insecure settings. Run it immediately after installation:

sudo mariadb-secure-installation

Answer the prompts as follows:

Enter current password for root (enter for none): [press Enter]
Switch to unix_socket authentication [Y/n]: Y
Change the root password? [Y/n]: Y
New password: [enter a strong password]
Re-enter new password: [confirm password]
Remove anonymous users? [Y/n]: Y
Disallow root login remotely? [Y/n]: Y
Remove test database and access to it? [Y/n]: Y
Reload privilege tables now? [Y/n]: Y

These steps accomplish the following:

• Unix socket authentication: Root can only connect locally via sudo mariadb, not with a password over the network.
• Root password: Set a strong password as a backup authentication method.
• Anonymous users removed: No one can connect without proper credentials.
• Remote root login disabled: Root can only connect from localhost.
• Test database removed: Eliminates a database that's accessible to anonymous users.

Verify you can connect:

sudo mariadb

You should see the MariaDB prompt:

Welcome to the MariaDB monitor.
MariaDB [(none)]>

Type exit to leave the shell.

Creating Databases and Users

Never use the root account for application connections. Create dedicated users with the minimum privileges needed.

Create a Database

sudo mariadb

CREATE DATABASE myapp_production
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

SHOW DATABASES;

Always use utf8mb4 as the character set. It supports full Unicode including emojis, unlike the older utf8 (which is actually utf8mb3 in MySQL/MariaDB).

Create an Application User

CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'StrongPassword!2026#Prod';
GRANT ALL PRIVILEGES ON myapp_production.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

This grants full access to only the myapp_production database. The user cannot access other databases or perform administrative operations.

Create a Read-Only User

For reporting dashboards, analytics queries, or replica connections, create a read-only user:

CREATE USER 'myapp_readonly'@'localhost' IDENTIFIED BY 'ReadOnlyPass!2026#Secure';
GRANT SELECT ON myapp_production.* TO 'myapp_readonly'@'localhost';
FLUSH PRIVILEGES;

Create a Backup User

For automated backups, create a user with only the privileges needed for mysqldump:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupPass!2026#Safe';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, RELOAD, PROCESS ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Verify User Privileges

SHOW GRANTS FOR 'myapp'@'localhost';
SHOW GRANTS FOR 'myapp_readonly'@'localhost';
SHOW GRANTS FOR 'backup_user'@'localhost';

Test the application user connection from the command line:

mariadb -u myapp -p myapp_production

Enter the password when prompted. You should land in the myapp_production database.

Performance Tuning

MariaDB's default configuration is conservative, designed to work on minimal hardware. For a production VPS, tuning a handful of key parameters makes a significant difference.

Edit the MariaDB configuration file:

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

Add or modify these settings under the [mysqld] section:

InnoDB Buffer Pool

The single most impactful setting. The InnoDB buffer pool caches table data and indexes in memory. Set it to 50-70% of your total RAM for a dedicated database server, or 25-40% if the server also runs your application.

# For a 4 GB VPS running both app and database
innodb_buffer_pool_size = 1G

# For a 4 GB VPS dedicated to database only
# innodb_buffer_pool_size = 2G

# For an 8 GB VPS dedicated to database only
# innodb_buffer_pool_size = 5G

Connection Limits

# Maximum simultaneous connections
max_connections = 150

# Timeout for idle connections (seconds)
wait_timeout = 600
interactive_timeout = 600

# Thread cache for connection reuse
thread_cache_size = 16

InnoDB Performance Settings

# Log file size (larger = better write performance, slower crash recovery)
innodb_log_file_size = 256M

# Flush method — O_DIRECT avoids double-buffering with OS cache
innodb_flush_method = O_DIRECT

# Flush log at transaction commit (1 = safest, 2 = faster with slight risk)
innodb_flush_log_at_trx_commit = 1

# Buffer pool instances (1 per GB of buffer pool, max 64)
innodb_buffer_pool_instances = 1

# I/O capacity — match to your disk capability
# NVMe SSD (MassiveGRID Ceph NVMe): 2000-4000
# Standard SSD: 500-1000
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# File per table (each table gets its own .ibd file)
innodb_file_per_table = 1

Query Cache

MariaDB still supports the query cache (MySQL 8.0 removed it). For read-heavy workloads, it can improve performance. For write-heavy workloads, disable it.

# Enable for read-heavy workloads (CMS, blogs, e-commerce catalogs)
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# Disable for write-heavy workloads (real-time analytics, chat apps)
# query_cache_type = 0
# query_cache_size = 0

Temporary Tables and Sorting

# Increase for complex queries with large sorts or GROUP BY
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M

Logging

# Slow query log — essential for finding performance problems
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Create the slow query log file and set permissions:

sudo touch /var/log/mysql/mariadb-slow.log
sudo chown mysql:mysql /var/log/mysql/mariadb-slow.log

Restart MariaDB to apply changes:

sudo systemctl restart mariadb

Verify the new settings:

sudo mariadb -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
sudo mariadb -e "SHOW VARIABLES LIKE 'max_connections';"

For database-heavy workloads where you need guaranteed CPU and memory resources, a Dedicated VPS (VDS) ensures your InnoDB buffer pool isn't competing with other tenants for physical RAM. This eliminates the performance variability that can occur on shared infrastructure during peak hours.

Complete Example Configuration

Here's a consolidated 50-server.cnf tuned for a 4 GB VPS running MariaDB alongside a web application:

[mysqld]
# Basic Settings
bind-address = 127.0.0.1
port = 3306
datadir = /var/lib/mysql
socket = /run/mysqld/mysqld.sock
pid-file = /run/mysqld/mysqld.pid

# Character Set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB
innodb_buffer_pool_size = 1G
innodb_log_file_size = 256M
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
innodb_buffer_pool_instances = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_file_per_table = 1

# Connections
max_connections = 150
wait_timeout = 600
interactive_timeout = 600
thread_cache_size = 16

# Query Cache (read-heavy workloads)
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# Temporary Tables
tmp_table_size = 64M
max_heap_table_size = 64M
sort_buffer_size = 4M
join_buffer_size = 4M

# Logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_error = /var/log/mysql/error.log

Automated Backups with mysqldump

Database backups are non-negotiable for production. We'll configure two approaches: logical backups with mysqldump for simplicity, and physical backups with mariadb-backup for speed on large databases.

mysqldump Backup Script

Create a backup directory:

sudo mkdir -p /var/backups/mariadb
sudo chown root:root /var/backups/mariadb
sudo chmod 700 /var/backups/mariadb

Store the backup credentials securely:

sudo nano /root/.my-backup.cnf

[client]
user=backup_user
password=BackupPass!2026#Safe

sudo chmod 600 /root/.my-backup.cnf

Create the backup script:

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

#!/bin/bash
# MariaDB automated backup script
set -euo pipefail

BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
RETENTION_DAYS=14
DEFAULTS_FILE="/root/.my-backup.cnf"

# Backup all databases
echo "[$(date)] Starting MariaDB backup..."
mysqldump --defaults-file="$DEFAULTS_FILE" \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  --quick \
  --lock-tables=false \
  | gzip > "${BACKUP_DIR}/all-databases-${DATE}.sql.gz"

# Verify the backup is not empty
BACKUP_SIZE=$(stat -f%z "${BACKUP_DIR}/all-databases-${DATE}.sql.gz" 2>/dev/null || stat -c%s "${BACKUP_DIR}/all-databases-${DATE}.sql.gz")
if [ "$BACKUP_SIZE" -lt 1000 ]; then
    echo "[$(date)] ERROR: Backup file is suspiciously small (${BACKUP_SIZE} bytes)"
    exit 1
fi

echo "[$(date)] Backup complete: all-databases-${DATE}.sql.gz ($(du -h ${BACKUP_DIR}/all-databases-${DATE}.sql.gz | cut -f1))"

# Remove old backups
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete
echo "[$(date)] Cleaned up backups older than ${RETENTION_DAYS} days"

# List current backups
echo "[$(date)] Current backups:"
ls -lh "$BACKUP_DIR"

sudo chmod +x /usr/local/bin/mariadb-backup.sh

Test the backup script:

sudo /usr/local/bin/mariadb-backup.sh

Schedule daily backups with cron:

sudo crontab -e

Add this line for daily backups at 3:00 AM:

0 3 * * * /usr/local/bin/mariadb-backup.sh >> /var/log/mariadb-backup.log 2>&1

Individual Database Backups

To back up individual databases separately (useful for selective restoration):

#!/bin/bash
# Individual database backup
BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +%Y-%m-%d_%H-%M-%S)
DEFAULTS_FILE="/root/.my-backup.cnf"

DATABASES=$(mysql --defaults-file="$DEFAULTS_FILE" -N -e "SHOW DATABASES;" | grep -Ev "(information_schema|performance_schema|mysql|sys)")

for DB in $DATABASES; do
    echo "Backing up: $DB"
    mysqldump --defaults-file="$DEFAULTS_FILE" \
      --single-transaction \
      --routines \
      --triggers \
      "$DB" | gzip > "${BACKUP_DIR}/${DB}-${DATE}.sql.gz"
done

Physical Backups with mariadb-backup

For large databases (10 GB+), mariadb-backup (Mariabackup) creates physical copies of the data files, which is much faster than logical dumps:

sudo apt install -y mariadb-backup

Create a full backup:

sudo mariabackup --backup \
  --target-dir=/var/backups/mariadb/full-backup \
  --user=backup_user \
  --password='BackupPass!2026#Safe'

Prepare the backup for restoration:

sudo mariabackup --prepare \
  --target-dir=/var/backups/mariadb/full-backup

To restore from a physical backup:

sudo systemctl stop mariadb
sudo rm -rf /var/lib/mysql/*
sudo mariabackup --copy-back \
  --target-dir=/var/backups/mariadb/full-backup
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

For a comprehensive backup strategy including off-site storage and automated verification, see our Ubuntu VPS automatic backups guide.

Setting Up Replication

MariaDB replication copies data from a primary server to one or more replicas. This provides read scaling, high availability, and geographic distribution. Here's how to set up basic primary-replica replication.

On the Primary Server

Edit the MariaDB configuration:

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

Add under [mysqld]:

# Replication - Primary
server-id = 1
log_bin = /var/log/mysql/mariadb-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800
max_binlog_size = 256M

Restart MariaDB:

sudo systemctl restart mariadb

Create a replication user:

sudo mariadb

CREATE USER 'repl_user'@'%' IDENTIFIED BY 'ReplPass!2026#Secure';
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'%';
FLUSH PRIVILEGES;

Get the current binary log position:

SHOW MASTER STATUS;

+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      663 |              |                  |
+--------------------+----------+--------------+------------------+

Note the File and Position values — you'll need them on the replica.

On the Replica Server

Install MariaDB using the same steps as the primary. Then edit the configuration:

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

Add under [mysqld]:

# Replication - Replica
server-id = 2
relay_log = /var/log/mysql/mariadb-relay-bin
read_only = 1

Restart MariaDB and configure the replication source:

sudo systemctl restart mariadb
sudo mariadb

CHANGE MASTER TO
  MASTER_HOST='primary-server-ip',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='ReplPass!2026#Secure',
  MASTER_LOG_FILE='mariadb-bin.000001',
  MASTER_LOG_POS=663;

START SLAVE;

Check replication status:

SHOW SLAVE STATUS\G

Look for these two key lines:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

Both must show Yes for replication to be working. If either shows No, check the Last_Error field for details.

Remote Access Configuration with SSL

By default, MariaDB only listens on localhost. If your application server is on a different machine, you need to enable remote access — but only with SSL encryption and strict firewall rules.

Step 1: Bind to All Interfaces

Edit the configuration:

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

Change the bind address:

# Listen on all interfaces (we'll restrict with firewall)
bind-address = 0.0.0.0

Step 2: Generate SSL Certificates

Create a directory for certificates:

sudo mkdir -p /etc/mysql/ssl
cd /etc/mysql/ssl

Generate a CA certificate:

sudo openssl genrsa 4096 > ca-key.pem
sudo openssl req -new -x509 -nodes -days 3650 -key ca-key.pem -out ca-cert.pem \
  -subj "/CN=MariaDB CA"

Generate the server certificate:

sudo openssl genrsa 4096 > server-key.pem
sudo openssl req -new -key server-key.pem -out server-req.pem \
  -subj "/CN=MariaDB Server"
sudo openssl x509 -req -in server-req.pem -days 3650 \
  -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out server-cert.pem

Generate the client certificate:

sudo openssl genrsa 4096 > client-key.pem
sudo openssl req -new -key client-key.pem -out client-req.pem \
  -subj "/CN=MariaDB Client"
sudo openssl x509 -req -in client-req.pem -days 3650 \
  -CA ca-cert.pem -CAkey ca-key.pem -CAcreateserial -out client-cert.pem

Set proper permissions:

sudo chown mysql:mysql /etc/mysql/ssl/*.pem
sudo chmod 600 /etc/mysql/ssl/*-key.pem
sudo chmod 644 /etc/mysql/ssl/*-cert.pem

Step 3: Configure MariaDB for SSL

Add to 50-server.cnf under [mysqld]:

# SSL Configuration
ssl-ca = /etc/mysql/ssl/ca-cert.pem
ssl-cert = /etc/mysql/ssl/server-cert.pem
ssl-key = /etc/mysql/ssl/server-key.pem
require_secure_transport = ON

Restart MariaDB:

sudo systemctl restart mariadb

Verify SSL is enabled:

sudo mariadb -e "SHOW VARIABLES LIKE '%ssl%';"

Step 4: Create a Remote User Requiring SSL

sudo mariadb

CREATE USER 'remote_app'@'10.0.1.%' IDENTIFIED BY 'RemotePass!2026#SSL';
GRANT ALL PRIVILEGES ON myapp_production.* TO 'remote_app'@'10.0.1.%'
  REQUIRE SSL;
FLUSH PRIVILEGES;

This creates a user that can only connect from the 10.0.1.0/24 subnet and must use SSL.

Step 5: Firewall Rules

Only allow the application server's IP to reach port 3306:

sudo ufw allow from 10.0.1.50 to any port 3306 proto tcp comment 'MariaDB from app server'
sudo ufw reload

Test the remote connection from the application server:

mariadb -h db-server-ip -u remote_app -p \
  --ssl-ca=ca-cert.pem \
  --ssl-cert=client-cert.pem \
  --ssl-key=client-key.pem \
  myapp_production

Verify the connection is encrypted:

STATUS;

Look for SSL: Cipher in use is TLS_AES_256_GCM_SHA384 (or similar) in the output.

Monitoring with SHOW STATUS and performance_schema

Regular monitoring helps you identify bottlenecks, connection issues, and slow queries before they impact users.

Key SHOW STATUS Metrics

Check connection usage:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

If Max_used_connections is close to max_connections, you need to increase the limit or investigate connection leaks.

Check InnoDB buffer pool efficiency:

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

Calculate the hit ratio:

SELECT
  (1 - (
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  )) * 100 AS buffer_pool_hit_ratio;

A hit ratio below 99% means you should increase innodb_buffer_pool_size.

Check query cache efficiency (if enabled):

SHOW STATUS LIKE 'Qcache%';

+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_hits             | 125432   |
| Qcache_inserts          | 45621    |
| Qcache_not_cached       | 12345    |
| Qcache_queries_in_cache | 2345     |
+-------------------------+----------+

If Qcache_hits is much higher than Qcache_inserts, the query cache is working well.

Slow Query Analysis

Review the slow query log:

sudo tail -50 /var/log/mysql/mariadb-slow.log

For structured analysis, use mysqldumpslow:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/mariadb-slow.log

This shows the top 10 slowest queries sorted by execution time.

Using performance_schema

Enable performance_schema in the configuration if not already active:

performance_schema = ON

Find the most time-consuming queries:

SELECT
  DIGEST_TEXT,
  COUNT_STAR AS exec_count,
  ROUND(SUM_TIMER_WAIT / 1000000000000, 2) AS total_time_sec,
  ROUND(AVG_TIMER_WAIT / 1000000000000, 4) AS avg_time_sec,
  SUM_ROWS_EXAMINED AS rows_examined,
  SUM_ROWS_SENT AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

Find tables with the most I/O:

SELECT
  OBJECT_SCHEMA,
  OBJECT_NAME,
  COUNT_READ,
  COUNT_WRITE,
  COUNT_FETCH
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
ORDER BY COUNT_READ + COUNT_WRITE DESC
LIMIT 10;

Quick Health Check Script

Create a simple monitoring script:

sudo nano /usr/local/bin/mariadb-health.sh

#!/bin/bash
# MariaDB quick health check
echo "=== MariaDB Health Check ==="
echo ""

echo "-- Uptime --"
sudo mariadb -e "SHOW STATUS LIKE 'Uptime';" | tail -1

echo ""
echo "-- Connections --"
sudo mariadb -e "
  SELECT
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Threads_connected') AS current_connections,
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Max_used_connections') AS max_used,
    (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME='max_connections') AS max_allowed;
"

echo ""
echo "-- Buffer Pool Hit Ratio --"
sudo mariadb -e "
  SELECT ROUND(
    (1 - (
      (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') /
      (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests')
    )) * 100, 2
  ) AS hit_ratio_pct;
"

echo ""
echo "-- Slow Queries --"
sudo mariadb -e "SHOW STATUS LIKE 'Slow_queries';" | tail -1

echo ""
echo "-- Database Sizes --"
sudo mariadb -e "
  SELECT
    table_schema AS database_name,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS size_mb
  FROM information_schema.tables
  WHERE table_schema NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')
  GROUP BY table_schema
  ORDER BY size_mb DESC;
"

sudo chmod +x /usr/local/bin/mariadb-health.sh

Run it anytime to get a quick overview:

sudo /usr/local/bin/mariadb-health.sh

For full-stack monitoring with dashboards and alerts, see our Ubuntu VPS monitoring guide which covers Prometheus, Grafana, and node exporters.

Common Operations Reference

Here's a quick reference for day-to-day database management tasks:

Security Best Practices

Beyond mysql_secure_installation, follow these additional security measures:

• Never use the root user for applications. Always create dedicated users with minimum required privileges.
• Use strong passwords. At least 20 characters with mixed case, numbers, and symbols.
• Keep MariaDB updated. Security patches are released regularly. Enable automatic security updates as described in our automated security updates guide.
• Restrict network access. Keep bind-address = 127.0.0.1 unless remote access is truly needed. If it is, use SSL and firewall rules.
• Audit user accounts regularly. Run SELECT User, Host FROM mysql.user; periodically and remove unused accounts.
• Protect backup files. Backups contain all your data. Ensure they're encrypted if stored off-site and have restrictive file permissions (700/600).
• Enable the slow query log. It helps identify both performance issues and unusual query patterns that might indicate SQL injection attempts.

Prefer Managed Database Hosting?

If you don't want to manage database tuning, backups, replication, security patches, and monitoring yourself, consider MassiveGRID's Managed Dedicated Cloud Servers. The managed service handles database administration alongside your full infrastructure — operating system updates, security hardening, backup management, performance tuning, and 24/7 incident response. Every managed server runs on a Proxmox HA cluster with automatic failover and Ceph triple-replicated NVMe storage, ensuring your data is safe even if hardware fails.

What's Next

• Ubuntu VPS initial setup guide — server configuration fundamentals
• Security hardening guide — firewall, SSH hardening, and Fail2Ban
• Install the LEMP stack — pair MariaDB with Nginx and PHP for WordPress, Laravel, and more
• Automated backup strategies — off-site backups, encryption, and verification
• VPS monitoring setup — dashboards and alerts for database and system metrics
• Optimize VPS performance — system-level tuning for memory, CPU, and I/O