Install MariaDB and Create Secure Backups on Ubuntu

Databases fail at the worst moments: a rushed deploy, a disk at 100%, a compromised WordPress plugin, or the wrong SQL on production. This guide shows you how to backup MariaDB on Ubuntu using automated dumps, sensible retention, encryption, and a restore test you can trust.

The steps assume an Ubuntu VPS (22.04/24.04 LTS) running a real stack, like WordPress, Laravel, Node.js, or a custom API.

If this is production, start with a VPS that has enough IOPS and storage headroom for both the database and the backups. HostMyCode VPS is a solid baseline.

If you don’t want backups to depend on “who remembered the cron job,” managed VPS hosting is the safer route.

What you’ll build (and what you’ll verify)

• MariaDB installed and locked down on Ubuntu
• Nightly logical backups using mariadb-dump (consistent, single-file per database)
• Retention policy (daily + weekly) with automatic pruning
• Encryption at rest for backup files (GPG symmetric encryption)
• Optional offsite copy (SFTP/rsync) to a second server
• A tested restore workflow into a separate database (the part most people skip)

Prerequisites

• Ubuntu server with sudo (root also works)
• At least 2–3x your database size in free disk (temporary + retention)
• Outbound network access if you’ll copy backups offsite

Before you change anything, confirm what you’re working with:

lsb_release -a
uname -r
df -h /

Install MariaDB on Ubuntu (repo packages)

Ubuntu’s repo packages are usually the right choice on a VPS. They’re stable, get security patches quickly, and are easy to maintain.

Install the server and client tools:

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

Enable and start MariaDB:

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

Check the installed version:

mariadb --version

Quick socket sanity check:

sudo mariadb -e "SELECT VERSION();"

Secure MariaDB (minimum hardening)

Start with the built-in hardening tool:

sudo mariadb-secure-installation

For most VPS setups, these answers are sensible defaults:

• Set a root password: Yes
• Switch to unix_socket authentication: No if you want password-based root; Yes if you only administer via sudo on the server. Pick one and document it.
• Remove anonymous users: Yes
• Disallow remote root login: Yes
• Remove test database: Yes
• Reload privilege tables: Yes

Common mistake: opening MariaDB to the internet “just for debugging.” If your app runs on the same VPS, keep MariaDB bound to localhost.

Only expose it for a specific, documented reason.

Confirm what’s listening, and where it’s bound:

sudo ss -lntp | grep 3306 || true
sudo grep -R "bind-address" -n /etc/mysql/ /etc/mysql/mariadb.conf.d/ 2>/dev/null || true

On Ubuntu, MariaDB config typically lives under /etc/mysql/mariadb.conf.d/.

If you see 0.0.0.0 and you don’t need remote access, set:

# /etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 127.0.0.1

Restart MariaDB:

sudo systemctl restart mariadb

Create a dedicated backup user (least privilege)

Don’t run backups with full root credentials. Use a dedicated account that can read what it needs for reliable logical dumps.

Open a root SQL shell (use sudo if unix_socket auth is enabled):

sudo mariadb

Create the backup user. Replace STRONG_LONG_PASSWORD:

CREATE USER 'backup'@'localhost' IDENTIFIED BY 'STRONG_LONG_PASSWORD';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES ON *.* TO 'backup'@'localhost';
GRANT RELOAD, PROCESS ON *.* TO 'backup'@'localhost';
FLUSH PRIVILEGES;

Exit the shell:

EXIT;

Confirm the account works:

mariadb -ubackup -p -e "SHOW DATABASES;"

Why these grants? A good dump needs read access to tables and views. It also needs triggers/events/routines.

Depending on engine and settings, dumps may take brief locks. They may also need process-level visibility for consistent behavior across schemas.

Install backup tooling (dump + compression + encryption)

Install the small utilities the backup script will call:

sudo apt install -y gzip coreutils gpg rsync

You’ll use:

• mariadb-dump (installed with the client package)
• gzip for compression
• gpg for encryption at rest
• rsync for optional offsite copy

Set up a secure backup directory

Create a root-only directory for backups.

Treat these files as production secrets. Dumps often contain customer data, sessions, and API tokens.

sudo mkdir -p /var/backups/mariadb/{daily,weekly}
sudo chown -R root:root /var/backups/mariadb
sudo chmod -R 700 /var/backups/mariadb

Double-check permissions:

sudo ls -ld /var/backups/mariadb /var/backups/mariadb/daily

Store credentials safely (without putting passwords in scripts)

Use a MariaDB option file readable only by root.

Create /root/.my.cnf:

sudo nano /root/.my.cnf

Add:

[client]
user=backup
password=STRONG_LONG_PASSWORD
host=localhost

Lock it down:

sudo chown root:root /root/.my.cnf
sudo chmod 600 /root/.my.cnf

Verify that mariadb connects without prompting:

sudo mariadb -e "SELECT 1;"

Common mistake: using -pPASSWORD in cron jobs. It can leak into shell history and show up in process listings.

Create the backup script (daily dumps + retention + encryption)

Create /usr/local/sbin/mariadb-backup.sh:

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

Paste the script below.

Then adjust the variables at the top to match your retention and paths.

#!/usr/bin/env bash
set -euo pipefail

# ---- settings ----
BACKUP_ROOT="/var/backups/mariadb"
DAILY_DIR="$BACKUP_ROOT/daily"
WEEKLY_DIR="$BACKUP_ROOT/weekly"
KEEP_DAYS=14
KEEP_WEEKS=8

# Encrypt backups at rest with a passphrase.
# Generate one: openssl rand -base64 48
GPG_PASSPHRASE_FILE="/root/.backup-gpg-passphrase"

# Dump options: safe defaults for most apps
DUMP_OPTS=(
  --single-transaction
  --routines
  --triggers
  --events
  --hex-blob
  --set-gtid-purged=OFF
)

umask 077

log() { echo "[$(date -Is)] $*"; }

die() { echo "ERROR: $*" 1>&2; exit 1; }

require() { command -v "$1" >/dev/null 2>&1 || die "Missing command: $1"; }

require mariadb
require mariadb-dump
require gzip
require gpg

[ -r "$GPG_PASSPHRASE_FILE" ] || die "Passphrase file missing or unreadable: $GPG_PASSPHRASE_FILE"

mkdir -p "$DAILY_DIR" "$WEEKLY_DIR"

STAMP="$(date +%F_%H%M%S)"
HOST="$(hostname -s)"

# Get DB list; exclude system schemas
DBS=$(mariadb -N -e "SHOW DATABASES" | egrep -v "^(information_schema|performance_schema|mysql|sys)$")

[ -n "$DBS" ] || die "No user databases found to back up"

log "Starting MariaDB backups on $HOST"

for db in $DBS; do
  out="$DAILY_DIR/${HOST}_${db}_${STAMP}.sql.gz"
  enc="$out.gpg"

  log "Dumping database: $db"
  mariadb-dump "${DUMP_OPTS[@]}" "$db" | gzip -c > "$out"

  log "Encrypting: $(basename "$out")"
  gpg --batch --yes --symmetric --cipher-algo AES256 \
    --pinentry-mode loopback --passphrase-file "$GPG_PASSPHRASE_FILE" \
    -o "$enc" "$out"

  rm -f "$out"

done

# Weekly snapshot every Sunday (copy daily encrypted files into weekly)
DOW=$(date +%u) # 7 = Sunday
if [ "$DOW" -eq 7 ]; then
  log "Weekly rotation: copying today’s encrypted dumps into weekly"
  find "$DAILY_DIR" -maxdepth 1 -type f -name "*_${STAMP%_*}*.sql.gz.gpg" -print0 2>/dev/null | \
    xargs -0 -I{} cp -a {} "$WEEKLY_DIR/" || true
fi

log "Pruning daily backups older than $KEEP_DAYS days"
find "$DAILY_DIR" -type f -name "*.sql.gz.gpg" -mtime +"$KEEP_DAYS" -delete

log "Pruning weekly backups older than $KEEP_WEEKS weeks"
find "$WEEKLY_DIR" -type f -name "*.sql.gz.gpg" -mtime +"$((KEEP_WEEKS*7))" -delete

log "Backup run complete"

Make it executable:

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

Create the encryption passphrase file

Generate a strong passphrase and store it in a root-only file:

sudo bash -c 'openssl rand -base64 48 > /root/.backup-gpg-passphrase'
sudo chown root:root /root/.backup-gpg-passphrase
sudo chmod 600 /root/.backup-gpg-passphrase

Important: if you lose this passphrase, your backups are unreadable. Store it in a password manager and in your incident runbook.

Run a manual backup and verify output

Run the script once manually. This lets you see exactly what it produces.

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

List the newest files:

sudo ls -lh /var/backups/mariadb/daily | tail -n 10

You should see files like:

• yourhost_appdb_2026-10-21_021500.sql.gz.gpg

Quick integrity check: decrypt to stdout and confirm you get SQL output:

sudo gpg --batch --yes --pinentry-mode loopback \
  --passphrase-file /root/.backup-gpg-passphrase \
  -d /var/backups/mariadb/daily/*.sql.gz.gpg | head

You should see SQL comments and CREATE TABLE statements.

Schedule it with a systemd timer (logged, reliable)

Cron can work.

systemd timers are usually easier to inspect and troubleshoot.

Create a service unit:

sudo nano /etc/systemd/system/mariadb-backup.service

[Unit]
Description=MariaDB logical backups (encrypted)
Wants=network-online.target
After=network-online.target mariadb.service

[Service]
Type=oneshot
ExecStart=/usr/local/sbin/mariadb-backup.sh
Nice=10
IOSchedulingClass=best-effort
IOSchedulingPriority=7

Create the timer:

sudo nano /etc/systemd/system/mariadb-backup.timer

[Unit]
Description=Run MariaDB backups nightly

[Timer]
OnCalendar=*-*-* 02:15:00
Persistent=true
RandomizedDelaySec=10m

[Install]
WantedBy=timers.target

Enable it:

sudo systemctl daemon-reload
sudo systemctl enable --now mariadb-backup.timer
sudo systemctl list-timers --all | grep mariadb-backup

After the first run, check the logs:

sudo journalctl -u mariadb-backup.service -n 200 --no-pager

If you prefer cron, HostMyCode also has a practical guide to timers that maps well to hosting workloads: systemd timer setup guide.

Optional: copy encrypted backups offsite (recommended)

If backups live on the same disk as the database, you’re still one incident away from losing both.

Offsite copies protect you from disk failure, host-level compromise, and ransomware.

A simple approach is to push encrypted files to a second VPS over SSH.

If you don’t have a destination yet, a small storage VPS (modest CPU, more disk) is fine. HostMyCode VPS works well for this role.

On the backup target server, create a restricted user:

sudo adduser --disabled-password --gecos "" backupstore
sudo mkdir -p /srv/backupstore/mariadb
sudo chown -R backupstore:backupstore /srv/backupstore/backupstore 2>/dev/null || true
sudo chown -R backupstore:backupstore /srv/backupstore

On the database server, create an SSH key.

No passphrase can be acceptable if you protect the private key file properly. A passphrase is better if you can support it operationally:

sudo ssh-keygen -t ed25519 -f /root/.ssh/id_ed25519_backup -C "mariadb-backups"

Copy the public key to the backup target:

sudo ssh-copy-id -i /root/.ssh/id_ed25519_backup.pub backupstore@BACKUP_TARGET_IP

Test login:

sudo ssh -i /root/.ssh/id_ed25519_backup backupstore@BACKUP_TARGET_IP "ls -la /srv/backupstore/mariadb"

Create a sync script:

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

#!/usr/bin/env bash
set -euo pipefail

SRC="/var/backups/mariadb/"
DST_USER="backupstore"
DST_HOST="BACKUP_TARGET_IP"
DST_PATH="/srv/backupstore/mariadb/"
KEY="/root/.ssh/id_ed25519_backup"

rsync -a --delete \
  -e "ssh -i $KEY -o StrictHostKeyChecking=accept-new" \
  "$SRC" "$DST_USER@$DST_HOST:$DST_PATH"

Run this after backups finish (for example, via a second timer at 02:45), or call it at the end of the main script.

Keeping it separate is usually cleaner. The backup job still succeeds even if the network is down.

Verified restore workflow (the step that makes backups real)

A backup you’ve never restored is just a file you hope is usable.

Do the first restore test on the same server, but restore into a separate database.

In production, repeating the test on a staging VPS is even better. It also validates application startup.

Pick one encrypted backup file from /var/backups/mariadb/daily:

sudo ls -1 /var/backups/mariadb/daily/*.sql.gz.gpg | tail -n 1

Set a variable:

BACKUP_FILE="/var/backups/mariadb/daily/PASTE_FILENAME_HERE.sql.gz.gpg"

Create a restore target DB (example: restore appdb into appdb_restore_test):

sudo mariadb -e "CREATE DATABASE IF NOT EXISTS appdb_restore_test;"

Decrypt, decompress, and import. This restores SQL into the test database by rewriting the USE statement.

It won’t fit every dump pattern, but it works well for typical single-DB dumps.

sudo bash -c '
  gpg --batch --yes --pinentry-mode loopback \
    --passphrase-file /root/.backup-gpg-passphrase \
    -d "$BACKUP_FILE" \
  | gunzip -c \
  | sed "s/^USE `[^`]*`;/USE `appdb_restore_test`;/" \
  | mariadb appdb_restore_test
'

Verify import success with a quick table check and a count:

sudo mariadb -N -e "SHOW TABLES" appdb_restore_test | head
sudo mariadb -e "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='appdb_restore_test';"

If this is WordPress, confirm the expected tables exist:

sudo mariadb -e "SHOW TABLES LIKE 'wp_%';" appdb_restore_test

Common restore failures and fixes:

• "Access denied" during import: you’re importing as a limited user. Run the restore using root via sudo mariadb.
• "Unknown collation": your dump came from a newer MariaDB/MySQL version. Restore on a matching or newer version.
• "ERROR 1044" (privileges): the dump includes DEFINERs or views. Keep --routines --triggers and import as a privileged user.

If you want a wider recovery plan that includes DNS cutover and clean rebuilds, keep this runbook nearby: disaster recovery tutorial (2026).

How to confirm backups are consistent (quick checks)

Logical dumps can “succeed” and still be unusable.

You can end up with missing rows, broken views, or an incomplete export.

Keep a small backup health checklist and use it:

• Backup file exists for every user DB (not just one big dump you never checked)
• Backup file size is within a reasonable range vs yesterday (watch for sudden 90% drops)
• Decryption works (wrong passphrase shows up immediately)
• Restore test imports without errors at least weekly

Example size comparison (top 20 newest backups):

sudo find /var/backups/mariadb/daily -type f -name '*.gpg' -printf '%TY-%Tm-%Td %TH:%TM %s %p\n' | sort -r | head -n 20

Hardening extras that matter on a hosting VPS

Backups add files, keys, and schedules.

Each one is another thing you must protect.

Before you call the setup “done,” tighten these basics:

• Firewall: keep port 3306 closed publicly. If you need a baseline guide, follow Set up UFW firewall on Ubuntu.
• Disk space monitoring: retention fills disks quietly until it doesn’t. Add alerts. See server monitoring setup guide.
• Protect your offsite target: treat it like production. If an attacker can delete your backups, encryption doesn’t help.

Common mistakes (and how to avoid them)

• Backing up to the same filesystem as the DB with no offsite copy: a single disk failure wipes both.
• Using only “all databases” dumps: you lose clarity and make restores slower. Per-DB dumps simplify recovery.
• Never running a restore test: permissions, collation mismatches, and missing routines usually show up only during restore.
• No retention pruning: backups quietly grow until the VPS hits 100% disk. Then MariaDB and your web server start failing.

If disk is already tight, fix that first.

This guide helps you reclaim space without guesswork: disk space troubleshooting tutorial.

Summary: your minimum “production ready” backup baseline

• Nightly encrypted dumps stored under /var/backups/mariadb
• Retention set (e.g., 14 daily + 8 weekly)
• Backups copied offsite at least once per day
• A scheduled restore test (weekly or after major upgrades)
• Alerts for disk usage and backup job failures

If you’re running customer sites or revenue-driving applications, plan for backup and restore overhead.

That means enough CPU for dumps, enough disk for retention, and enough I/O headroom for recovery tests.

Use a HostMyCode VPS, or choose managed VPS hosting if you want patching, monitoring, and backup discipline handled continuously—not “after the next incident.”

FAQ

How often should I backup MariaDB on Ubuntu?

Nightly is a good default for most sites. If you take orders or accept user-generated content, consider hourly backups for the critical database and keep a shorter hourly retention.

Is mariadb-dump enough, or do I need physical backups?

Logical dumps are portable and easy to restore, which makes them a good fit for small to medium databases.

If your database is large enough that restore time becomes the bottleneck, add physical backups (for example, Mariabackup) in addition to this, not as a replacement.

Can I restore onto a new VPS during an outage?

Yes. Copy the encrypted dump plus the GPG passphrase (securely), install MariaDB, create the target database, then decrypt and import.

Pair that with a DNS cutover plan if you’re also moving the app endpoint.

Why encrypt if I’m already using a private server?

Because backups rarely stay in one place. They get synced, copied, and stored for retention.

Encryption protects you if the offsite server is compromised, a storage snapshot is exposed, or the wrong person downloads an archive.