MariaDB on a VPS: free, fast, controlled SQL

MariaDB is a community fork of MySQL, 100% open source, renowned for its performance, reliability and full compatibility with existing MySQL applications. Hosted on a VPS, it frees you from the restrictions of shared hosting: no imposed connection limits, no arbitrary quotas on database size, and the ability to finely tune InnoDB engine parameters according to your real load. You keep control of your backups, your users and network security, without depending on a third-party provider for every maintenance operation.

Two LTS versions are available today: MariaDB 11.4 LTS (released May 2024, support guaranteed until 2028+) and MariaDB 10.11 LTS (support until February 2028). Both are stable for production; 11.4 notably brings the definitive shift from the mysql client to mariadb, better thread parallelism and InnoDB improvements.

Before you start, make sure you have the following:

- A ServOrbit VPS: 2 GB of RAM minimum (light use or testing), 4 GB recommended in production. MariaDB itself consumes little, but the InnoDB buffer pool (innodb_buffer_pool_size) should represent 60 to 70% of available RAM to be effective — on 2 GB, less than 1.2 GB remains after the operating system.
- SSH root access or sudo to your VPS.
- A recent MariaDB version: MariaDB 11.4 LTS (May 2024, support until 2028+) or MariaDB 10.11 LTS (support until February 2028). Avoid 10.6 and below in production: they are approaching end of support.
- An active firewall (UFW or equivalent): port 3306 must never be exposed directly to the Internet.

A database without automated backups is a ticking time bomb. The following script creates a compressed nightly backup with 7-day retention.

Create the backup script at /usr/local/bin/backup-mariadb.sh:

#!/bin/bash
BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="root"
DB_PASS="your_root_password"
RETENTION=7

mkdir -p "$BACKUP_DIR"

# List all databases (excluding system ones)
DATABASES=$(mariadb -u"$DB_USER" -p"$DB_PASS" -e "SHOW DATABASES;" 2>/dev/null | \
  grep -vE "^(Database|information_schema|performance_schema|mysql|sys)$")

for DB in $DATABASES; do
  mariadb-dump \
    -u"$DB_USER" -p"$DB_PASS" \
    --single-transaction \
    --quick \
    --routines \
    --triggers \
    "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"
done

# Remove backups older than RETENTION days
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION -delete

Make the script executable and add a cron job to run it every night at 02:00:

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

Add the following line:

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

Why --single-transaction and --quick: --single-transaction takes a consistent snapshot without locking InnoDB tables (essential in production to avoid write blocks); --quick streams rows one at a time without loading them into memory, preventing RAM overflow on large tables.

To restore a database from a backup:

zcat /var/backups/mariadb/my_app_20261007_020000.sql.gz | mariadb -u root -p my_app

MariaDB's default configuration is very conservative (only a few tens of MB for innodb_buffer_pool_size). On a production VPS, tune InnoDB parameters to make full use of available memory.

Create the file /etc/mysql/conf.d/performance.cnf:

[mysqld]
# InnoDB buffer pool: 60-70% of total RAM
# On 4 GB RAM → 2.5 GB
innodb_buffer_pool_size = 2560M

# Log file size (recommended: 25% of pool)
innodb_log_file_size = 640M

# Number of log files (default 2, sufficient)
innodb_log_files_in_group = 2

# Query cache disabled on MariaDB 10.3+ (measured degradation)
query_cache_size = 0
query_cache_type = 0

# Simultaneous connections (adjust to real load)
max_connections = 150

# Slow query log (identify queries to optimise)
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /var/log/mysql/mariadb-slow.log

Adjust innodb_buffer_pool_size according to your VPS RAM: on 2 GB, use 1.2 GB (1228M); on 8 GB, use 5.5 GB (5632M).

Then restart MariaDB to apply the configuration:

systemctl restart mariadb

Verify the parameters were applied:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'query_cache_size';

By default, MariaDB only listens on 127.0.0.1 — this is the secure default. For access from an application on another server, two approaches exist.

Option 1 — SSH tunnel (recommended, without opening port 3306)

From your local machine or application server:

ssh -L 3306:127.0.0.1:3306 -N root@<mariadb-vps-ip>

Your application then connects to 127.0.0.1:3306 locally — the SSH tunnel encrypts the traffic. Add -f to put the tunnel in the background.

Option 2 — Inter-VPS connection with IP allowlisting

If your application runs on another ServOrbit VPS (e.g., a separate application VPS from the database VPS), open the port only for that server's private IP:

# In /etc/mysql/conf.d/network.cnf
[mysqld]
bind-address = 0.0.0.0

# Allow only the application server IP
ufw allow from <app-ip> to any port 3306

Create the MariaDB user limited to the application server's IP:

CREATE USER 'app'@'<app-ip>' IDENTIFIED BY 'strong_password';
GRANT ALL PRIVILEGES ON my_app.* TO 'app'@'<app-ip>';
FLUSH PRIVILEGES;

Never create a 'user'@'%' account (wildcard host): this allows connections from any IP — if port 3306 were accidentally opened, the account would be directly accessible from the Internet.

Here are the four most common errors when running MariaDB on a VPS, and how to resolve them.

1. Can't connect to local MariaDB server through socket

The MariaDB service is not running or the socket is missing.

systemctl status mariadb
systemctl start mariadb

If the service refuses to start, read the logs:

journalctl -xe -u mariadb --no-pager | tail -40

Common cause: innodb_buffer_pool_size is too large for the available RAM — MariaDB stops immediately without an obvious message. Reduce the value in /etc/mysql/conf.d/performance.cnf.

2. Too many connections

The number of simultaneous connections has reached the limit. Increase max_connections in /etc/mysql/conf.d/performance.cnf then reload:

systemctl reload mariadb

Inspect active connections before increasing: SHOW PROCESSLIST; in the MariaDB shell. Hundreds of idle connections usually indicate a misconfigured connection pool on the application side.

3. Table '<name>' is marked as crashed

A MyISAM table is corrupt (system crash, power outage). Repair it:

mysqlcheck -u root -p --repair my_app table_name

If you use InnoDB (the general case), this error is rare; InnoDB corruption is resolved by restoring a backup — another reason to enable automated backups.

4. Access denied for user 'root'@'localhost'

Forgotten root password or modified authentication plugin. Reset in skip-grant mode:

systemctl stop mariadb
mysqld_safe --skip-grant-tables &
mariadb -u root

Then in the MariaDB shell:

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
EXIT;

Restart MariaDB normally: systemctl restart mariadb.