Database Backup and Restore: mysqldump and pg_dump Guide

What is a database backup and how does mysqldump create one?

A database backup is a copy of your tables and their data, saved outside the live database. For MySQL and MariaDB, mysqldump turns that copy into a text file of SQL statements. Likewise, PostgreSQL has pg_dump for the same job. Run the file later and you rebuild the database.

This guide is for site owners and developers who manage their own VPS or cPanel account. So you can follow the commands step by step. Protecting the whole site is a separate topic, though. For file backups, retention and the 3-2-1 idea, read our website backup strategy guide. So here we cover only the database side.

We are a digital marketing and web team, not a hosting company. So the explanations rest on official documentation. We checked each option against the MySQL and PostgreSQL manuals. We give no version numbers, because options change over time. Therefore, always open the manual for your own version before you run anything.

What is the difference between a logical and a physical backup?

A logical backup writes your data as SQL statements. A physical backup, on the other hand, copies the database engine's files on disk. Both mysqldump and pg_dump produce logical backups. In practice, they are readable, portable and good enough for small and mid-sized sites.

Physical backups often restore faster on very large databases. On the other hand, they tie you to the same engine and a compatible version. So for most websites, a logical backup is the sensible first step. The table below sums up the difference, so you can compare at a glance.

What should you check before you start a database backup?

First, decide exactly what you are backing up. The database, the engine and the user decide the command. You also need to know where the file will go and whether the disk has enough free space.

• Find out the engine: MySQL, MariaDB or PostgreSQL.
• Check the table engines: InnoDB or MyISAM.
• Create a separate backup user with only the privileges the backup needs.
• Make sure the backup folder has free space, because a full disk stops the job.
• Prepare a test environment where you can restore the file.

The backup user needs a strong password. You can use our password generator and store the result in a password manager. Also, never type it into commands.

If you run MariaDB, the client also ships as mariadb-dump. According to the MariaDB documentation, the old mysqldump name stays as a symlink, but it is deprecated from version 11.0. So check which name your server has.

How do you back up a single database with mysqldump?

The simplest form gives the database name and redirects the output to a file. The example below backs up a database called example_db. For the password it uses an option file, which we explain a bit later.

mysqldump --defaults-extra-file=/home/user/.backup.cnf \
  --single-transaction --routines --triggers --events \
  example_db > example_db.sql

Note that we left out the --databases option on purpose. According to the MySQL manual, that option adds CREATE DATABASE statements to the output. If you load such a file for a test, you can overwrite a live database with the same name. Naming the database in the command lets you choose the target when you restore.

The --all-databases option dumps every database into one file. However, separate files make it easier to restore a single site. For that reason we suggest one command per database.

What does --single-transaction do, and when is it not enough?

The MySQL manual says --single-transaction sends a BEGIN statement before it dumps data. On InnoDB tables, you then get a consistent snapshot without locking the site. In other words, orders or comments can keep arriving while the backup runs, yet the file reflects one single moment.

However, the option has limits, and the manual names two. First, statements that change table structure during the dump can break consistency. For example, do not run ALTER TABLE or TRUNCATE TABLE meanwhile. Second, tables without transaction support, such as MyISAM, do not get consistency from this option.

For a mixed-engine database the manual points to --lock-tables. With it, writes wait during the backup, so the site feels slower. So avoid busy hours, and talk to an expert about moving tables to InnoDB if you can.

Do stored routines, triggers and events end up in the backup?

Asking for all of them explicitly is the safest path. Per the MySQL manual, stored routines need --routines and events need --events, and neither is dumped by default. However, triggers have their own --triggers option. We write all three in our examples, because trusting defaults can cause a surprise on restore day.

If you use none of these objects, adding the options still does no harm, because the dump stays correct. If you do use them, a missing procedure can break part of the site. Ready-made systems such as WordPress usually have few of them, but custom software often does.

In custom software, it is a good habit to keep database objects in your code repository too. That way you can rebuild the structure even if a backup fails. If you want help with that, see our custom software development service.

Which privileges does the backup user need?

The MySQL manual lists the required privileges. You need SELECT for dumped tables, SHOW VIEW for views and TRIGGER for triggers. You also need LOCK TABLES if you do not use --single-transaction. So you can work with a narrow account instead of an admin account.

Watch two details, though. The manual asks for the PROCESS privilege unless you use --no-tablespaces. On servers that use GTIDs, --single-transaction may also need RELOAD or FLUSH_TABLES. Routines and events can ask for extra privileges, so read the manual for your version.

GRANT SELECT, SHOW VIEW, TRIGGER ON example_db.* TO 'backup_user'@'localhost';

You run this statement with a privileged account, for example the admin user. The user and database names are made up for the example. Keeping the admin password out of the backup script also shrinks the impact of a leak. Limiting the grant to the needed database is another good habit.

Can you run a backup without typing the password on the command line?

You can, and you should. The MySQL manual states that a password on the command line is insecure and recommends an option file. Also, a command-line password can show up in the process list and in shell history.

First create an option file. The format is simple: you write a group name in square brackets, then add user and password lines. Treat the values below as pure examples.

[client]
user=backup_user
password=TYPE_PASSWORD_HERE

Then make the file readable only by you. The manual is clear that only the owner should have access. Here is the command:

chmod 600 /home/user/.backup.cnf

Finally, pass the file with --defaults-extra-file. The manual says it must be the first option on the command line. That is why we always put it first in our examples.

How do you take a PostgreSQL backup with pg_dump?

According to the PostgreSQL manual, pg_dump makes a consistent export even while the database is in use, and it does not block other users from reading or writing. So you do not need a separate lock option. The most common format is the custom archive.

pg_dump --format=custom --file=example_db.dump \
  --host=localhost --username=backup_user example_db

For the password you can use the PGPASSWORD environment variable or a .pgpass file. The manual supports both. We prefer the file, because an environment variable can also leak through process information. Each line has the form host:port:database:user:password. Again, limit the file permissions to the owner.

Do not skip one point: pg_dump does not include roles and users. The manual tells you to run pg_dumpall with the --globals-only option for that. If you may rebuild the server from scratch, keep that file too.

Which pg_dump format should you choose?

Your choice decides how you restore. Plain text loads with psql, and the others load with pg_restore. Also, only the directory format supports parallel dumps. The table below collects the facts from the PostgreSQL manual.

For most sites the custom archive is enough. On a very large database, the directory format with the --jobs option can cut the time. However, parallel work opens extra connections, and the manual says it uses one more connection than the number of jobs. On shared hosting, ask your provider first.

How do you compress a database backup file?

mysqldump writes its output as text, and text compresses well. So piping the output to gzip saves disk space. It also means you send less data when you copy the file off the server.

mysqldump --defaults-extra-file=/home/user/.backup.cnf \
  --single-transaction --routines --triggers --events example_db \
  | gzip > example_db.sql.gz

Pipes have a small trap. If mysqldump fails, gzip still finishes successfully, and you end up with a valid but nearly empty compressed file. So turn on the pipefail option in bash scripts. Then the script exits with an error code if any command in the pipe fails.

In PostgreSQL, the custom and directory formats already compress. The manual says the -Z option lets you pick gzip, lz4 or zstd as the method. For plain text, you can pipe to gzip as you do with MySQL.

How do you schedule a database backup with cron?

Manual backups get forgotten, so schedule your database backup. Put the command into a script first, then run it with cron. The script below is an example. Change the paths and the database name to fit your environment.

#!/bin/bash
set -euo pipefail
DIR=/home/user/backups/db
STAMP=$(date +%Y%m%d-%H%M)
mkdir -p "$DIR"
mysqldump --defaults-extra-file=/home/user/.backup.cnf \
  --single-transaction --routines --triggers --events example_db \
  | gzip > "$DIR/example_db-$STAMP.sql.gz"
find "$DIR" -name 'example_db-*.sql.gz' -mtime +14 -delete

Make the script executable, run crontab -e and add the line below. It runs every night at 03:30 and writes its output to a log.

30 3 * * * /home/user/backup-db.sh >> /home/user/backups/backup.log 2>&1

We kept the percent signs of the date format inside the script. In crontab lines, a percent sign has a special meaning and needs escaping. For that reason, moving the command into its own file is safer.

How long should you keep old backups, and how do you delete them?

If you keep every backup forever, the disk fills up and one day the backup job fails. The find line in the script prevents that by deleting files older than 14 days. Still, you should pick the number to fit your own needs.

Three questions decide retention. How late might you notice a mistake? Do you have a legal duty to keep order or user data? How much disk space do you have? For example, if you notice a bug after three weeks, two weeks of retention is not enough.

• Keep daily backups for a short time and weekly backups for longer.
• Run the delete command first without -delete and check the list.
• Confirm the new backup succeeded before you delete anything.

We do not give a numeric rule here. The right rule depends on how fast your data changes and on your legal duties. For the general idea of tiered retention, see the strategy guide.

Does a database backup slow your server down?

It can. During a backup, the database reads its tables from start to finish. That reading uses CPU and disk, so a backup at a busy hour may slow the site. The first fix is to schedule the job when visitors are few. Check your traffic graph and pick the quietest hour.

The MySQL manual says --opt is on by default and also turns on --quick. That option reads a table row by row, so it does not load a huge table into memory. In short, mysqldump is already memory friendly. You can still lower the job's priority with nice and ionice.

nice -n 19 ionice -c3 /home/user/backup-db.sh

This line runs the script with the lowest CPU priority and the idle disk class. On shared hosting these commands may not work, or your quota may still limit you. If the backup keeps getting slower, treat it as a warning. Then look at the database size and ask your provider about backing up from a replica.

Can special characters break in a database backup?

With the right settings they do not. The problem usually comes from the client that dumps and the client that loads using different character sets. As a result, accented letters can turn into question marks or odd symbols. So first find out your database's character set.

You can use the SHOW CREATE DATABASE statement for that, and the output shows the character set. mysqldump has a --default-character-set option. If your database uses utf8mb4, it is safe to write the same value in both the dump and the load commands. Then check a few rows with accented text by eye after the test restore.

In PostgreSQL the risk is smaller, because the archive carries the database encoding. Still, take care if you load into a database with a different encoding. Old sites in particular can hide mixed encodings that only appear when you move the data.

How do you back up only some tables, or only the structure?

Sometimes you do not need the whole database. mysqldump lets you write table names after the database name, and then it dumps only those tables. For example, you might want only the product and category tables for a development copy.

mysqldump --defaults-extra-file=/home/user/.backup.cnf \
  --single-transaction example_db products categories > products.sql

The --ignore-table option does the opposite. You leave out large and useless tables, such as log records. Also, the --no-data option writes only the table structure. It helps when you set up an empty test environment.

Still, do not treat a partial dump as a safe backup on its own. Relations between tables can end up half complete. So make your regular backup always the full database, and use partial dumps only for special jobs.

How do you restore a MySQL backup?

The MySQL manual says you restore a dump by feeding the file to the mysql client. The target database must exist first. So create the database, then load the file.

mysql --defaults-extra-file=/home/user/.backup.cnf \
  -e "CREATE DATABASE test_restore"
gunzip < example_db.sql.gz | mysql \
  --defaults-extra-file=/home/user/.backup.cnf test_restore

If your backup user was designed for reading only, use another account with write privileges for the load. Before you load over a live database, always take a fresh backup. A restore into the wrong target can change data for good.

Large files take a long time. During that time the site may run on half the data. For that reason, do a live restore inside a maintenance window and show visitors a maintenance page.

How do you restore a PostgreSQL backup?

The tool depends on the format. psql loads plain text files, and pg_restore loads custom and directory archives. In the example, we first create an empty database and then load the archive into it.

createdb test_restore
pg_restore --dbname=test_restore --no-owner --exit-on-error example_db.dump

According to the PostgreSQL manual, --exit-on-error stops at the first error, and --no-owner skips restoring object ownership. The --list option also prints the archive contents. It helps you see that the file is readable before you load it.

For a plain text file, use the form below. The ON_ERROR_STOP variable makes psql stop at the first error.

gunzip -c example_db.sql.gz | psql --set ON_ERROR_STOP=on \
  --dbname=test_restore

The manual also says --single-transaction cannot be combined with --jobs. So decide whether you want speed or integrity in one transaction.

How do you verify that a database backup really works?

A backup you never restored is a file that has not proven anything. Verification has three steps. First, you check that the file is not corrupt. Then you load it into a test environment. Finally, you count that the data is as large as you expect.

1. Test the compressed file with gzip -t.
2. Look for the "Dump completed" line at the end of the mysqldump output.
3. Run pg_restore --list on a PostgreSQL archive.
4. Load it into a test database and compare row counts on key tables.
5. Connect a test copy of the site and open a real order or post page.

We suggest repeating this check once a month. A backup script can break silently: a password changes, a disk fills up, a privilege is removed. Cron may keep running, and the file may still be empty.

The safest test runs in an environment separate from the live server. Starting a throwaway database with Docker on your own machine works well. For Docker basics, see our Docker guide.

How do you use a backup to move to a new host or server?

The biggest strength of a logical backup is portability. On the new server, create an empty database and a user, then load the file. Because the file is SQL text, it usually moves across engine versions without trouble. Still, read the compatibility notes in the manual before a big version jump.

Order matters during a move. First prepare the new environment and run a test load. Then, inside a maintenance window, stop writes on the old site, take a fresh backup and load it into the new one. Finally, point the DNS record to the new server. You can watch the change with our DNS lookup tool.

Do not shut the old server down right away. While DNS spreads, some visitors still reach the old address. Watch the new site for a few days. Keeping the old database read-only during that time prevents different data from appearing in two places.

How do you get the backup off the server?

If the backup sits on the same disk as the database, both vanish when the disk fails. So copy the file to another location. With SSH access, rsync is a simple and reliable way.

rsync -av -e ssh /home/user/backups/db/ \
  backup@backup.example.com:/backups/example-site/

After copying, verify that the files match with a checksum. Run sha256sum on both sides and compare the output. Also remember that the backup contains customer data. So transfer it over an encrypted channel and restrict access at the destination.

For more sensitive data, you can encrypt the file before sending it. For example, gpg offers symmetric encryption. Do not store the key or password next to the backup. For the wider picture, see our guide on website data security and encryption.

What are the most common database backup mistakes?

Most mistakes come from the surroundings of the command, not from the command itself. The list below sums up the traps, based on the documentation.

• Typing the password on the command line. An option file and strict permissions fix this.
• Using --databases without noticing and overwriting live data during a test load.
• Skipping pipefail in a pipe and producing an empty backup.
• Leaving roles and users out of the backup. PostgreSQL needs pg_dumpall for that.
• Never restoring a backup, so the first attempt happens during a disaster.
• Keeping backups only on the same server.

You can prevent all of these. Set the backup script up once and put a monthly check in your calendar. Security holes also cause data loss, so for that risk read our OWASP Top 10 guide.

When should you not do this yourself and leave it to your hosting provider?

You do not have to do everything yourself. If you have no shell access, or you cannot run commands on shared hosting, use the panel's backup tools and the provider's support team. A wrong restore can damage your data.

• The database is very large and the backup slows the site down.
• You need to return to an exact moment, which is point-in-time recovery. That job usually needs binary logs and expert management.
• You use replication or a cluster.
• Legal retention and audit duties apply to you.
• You are not sure what the commands do.

When you pick a provider, ask about the backup policy. Get in writing how often they back up, how many days they keep copies and how long a restore request takes. Our guide to choosing web hosting helps with this.

What should a short database backup checklist look like?

The list below gathers the steps of this guide in one place. You can print it and add it to your server notes. Once you set up each item, you only repeat the verification.

1. Create a backup user that has only the privileges it needs.
2. Put the password in an option file and set the permissions to 600.
3. Use --single-transaction for InnoDB, plus --routines, --triggers and --events.
4. Compress the output and turn on pipefail in the script.
5. Run it daily with cron and clean up old files.
6. Copy the backup off the server and verify the checksum.
7. Restore it into a test database once a month.

Set up your database backup correctly once, and the rest turns into a small maintenance habit. To grow your SQL skills, our SQL learning roadmap and SQL query scenarios are good next steps.

Our team can help you plan infrastructure decisions for web and ecommerce projects together with your hosting provider. See our web design service for details. Writing down your backup and restore plan saves the most time during a crisis.

Command options can change between versions, so always check the manual for your own version. The official pages behind this guide are these:

• MySQL mysqldump manual.
• MySQL option files manual.
• PostgreSQL pg_dump manual.
• PostgreSQL pg_restore manual.
• MariaDB mariadb-dump manual.

The version part of a documentation link changes over time. Go to the page for the current stable release and look for the same heading. In short, confirm that an option exists in your version before you run the command.