A database cannot be backed up safely by copying its data directory while the server is running: the files change mid-copy and the result may not start. Logical dumps made with mysqldump and pg_dump solve this by exporting a consistent snapshot while the database keeps serving traffic. In this tutorial you will dump MySQL and PostgreSQL databases by hand, turn that into a daily backup job driven by a systemd timer, and prove the dumps work by restoring them into a scratch database on Ubuntu 24.04.

Prerequisites

To follow this tutorial, you will need:

  • A server running Ubuntu 24.04 LTS, for example a CubePath VPS, with a non-root user that has sudo privileges.
  • MySQL 8.0 (mysql-server), MariaDB 10.11 (mariadb-server), PostgreSQL 16 (postgresql), or a combination of them, installed from the Ubuntu repositories and holding at least one database.
  • Free disk space for the dumps. A compressed dump is usually 10 to 30 percent of the database size on disk; check it with df -h /var/backups.

Throughout the guide, replace your_database with the name of one of your databases.

Step 1 - Dumping a MySQL or MariaDB database

mysqldump exports a database as SQL statements. For InnoDB tables, the --single-transaction option runs the whole dump inside one transaction, so you get a consistent snapshot without locking the tables. The other options add stored routines, triggers and scheduled events, which are not included by default, and --quick streams rows instead of buffering whole tables in memory:

sudo mysqldump --single-transaction --quick --routines --triggers --events your_database | gzip > ~/your_database.sql.gz

A dump that finished cleanly always ends with a completion comment. Check the first and last lines:

zcat ~/your_database.sql.gz | head -n 1
zcat ~/your_database.sql.gz | tail -n 1

-- MySQL dump 10.13  Distrib 8.0.43, for Linux (x86_64)
-- Dump completed on 2026-09-25 10:12:03

On MariaDB the first line reads -- MariaDB dump. If the Dump completed line is missing, the dump was interrupted and must not be trusted.

Because the dump was made without --databases, it contains no CREATE DATABASE or USE statement. That lets you restore it into a database of any name, which you will use in Step 5.

Step 2 - Dumping a PostgreSQL database

pg_dump always takes a consistent snapshot, whatever the table type, and never blocks normal reads and writes. Use the custom format (-Fc): it is compressed, and pg_restore can restore a single table or schema from it later:

sudo -u postgres pg_dump -Fc your_database > ~/your_database.dump

The redirection runs as your user, so the file ends up in your home directory. pg_restore --list reads the archive's table of contents without touching the server, which is a quick way to confirm the file is a valid dump:

pg_restore --list ~/your_database.dump | head -n 8

;
; Archive created at 2026-09-25 10:15:41 UTC
;     dbname: your_database
;     TOC Entries: 48
;     Compression: gzip
;     Dump Version: 1.15-0
;     Format: CUSTOM
;     Integer: 4 bytes

pg_dump does not include roles (users) or their passwords, because they belong to the cluster rather than to one database. Dump them separately with pg_dumpall:

sudo -u postgres pg_dumpall --globals-only > ~/pg-globals.sql

This file contains password hashes, so treat it like any other secret.

Step 3 - Writing the backup script

Manual dumps are fine once; for daily backups you want a script that dumps every database, writes the files atomically, records checksums and deletes old backups. Create the script:

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

Paste the following content. It backs up MySQL/MariaDB if mysqldump is installed and PostgreSQL if pg_dump is installed, so the same script works on a server that runs either or both:

#!/usr/bin/env bash
# Dump every MySQL/MariaDB and PostgreSQL database into a dated directory.
set -euo pipefail
umask 077

BACKUP_ROOT="/var/backups/db"
RETENTION_DAYS=14
DEST="$BACKUP_ROOT/$(date +%Y%m%d-%H%M%S)"

mkdir -p "$DEST"
cd /

if command -v mysqldump >/dev/null 2>&1; then
    mysql_dbs=$(mysql -N -B -e "SELECT schema_name FROM information_schema.schemata
        WHERE schema_name NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys')")
    while read -r db; do
        [ -n "$db" ] || continue
        mysqldump --single-transaction --quick --routines --triggers --events "$db" \
            | gzip > "$DEST/mysql-$db.sql.gz.part"
        mv "$DEST/mysql-$db.sql.gz.part" "$DEST/mysql-$db.sql.gz"
    done <<< "$mysql_dbs"
fi

if command -v pg_dump >/dev/null 2>&1; then
    runuser -u postgres -- pg_dumpall --globals-only | gzip > "$DEST/pg-globals.sql.gz"
    pg_dbs=$(runuser -u postgres -- psql -At -c \
        "SELECT datname FROM pg_database WHERE NOT datistemplate AND datname <> 'postgres'")
    while read -r db; do
        [ -n "$db" ] || continue
        runuser -u postgres -- pg_dump -Fc "$db" > "$DEST/pg-$db.dump.part"
        mv "$DEST/pg-$db.dump.part" "$DEST/pg-$db.dump"
    done <<< "$pg_dbs"
fi

(cd "$DEST" && sha256sum -- * > SHA256SUMS)

find "$BACKUP_ROOT" -mindepth 1 -maxdepth 1 -type d -mtime +"$RETENTION_DAYS" -exec rm -rf -- {} +

echo "Backup written to $DEST"

A few details are worth understanding:

  • set -euo pipefail makes the script stop at the first failure, including a failed mysqldump in the middle of a | gzip pipeline, so a broken dump makes the whole job fail instead of leaving a silent, truncated file.
  • Each dump is written to a .part file and renamed only when it is complete, so a file without .part is always a finished dump.
  • umask 077 makes every backup readable by root only. Dumps contain all your data, including password hashes.
  • SHA256SUMS lets you detect later corruption, for example after copying the backups to another server.
  • Directories older than RETENTION_DAYS days are removed. Adjust the value to your disk space and recovery needs.

The script skips the mysql system schema. User accounts and grants are therefore not part of the MySQL backup; keep the CREATE USER and GRANT statements for your application users in your deployment notes, or dump them with SHOW GRANTS.

Make the script executable and run it once by hand:

sudo chmod 700 /usr/local/bin/db-backup.sh
sudo /usr/local/bin/db-backup.sh

Backup written to /var/backups/db/20260925-102211

List the result and verify the checksums:

sudo ls -lh /var/backups/db/20260925-102211
sudo sh -c 'cd /var/backups/db/20260925-102211 && sha256sum -c SHA256SUMS'

-rw------- 1 root root  812 Sep 25 10:22 SHA256SUMS
-rw------- 1 root root 4.1M Sep 25 10:22 mysql-your_database.sql.gz
-rw------- 1 root root  612 Sep 25 10:22 pg-globals.sql.gz
-rw------- 1 root root 2.3M Sep 25 10:22 pg-your_database.dump
mysql-your_database.sql.gz: OK
pg-globals.sql.gz: OK
pg-your_database.dump: OK

Step 4 - Scheduling daily backups with a systemd timer

A systemd timer is preferable to cron here: the output goes to the journal, a failed run shows up in systemctl --failed, and Persistent=true runs a missed backup as soon as the server boots again. Create the service unit:

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

[Unit]
Description=Dump MySQL and PostgreSQL databases
After=mysql.service mariadb.service postgresql.service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/db-backup.sh
Nice=10
IOSchedulingClass=idle

Nice and IOSchedulingClass=idle lower the priority of the job so that the dump yields CPU and disk time to the running databases. After= lines that name a service you do not have installed are simply ignored.

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

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

[Unit]
Description=Daily database backup

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

[Install]
WantedBy=timers.target

Reload systemd and enable the timer:

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

Confirm that the timer is scheduled:

systemctl list-timers db-backup.timer

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

You can trigger a run at any time and read its log:

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

Sep 25 10:31:02 server systemd[1]: Starting db-backup.service - Dump MySQL and PostgreSQL databases...
Sep 25 10:31:09 server db-backup.sh[48211]: Backup written to /var/backups/db/20260925-103102
Sep 25 10:31:09 server systemd[1]: db-backup.service: Deactivated successfully.
Sep 25 10:31:09 server systemd[1]: Finished db-backup.service - Dump MySQL and PostgreSQL databases.

Step 5 - Testing a restore

A backup is only proven once it has been restored. Restore into a separate database so you never touch production data. Pick the most recent backup directory:

sudo ls /var/backups/db

Restoring a MySQL dump

Create an empty database and load the dump into it. The file is readable only by root, so zcat also runs with sudo:

sudo mysql -e "CREATE DATABASE restore_test"
sudo zcat /var/backups/db/20260925-103102/mysql-your_database.sql.gz | sudo mysql restore_test

Compare the number of tables in the original and the restored database:

sudo mysql -N -e "SELECT table_schema, COUNT(*) FROM information_schema.tables WHERE table_schema IN ('your_database', 'restore_test') GROUP BY table_schema"

restore_test	23
your_database	23

For a stronger check, run SELECT COUNT(*) on your most important tables in both databases. Then remove the test copy:

sudo mysql -e "DROP DATABASE restore_test"

To restore over the real database after an incident, load the same file into your_database instead. The dump drops and recreates each table it contains.

Restoring a PostgreSQL dump

Create an empty database owned by postgres and restore into it. pg_restore reads the archive from standard input here, because the postgres user cannot open files in the root-only backup directory:

sudo -u postgres createdb restore_test
sudo cat /var/backups/db/20260925-103102/pg-your_database.dump | sudo -u postgres pg_restore --no-owner -d restore_test

--no-owner makes postgres the owner of every restored object, which avoids errors if the original owner role does not exist yet. Check that the tables are there:

sudo -u postgres psql -d restore_test -c "\dt"

             List of relations
 Schema |    Name     | Type  |  Owner
--------+-------------+-------+----------
 public | customers   | table | postgres
 public | orders      | table | postgres
...

Drop the test database when you are done:

sudo -u postgres dropdb restore_test

For a real recovery on a new server, restore the roles first with sudo zcat pg-globals.sql.gz | sudo -u postgres psql, create the database with the right owner (sudo -u postgres createdb -O app_user your_database), and run pg_restore without --no-owner so the original ownership is kept.

Troubleshooting

  • mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces: you ran mysqldump as a MySQL user other than root. Grant that user PROCESS, or add --no-tablespaces if you do not use general tablespaces.
  • pg_dump: error: connection to server on socket ... failed: FATAL: Peer authentication failed: you connected as a PostgreSQL role that does not match your Linux user. Run the command through sudo -u postgres as shown above.
  • could not change directory to "/home/your_user": Permission denied: a harmless warning from running a PostgreSQL tool as postgres from your home directory. Run it from /tmp or ignore it.
  • The service fails with status=1/FAILURE: read journalctl -u db-backup.service. The last line before the failure names the database or command that broke, thanks to set -e.

Conclusion

You now have consistent MySQL and PostgreSQL dumps created every night, protected with root-only permissions and checksums, rotated automatically, and verified by restoring them into a scratch database. Backups that live only on the database server do not survive the loss of that server, so the next step is to copy /var/backups/db somewhere else. From here you can:

  • Send the backups off the server with rclone to S3-compatible object storage.
  • Automate the restore test from Step 5 on a schedule, so a broken backup is detected before you need it.
  • For large databases or recovery to an exact point in time, look at physical backups: Percona XtraBackup with binary logs for MySQL, or pg_basebackup with WAL archiving (or pgBackRest) for PostgreSQL.