---
title: "How to Back Up MySQL and PostgreSQL Databases on Ubuntu"
description: "Back up MySQL and PostgreSQL databases with mysqldump and pg_dump, automate daily dumps with a systemd timer, and test that every backup restores correctly."
category: "Backup Recovery"
language: en
source: https://cubepath.com/docs/backup-recovery/mysql-postgresql-database-backup
updated: 2026-09-25
---

# How to Back Up MySQL and PostgreSQL Databases on Ubuntu

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.

> Note: On Ubuntu, the MySQL and MariaDB `root` account authenticates through the Unix socket, so `sudo mysql` and `sudo mysqldump` work without a password. The PostgreSQL superuser is the `postgres` system account, reached with `sudo -u postgres`. This tutorial relies on both, so no database passwords are stored on disk.

## 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:

```bash
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:

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

```output
-- 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.

> Warning: `--single-transaction` only gives a consistent snapshot for InnoDB tables. If you still have MyISAM tables, use `--lock-tables` instead, which blocks writes to each database while it is dumped. List MyISAM tables with `sudo mysql -e "SELECT table_schema, table_name FROM information_schema.tables WHERE engine = 'MyISAM' AND table_schema NOT IN ('mysql', 'sys')"`.

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:

```bash
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:

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

```output
;
; 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`:

```bash
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:

```bash
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:

```bash
#!/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:

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

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

List the result and verify the checksums:

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

```output
-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:

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

```ini
[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:

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

```ini
[Unit]
Description=Daily database backup

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

[Install]
WantedBy=timers.target
```

Reload systemd and enable the timer:

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

Confirm that the timer is scheduled:

```bash
systemctl list-timers db-backup.timer
```

```output
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:

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

```output
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:

```bash
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`:

```bash
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:

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

```output
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:

```bash
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:

```bash
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:

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

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

Drop the test database when you are done:

```bash
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.