How to back up your database

A database backup is a copy of your data that you can restore when something goes wrong: a DELETE without a WHERE, a failing disk, a migration that corrupts tables. The most portable kind is a dump: a file with the contents of the database that you can restore on any compatible server.

The MySQL, PostgreSQL and MongoDB commands below follow each tool's official documentation. A backup that has never been restored is only a hope, so each section ends with the restore.

What do you need before you start?#

• The command-line tools installed on the machine that runs the backup: mysqldump (part of the MySQL client), pg_dump and pg_restore (PostgreSQL client), and mongodump and mongorestore (MongoDB Database Tools).
• Host, port, user, password and database name. For a hosted database, the dashboard shows the connection string.
• A user with read access to everything you want to copy.
• Disk space for the file. Check the size of the first dump before you schedule anything.
• An empty database to test the restore. Never test by restoring over production.

Avoid putting the password on the command line: it shows up in shell history and in the process list. Use environment variables or the tools' own config files (for example ~/.my.cnf and ~/.pgpass).

How do you back up a MySQL database?#

Use mysqldump. For InnoDB tables, --single-transaction opens a transaction and reads a consistent view without blocking writes.

mysqldump -h "$DB_HOST" -u "$DB_USER" -p \
  --single-transaction --routines --triggers --events \
  --databases mydb > mydb.sql

--routines and --events include stored procedures, functions and scheduled events, which are left out by default. Triggers are dumped by default, and the option only makes that explicit. --databases also writes the CREATE DATABASE statement into the file. On Windows, use --result-file=mydb.sql instead of the > redirect, because PowerShell can write UTF-16 and the file will not load later.

To restore:

mysql -h "$DB_HOST" -u "$DB_USER" -p < mydb.sql

Because the dump was made with --databases, it already contains CREATE DATABASE and USE, so you do not need to pass the database name.

How do you back up a PostgreSQL database?#

Use pg_dump in custom format (-Fc). It is compressed by default and lets pg_restore restore only part of the database.

pg_dump -h "$DB_HOST" -U "$DB_USER" -d mydb -Fc -f mydb.dump

Restore into a new database:

createdb -h "$DB_HOST" -U "$DB_USER" restore_test
pg_restore -h "$DB_HOST" -U "$DB_USER" -d restore_test --no-owner mydb.dump

--no-owner avoids ALTER OWNER errors when the restoring user differs from the original one. To restore over existing objects, use --clean --if-exists. With -j 4, pg_restore loads data and builds indexes in parallel (custom and directory formats only).

If you prefer a plain SQL file (pg_dump -f mydb.sql, without -Fc), restore it with psql -X -h "$DB_HOST" -U "$DB_USER" -d restore_test -f mydb.sql.

How do you back up a MongoDB database?#

Use mongodump with a single compressed archive:

mongodump --uri="$MONGO_URI" --gzip --archive=mydb.archive.gz

If MONGO_URI contains the password, it shows up in the process list (ps). The documentation recommends leaving the password out of the URI so mongodump prompts for it, or using --config with a YAML file. Without --db, it copies every database on the instance. For one database, add --db=mydb. To restore:

mongorestore --uri="$MONGO_URI" --gzip --archive=mydb.archive.gz

Add --drop to remove existing collections before restoring, but only in a test environment or when you are sure of what you are doing. To restore a single collection, use --nsInclude="mydb.orders".

On a replica set, --oplog also records the writes that happen during the dump, so the result represents a single point in time (restore with --oplogReplay). It cannot be combined with --db or --collection, and it does not work on a sharded cluster.

What about Redis?#

Redis writes snapshots of the dataset to a dump.rdb file, through rules such as save 60 1000 or the BGSAVE command. According to the documentation, the file is never modified once produced, so copying it while the server runs is safe. Backing up Redis means copying that file. To restore, place it in the data directory before the server starts (with AOF enabled, Redis loads the AOF instead). Persistence details are in What is Redis.

How do you automate it and keep a copy off the server?#

A manual backup gets forgotten. Schedule it with cron and put the date in the file name:

#!/usr/bin/env bash
set -euo pipefail
FILE="mydb-$(date +%F-%H%M).dump"
pg_dump -h "$DB_HOST" -U "$DB_USER" -d mydb -Fc -f "$FILE"
rclone copy "$FILE" remote:backups/  # example: sends the file off the server
find . -name 'mydb-*.dump' -mtime +7 -delete

To run it every day at 3 a.m., add 0 3 * * * /path/backup.sh to your crontab. Cron does not load your shell's variables, so set DB_HOST and DB_USER in the script itself and keep the password in ~/.pgpass. The find line deletes dumps older than 7 days so the disk does not fill up.

The off-server copy follows the 3-2-1 rule. S3-compatible object storage is a good destination, as covered in backups to object storage.

How do you know the backup works?#

1. Check that the file is not empty: ls -lh, and compare with the previous dump.
2. Restore it into a test database, as in the examples above.
3. Compare counts: SELECT COUNT(*) on the main tables, or db.orders.countDocuments() on MongoDB, in both databases.
4. Note how long the restore took. That is the recovery time you will have in an emergency.

Repeat the test regularly, not just once.

What are the common mistakes?#

• Silent partial dump: the command failed midway and the file is incomplete. Use set -e in the script and check the exit code.
• Tool version different from the server: use pg_dump, mysqldump and mongodump at a version compatible with the server, and restore with the same tool or a newer one. An old client is a common source of errors.
• Forgetting procedures and events in MySQL: without --routines and --events they are not in the file.
• Password in shell history: use credentials files.
• Backup on the same disk only: losing the server means losing everything.
• Never testing the restore: the failure shows up exactly when you need it most.

How to do this on Square Cloud#

On Square Cloud's database hosting (MongoDB, PostgreSQL, MySQL and Redis, from the Standard plan), a recent restore point comes with the plan. Every plan that includes databases takes an automatic daily snapshot of each database: each one is kept for 7 days, and the most recent one for up to 30 days. Before a migration or a risky DELETE, you take a manual snapshot from the dashboard, up to 8 per GB of plan RAM per day.

Restoring follows the rule of testing away from production: the snapshot comes back whole into the same database, another database of the same engine or a new database, so you check the counts on a copy before touching the main database. To keep a copy of your own, download the snapshot as a .zip, which works for every engine, Redis included.

The dumps in this guide are still your independent, portable copy. The database accepts outside connections with its host, port, password and the certificates downloaded from the dashboard, so the cron script above can run on your machine or in CI. TLS is mandatory: pass the CA to the tool (--ssl-ca for mysqldump, sslrootcert in the pg_dump connection string, --tlsCAFile for mongodump) and, on PostgreSQL and MongoDB, the client certificate too (sslcert and sslkey, or --tlsCertificateKeyFile). Since each database is a single node, with no replica set, mongodump --oplog does not apply.

See retention, limits and restore options on Snapshots.