--- title: "Back Up MySQL and PostgreSQL with mysqldump and pg_dump" description: "Create consistent logical backups of MySQL, MariaDB and PostgreSQL with mysqldump and pg_dump on Ubuntu 24.04, then restore them and check every dump is usable." category: "Database Management" language: en source: https://cubepath.com/docs/database-management/database-backup-with-mysqldump-and-pg_dump updated: 2026-09-25 --- # Back Up MySQL and PostgreSQL with mysqldump and pg_dump `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 `sudo` privileges. - 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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`: ```bash 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-transaction` takes 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`, `--triggers` and `--events` include stored procedures, functions, triggers and scheduled events. Triggers are included by default; the other two are not. - `--databases appdb` adds `CREATE DATABASE` and `USE` statements, so the dump can be restored without creating the database first. Use `--all-databases` instead to back up everything, including the `mysql` system schema with users and grants. Check the result: ```bash sudo ls -lh /var/backups/mysql/ ``` ```output -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=2` on MySQL 8.0 (`--master-data=2` on 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: ```bash 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: ```sql 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: ```bash sudo nano /root/.my-backup.cnf ``` ```ini [mysqldump] user=backup password=your_strong_password ``` Restrict the file to root: ```bash sudo chmod 600 /root/.my-backup.cnf ``` Now run the dump with that file. `--defaults-extra-file` must be the first option: ```bash 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. ```bash sudo -u postgres pg_dump -Fc -f /var/backups/postgresql/appdb-$(date +%F).dump appdb ``` Check the result: ```bash sudo ls -lh /var/backups/postgresql/ ``` ```output -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`: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash sudo zcat /var/backups/mysql/appdb-$(date +%F).sql.gz | tail -n 1 ``` ```output -- Dump completed on 2026-09-25 10:04:31 ``` Also verify the gzip file is not corrupted: ```bash 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: ```bash 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: ```bash 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: ```bash 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: ```bash 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)'`**: grant `PROCESS` to the backup user as shown in Step 3, or add `--no-tablespaces` if you do not use general tablespaces. - **`mysqldump: Got error: 1044: Access denied ... when using LOCK TABLES`**: the user lacks `LOCK TABLES`. `--single-transaction` avoids most locks, but the privilege is still checked for some tables. - **`pg_dump: error: could not open output file ... Permission denied`**: the `postgres` user cannot write to the target directory. Use a directory owned by `postgres`, 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 the `pg_dump` from 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.