A backup you have to remember to run is a backup that eventually does not exist. In this tutorial you will write one backup script that dumps every MySQL (or MariaDB) and PostgreSQL database on the server, checks each dump, deletes old ones and copies the result to another machine. You will then schedule it with a systemd timer on Ubuntu 24.04, check its logs and practice a restore.

Prerequisites

To follow this guide you need:

  • A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with MySQL 8.0, MariaDB or PostgreSQL 16 installed from the Ubuntu repositories. The script handles either engine or both.
  • A non-root user with sudo privileges.
  • Enough free disk space for several days of compressed dumps. Check database sizes as shown in the mysqldump and pg_dump guide.
  • Optionally, a second server reachable over SSH to receive off-site copies.

This guide builds on mysqldump and pg_dump. If you have not used them before, read the guide on database backups with mysqldump and pg_dump first.

Step 1 - Deciding how the script authenticates

The script runs as root from systemd, which keeps authentication simple on Ubuntu:

  • MySQL and MariaDB: the root account uses Unix socket authentication by default, so mysql and mysqldump run by the system root user connect without a password stored anywhere.
  • PostgreSQL: the script switches to the postgres system user with runuser, which connects through the local socket with peer authentication.

Confirm both work before writing the script:

sudo mysql -N -B -e "SELECT CURRENT_USER();"

root@localhost

sudo runuser -u postgres -- psql -Atc "SELECT current_user;"

postgres

If the MySQL command asks for a password, root was switched to password authentication. In that case create /root/.my.cnf with a [client] section containing user and password, and restrict it with sudo chmod 600 /root/.my.cnf; the MySQL client tools read it automatically.

Step 2 - Writing the backup script

Create the script in /usr/local/sbin, the standard place for administrator scripts that run as root:

sudo nano /usr/local/sbin/db-backup

Paste the following. Set ENABLE_MYSQL or ENABLE_POSTGRESQL to 0 for an engine you do not use, and leave REMOTE empty for now:

#!/usr/bin/env bash
# Dump all MySQL/MariaDB and PostgreSQL databases, verify, rotate and copy off-site.
set -euo pipefail
umask 077
cd /

BACKUP_ROOT="/var/backups/db"
RETENTION_DAYS=14
ENABLE_MYSQL=1
ENABLE_POSTGRESQL=1
REMOTE=""   # e.g. "backup@backup_host:/srv/db-backups/your_server"

STAMP="$(date +%F_%H%M)"

backup_mysql() {
    local dest="$BACKUP_ROOT/mysql/$STAMP"
    local dbs db
    mkdir -p "$dest"
    dbs="$(mysql -N -B -e 'SHOW DATABASES')"
    while IFS= read -r db; do
        case "$db" in
            information_schema|performance_schema|sys) continue ;;
        esac
        echo "mysql: dumping $db"
        mysqldump --single-transaction --routines --triggers --events \
            --databases "$db" | gzip > "$dest/$db.sql.gz"
        gzip -t "$dest/$db.sql.gz"
    done <<< "$dbs"
}

backup_postgresql() {
    local dest="$BACKUP_ROOT/postgresql/$STAMP"
    local dbs db
    mkdir -p "$dest"
    runuser -u postgres -- pg_dumpall --globals-only > "$dest/globals.sql"
    dbs="$(runuser -u postgres -- psql -At -c \
        "SELECT datname FROM pg_database WHERE datallowconn AND NOT datistemplate")"
    while IFS= read -r db; do
        echo "postgresql: dumping $db"
        runuser -u postgres -- pg_dump -Fc "$db" > "$dest/$db.dump"
        pg_restore --list "$dest/$db.dump" > /dev/null
    done <<< "$dbs"
}

if [[ "$ENABLE_MYSQL" == 1 ]]; then
    backup_mysql
fi

if [[ "$ENABLE_POSTGRESQL" == 1 ]]; then
    backup_postgresql
fi

echo "rotating backups older than $RETENTION_DAYS days"
find "$BACKUP_ROOT" -mindepth 2 -maxdepth 2 -type d -mtime +"$RETENTION_DAYS" \
    -exec rm -rf -- {} +

if [[ -n "$REMOTE" ]]; then
    echo "copying to $REMOTE"
    rsync -a --delete -e "ssh -o BatchMode=yes" "$BACKUP_ROOT"/ "$REMOTE"/
fi

echo "backup finished: $STAMP"

How it works:

  • set -euo pipefail stops the script at the first failed command, including a failed mysqldump or pg_dump inside a pipe, so a broken backup makes the whole run fail instead of silently producing an empty file.
  • umask 077 makes every file and directory it creates readable only by root.
  • Each run writes to its own directory named after the date and time, for example /var/backups/db/mysql/2026-09-25_0230/, with one file per database. Restoring a single database is then a matter of picking one file.
  • Each dump is checked right after it is written: gzip -t validates the compressed MySQL dump and pg_restore --list reads the PostgreSQL archive's table of contents.
  • PostgreSQL roles are saved in globals.sql with pg_dumpall --globals-only, because pg_dump does not include them.
  • Rotation deletes run directories older than RETENTION_DAYS. With rsync --delete, the remote copy follows the same retention.

Make the script executable:

sudo chmod 750 /usr/local/sbin/db-backup

Step 3 - Running the script manually

Run it once by hand to catch errors before scheduling it:

sudo /usr/local/sbin/db-backup

mysql: dumping appdb
mysql: dumping mysql
postgresql: dumping postgres
postgresql: dumping shopdb
rotating backups older than 14 days
backup finished: 2026-09-25_1012

Check what it produced:

sudo find /var/backups/db -type f -exec ls -lh {} +

-rw------- 1 root root  48M Sep 25 10:12 /var/backups/db/mysql/2026-09-25_1012/appdb.sql.gz
-rw------- 1 root root 1.1M Sep 25 10:12 /var/backups/db/mysql/2026-09-25_1012/mysql.sql.gz
-rw------- 1 root root 2.1K Sep 25 10:12 /var/backups/db/postgresql/2026-09-25_1012/globals.sql
-rw------- 1 root root 1.3K Sep 25 10:12 /var/backups/db/postgresql/2026-09-25_1012/postgres.dump
-rw------- 1 root root  41M Sep 25 10:12 /var/backups/db/postgresql/2026-09-25_1012/shopdb.dump

If the script stops early, the last line printed shows which database or step failed, and set -e makes it exit with a non-zero status that systemd will report as a failure.

Step 4 - Scheduling the backup with a systemd timer

A systemd timer is a better fit than cron here: runs are logged in the journal, a missed run (for example while the server was powered off) is caught up at the next boot, and the job can run with low CPU and I/O priority. Create the service unit:

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

[Unit]
Description=Database backups (MySQL/MariaDB and PostgreSQL)
Wants=network-online.target
After=network-online.target mysql.service mariadb.service postgresql.service

[Service]
Type=oneshot
Environment=HOME=/root
ExecStart=/usr/local/sbin/db-backup
Nice=10
IOSchedulingClass=idle

Type=oneshot tells systemd the job runs to completion and exits. HOME=/root is set because system services start without it, and the MySQL client needs it to find /root/.my.cnf if you use one. Nice and IOSchedulingClass=idle keep the backup from slowing down the database for its users. Listing services in After= that do not exist on your server is harmless.

Now create the timer that triggers it every night at 02:30:

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

[Unit]
Description=Run database backups nightly

[Timer]
OnCalendar=*-*-* 02:30:00
RandomizedDelaySec=10min
Persistent=true

[Install]
WantedBy=timers.target

Persistent=true runs a missed backup as soon as the server boots, and RandomizedDelaySec spreads the start time so several servers do not all hit the same backup host at once. Load the new units and enable the timer:

sudo systemctl daemon-reload
sudo systemctl enable --now db-backup.timer

Confirm the timer is scheduled:

systemctl list-timers db-backup.timer

NEXT                        LEFT     LAST PASSED UNIT            ACTIVATES
Sat 2026-09-26 02:34:12 UTC 16h left -    -      db-backup.timer db-backup.service

Trigger one run through systemd to confirm it works in that environment too, then read its log:

sudo systemctl start db-backup.service
journalctl -u db-backup.service -n 20 --no-pager

Sep 25 10:20:03 db1 systemd[1]: Starting db-backup.service - Database backups (MySQL/MariaDB and PostgreSQL)...
Sep 25 10:20:03 db1 db-backup[5121]: mysql: dumping appdb
...
Sep 25 10:20:41 db1 db-backup[5121]: backup finished: 2026-09-25_1020
Sep 25 10:20:41 db1 systemd[1]: db-backup.service: Deactivated successfully.
Sep 25 10:20:41 db1 systemd[1]: Finished db-backup.service - Database backups (MySQL/MariaDB and PostgreSQL).

If a run fails, systemctl status db-backup.service shows status=1/FAILURE and the journal shows the command that failed.

Step 5 - Copying backups to another server

Backups on the same disk as the database are lost together with it. Set up a second server (or a storage box) that accepts SSH, and give root on the database server a dedicated key to reach it.

Generate the key on the database server:

sudo ssh-keygen -t ed25519 -N "" -f /root/.ssh/id_ed25519 -C "db-backup@$(hostname)"

Print the public key:

sudo cat /root/.ssh/id_ed25519.pub

On the backup server, create a backup user, add that public key to /home/backup/.ssh/authorized_keys and create the target directory, for example /srv/db-backups/your_server, owned by backup. Then, from the database server, connect once to accept the host key and confirm the login works:

sudo ssh backup@backup_host true

Set REMOTE in the script to the target, replacing backup_host and your_server:

REMOTE="backup@backup_host:/srv/db-backups/your_server"

Run the service again and confirm the files arrive on the backup server:

sudo systemctl start db-backup.service
sudo ssh backup@backup_host ls /srv/db-backups/your_server/mysql

2026-09-25_1012
2026-09-25_1020
2026-09-25_1035

Step 6 - Testing a restore

A backup job that has never been restored is not a backup. Restore the latest dumps into temporary databases at least once now, and then regularly, for example once a month.

Find the newest run directory:

LATEST="$(sudo ls -1 /var/backups/db/mysql | tail -n 1)"
echo "$LATEST"

Restore a MySQL dump into a scratch database. The dump contains CREATE DATABASE and USE statements, so filter them out to avoid overwriting the live database:

sudo mysql -e "CREATE DATABASE appdb_restore;"
sudo bash -c "set -o pipefail; zcat /var/backups/db/mysql/$LATEST/appdb.sql.gz | grep -v -E '^(CREATE DATABASE|USE )' | mysql appdb_restore"
sudo mysql -e "SELECT COUNT(*) FROM appdb_restore.orders;"

Restore a PostgreSQL dump the same way:

sudo -u postgres createdb shopdb_restore
sudo bash -c "runuser -u postgres -- pg_restore --no-owner -d shopdb_restore < /var/backups/db/postgresql/$LATEST/shopdb.dump"
sudo -u postgres psql -d shopdb_restore -Atc "SELECT count(*) FROM orders;"

pg_restore reads the archive from standard input here because the backup files are only readable by root. Compare the row counts with the live databases, then drop the scratch copies:

sudo mysql -e "DROP DATABASE appdb_restore;"
sudo -u postgres dropdb shopdb_restore

Troubleshooting

  • mysqldump: Got error: 1045: Access denied for user 'root'@'localhost': root does not use socket authentication on this server. Create /root/.my.cnf as described in Step 1.
  • could not change directory to "/root": Permission denied: a harmless warning from psql when run from root's home. The script avoids it with cd /.
  • The timer never fires: check systemctl list-timers --all and make sure you enabled the .timer, not the .service. Run systemd-analyze calendar "*-*-* 02:30:00" to confirm the schedule.
  • Host key verification failed in the journal: root has never connected to the backup host. Run sudo ssh backup@backup_host true once and accept the key.
  • The disk fills up: lower RETENTION_DAYS, exclude large log tables, or move BACKUP_ROOT to a separate volume.

Conclusion

Your server now dumps every MySQL and PostgreSQL database each night, verifies each file, keeps two weeks of history, mirrors it to a second machine and logs every run in the journal. Next, set up an alert for failed or missing runs, schedule a monthly restore test, and for large databases consider adding physical backups with pg_basebackup or Percona XtraBackup to shorten recovery time.