mysqldump and pg_dump create logical backups: files containing the SQL (or an archive of it) needed to rebuild a database from scratch. They are the simplest way to back up small and medium databases, move a database to another server or keep a copy before a risky migration. In this tutorial you will create consistent backups of a MySQL or MariaDB database with mysqldump and of a PostgreSQL database with pg_dump, store credentials safely, restore the backups into a new database and verify them.
Prerequisites
To follow this guide you need:
- A server running Ubuntu 24.04 LTS, for example a CubePath VPS.
- A non-root user with
sudoprivileges. - MySQL 8.0 (
mysql-server), MariaDB (mariadb-server) or PostgreSQL 16 (postgresql) installed from the Ubuntu repositories, with at least one database to back up. - Free disk space for the dumps. A compressed dump is typically 10 to 30% of the database size.
The examples use a database called appdb. Replace it with your own database name.
Step 1 - Preparing a backup directory
Keep dumps in a directory that only root can read, because they contain all of your data:
sudo install -d -m 0700 /var/backups/mysql
sudo install -d -m 0700 -o postgres -g postgres /var/backups/postgresql
The PostgreSQL directory is owned by the postgres system user because pg_dump will run as that user. Check the size of your databases so you know how much space you need.
For MySQL or MariaDB:
sudo mysql -e "SELECT table_schema AS db, ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) AS size_mb FROM information_schema.tables GROUP BY table_schema;"
For PostgreSQL:
sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database WHERE NOT datistemplate;"
Compare the result with the free space on the disk:
df -h /var/backups
Step 2 - Backing up MySQL and MariaDB with mysqldump
On Ubuntu, the MySQL and MariaDB root account authenticates through the Unix socket, so sudo mysqldump works without a password. Create a compressed backup of appdb:
sudo bash -c 'set -o pipefail; mysqldump --single-transaction --routines --triggers --events --databases appdb | gzip > /var/backups/mysql/appdb-$(date +%F).sql.gz'
The command runs inside bash -c so that both the dump and the redirect run as root, and pipefail makes the command fail if mysqldump fails even though gzip succeeds. The options matter:
--single-transactiontakes a consistent snapshot of InnoDB tables without locking them, so the application keeps working during the backup. It does not protect MyISAM tables; convert those to InnoDB or accept a lock.--routines,--triggersand--eventsinclude stored procedures, functions, triggers and scheduled events. Triggers are included by default; the other two are not.--databases appdbaddsCREATE DATABASEandUSEstatements, so the dump can be restored without creating the database first. Use--all-databasesinstead to back up everything, including themysqlsystem schema with users and grants.
Check the result:
sudo ls -lh /var/backups/mysql/
-rw-r--r-- 1 root root 48M Sep 25 10:04 appdb-2026-09-25.sql.gz
A few useful variants:
- Structure only (no rows), for example to compare schemas between servers: add
--no-data. - Skip a large table such as a log table: add
--ignore-table=appdb.access_log. Repeat the option for each table. - Record the binary log position, which you need to set up a replica or do point-in-time recovery: add
--source-data=2on MySQL 8.0 (--master-data=2on MariaDB).
Step 3 - Using a dedicated backup user for mysqldump
Running backups from scripts or other machines as root is not a good idea. Create a MySQL user with only the privileges mysqldump needs. Open the MySQL shell:
sudo mysql
Create the user and grant it read access plus the privileges needed for consistent dumps, views, triggers and events. Replace your_strong_password with a real password:
CREATE USER 'backup'@'localhost' IDENTIFIED BY 'your_strong_password';
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT, PROCESS, RELOAD ON *.* TO 'backup'@'localhost';
EXIT;
PROCESS is required by MySQL 8.0 to dump tablespace information, and RELOAD is needed for --source-data. Store the password in an option file instead of typing it on the command line, where it would be visible in ps and the shell history:
sudo nano /root/.my-backup.cnf
[mysqldump]
user=backup
password=your_strong_password
Restrict the file to root:
sudo chmod 600 /root/.my-backup.cnf
Now run the dump with that file. --defaults-extra-file must be the first option:
sudo bash -c 'set -o pipefail; mysqldump --defaults-extra-file=/root/.my-backup.cnf --single-transaction --routines --triggers --events --databases appdb | gzip > /var/backups/mysql/appdb-$(date +%F).sql.gz'
If the command returns without an error and the file size is similar to before, the backup user has all the privileges it needs.
Step 4 - Backing up PostgreSQL with pg_dump
pg_dump always produces a consistent snapshot, because it runs inside a single transaction, and it does not block reads or writes. Use the custom format (-Fc): it is compressed by default and lets pg_restore restore selected tables or run the restore in parallel.
sudo -u postgres pg_dump -Fc -f /var/backups/postgresql/appdb-$(date +%F).dump appdb
Check the result:
sudo ls -lh /var/backups/postgresql/
-rw-r--r-- 1 postgres postgres 41M Sep 25 10:10 appdb-2026-09-25.dump
pg_dump backs up one database, but roles (users) and tablespaces are global objects that belong to the whole cluster. Back them up separately with pg_dumpall:
sudo -u postgres pg_dumpall --globals-only -f /var/backups/postgresql/globals-$(date +%F).sql
Without this file, restoring on a new server fails on every GRANT and OWNER TO that references a role that does not exist there.
For large databases, the directory format (-Fd) can dump several tables in parallel with -j. Use one job per CPU core at most:
sudo -u postgres pg_dump -Fd -j 4 -f /var/backups/postgresql/appdb-$(date +%F).dir appdb
Other useful options:
- Schema only:
--schema-only. Data only:--data-only. - Skip the rows of a large table but keep its definition:
--exclude-table-data=access_log. - Plain SQL instead of an archive, readable with any text editor:
-Fp.
To run pg_dump against a remote server or as a non-superuser, store the password in ~/.pgpass with one line per connection in the format hostname:port:database:username:password, and restrict it with chmod 600 ~/.pgpass. The PostgreSQL client ignores the file if other users can read it.
Step 5 - Restoring the backups
Always test a restore into a new database first, never on top of production. This is also the only real proof that a backup works.
Restoring a mysqldump backup
The dump from Step 2 contains CREATE DATABASE appdb, so restoring it as-is would write into the existing appdb. To restore into a test database, create one and strip the CREATE DATABASE and USE lines. A simpler approach is to take a dump without --databases for this purpose, but you can also filter the existing one:
sudo mysql -e "CREATE DATABASE appdb_restore;"
sudo bash -c 'set -o pipefail; zcat /var/backups/mysql/appdb-$(date +%F).sql.gz | grep -v -E "^(CREATE DATABASE|USE )" | mysql appdb_restore'
When you restore on a new server where appdb does not exist yet, pipe the dump straight into mysql without a database name:
zcat appdb-2026-09-25.sql.gz | sudo mysql
Restoring a pg_dump backup
Create an empty database and restore the custom-format archive into it. --no-owner assigns all objects to the user running the restore, which avoids errors if the original owner role does not exist:
sudo -u postgres createdb appdb_restore
sudo -u postgres pg_restore --no-owner -j 4 -d appdb_restore /var/backups/postgresql/appdb-$(date +%F).dump
On a new server, restore the globals file first so the roles exist, then restore the database with its original owners by letting pg_restore create it:
sudo -u postgres psql -f globals-2026-09-25.sql
sudo -u postgres pg_restore -C -d postgres appdb-2026-09-25.dump
With -C, pg_restore connects to the postgres database, creates appdb and restores into it.
Step 6 - Verifying the backups
Check that a dump is complete before you rely on it. A successful mysqldump ends with a completion comment:
sudo zcat /var/backups/mysql/appdb-$(date +%F).sql.gz | tail -n 1
-- Dump completed on 2026-09-25 10:04:31
Also verify the gzip file is not corrupted:
sudo gzip -t /var/backups/mysql/appdb-$(date +%F).sql.gz && echo OK
For PostgreSQL, pg_restore --list reads the archive's table of contents. If it prints the list of objects, the archive is readable:
sudo -u postgres pg_restore --list /var/backups/postgresql/appdb-$(date +%F).dump | head -n 15
The strongest check is comparing the restored copy with the original. For example, count the rows of an important table in both databases:
sudo -u postgres psql -Atc "SELECT count(*) FROM orders;" appdb
sudo -u postgres psql -Atc "SELECT count(*) FROM orders;" appdb_restore
The counts should match (allowing for rows written after the dump started). When you are done, drop the test databases:
sudo mysql -e "DROP DATABASE appdb_restore;"
sudo -u postgres dropdb appdb_restore
Step 7 - Copying backups off the server
A backup stored only on the same disk as the database does not survive a disk failure or a deleted server. Copy the dumps to another machine, for example with rsync over SSH (replace your_user and backup_host). Without a trailing slash, each directory is copied with its name, so MySQL and PostgreSQL dumps stay separate:
sudo rsync -av /var/backups/mysql /var/backups/postgresql your_user@backup_host:/srv/db-backups/
Because the command runs with sudo, it uses root's SSH key (/root/.ssh/), so add root's public key to your_user on the backup host first.
To run backups, rotation and off-site copies every night without manual work, see the guide on database backup automation, which wraps these commands in a script and a systemd timer.
Troubleshooting
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s)': grantPROCESSto the backup user as shown in Step 3, or add--no-tablespacesif you do not use general tablespaces.mysqldump: Got error: 1044: Access denied ... when using LOCK TABLES: the user lacksLOCK TABLES.--single-transactionavoids most locks, but the privilege is still checked for some tables.pg_dump: error: could not open output file ... Permission denied: thepostgresuser cannot write to the target directory. Use a directory owned bypostgres, as in Step 1.pg_restore: error: could not execute query: ERROR: role "appuser" does not exist: restore the globals file first, or use--no-owner --no-privileges.pg_dump: error: aborting because of server version mismatch: the client is older than the server. Use thepg_dumpfrom the same or a newer major version than the server you are dumping.
Conclusion
You created consistent backups of MySQL or MariaDB with mysqldump and of PostgreSQL with pg_dump and pg_dumpall, kept credentials out of the command line, restored the dumps into test databases and verified them. Next, schedule these backups with a systemd timer and a rotation policy, copy them to a second location every day, and for very large databases consider physical backups with Percona XtraBackup, Mariabackup or pg_basebackup, which restore much faster than logical dumps.