---
title: "Back up every database on a server"
date: "2024-09-26"
path: "/222-back-up-every-database-on-a-server.html"
section: "/ramblings"
description: "A short bash script that dumps each MySQL database on a server to its own compressed file, using an option file for the password."
---

I needed a copy of every database on a managed MySQL cluster before some maintenance, one file per database so any of them could be restored on its own. `mysqldump --all-databases` gives you one enormous file, which is awkward when you only need one schema back. So: list the databases, loop, dump each one.

## Credentials

Put the connection details in an option file rather than in the script. Anything passed as `-pSECRET` shows up in `ps` and in shell history, and a script with a password in it tends to get copied around.

`~/.backup.cnf`:

```text
[client]
host=db.example.com
user=backup_user
password=CHANGE_ME
```

```bash
chmod 600 ~/.backup.cnf
```

The user only needs to read. On MySQL 8 that is roughly:

```sql
CREATE USER 'backup_user'@'%' IDENTIFIED BY 'CHANGE_ME';
GRANT SELECT, SHOW VIEW, TRIGGER, EVENT, PROCESS ON *.* TO 'backup_user'@'%';
```

`PROCESS` is needed by mysqldump 8.0.21 and later to read tablespace information. Without it you get an access-denied warning; `--no-tablespaces` avoids it if you would rather not grant it.

## The script

```bash
#!/bin/bash
set -euo pipefail

CNF="$HOME/.backup.cnf"
OUT_DIR="/var/backups/mysql/$(date +%F)"

mkdir -p "$OUT_DIR"
chmod 700 "$OUT_DIR"

databases=$(mysql --defaults-extra-file="$CNF" -N -B -e "SHOW DATABASES;" \
  | grep -Ev '^(information_schema|performance_schema|mysql|sys)$')

for db in $databases; do
  echo "Backing up: $db"
  mysqldump --defaults-extra-file="$CNF" \
    --single-transaction --routines --triggers --events \
    --databases "$db" \
    | gzip > "$OUT_DIR/${db}.sql.gz"
done

echo "All databases backed up to $OUT_DIR"
```

## The bits worth explaining

`--defaults-extra-file` has to be the first option on the command line, or it is silently ignored and you get an access-denied error that makes no sense.

`-N -B` on the listing query drops the column header and the table borders, so the output is just one name per line. Without `-N` the word `Database` comes out as the first "database" and has to be filtered with grep.

The `grep -Ev` with anchors skips the system schemas exactly. A loose match such as `grep -v "Database\|information_schema"` also drops any real database whose name happens to contain one of those words.

- `information_schema` and `performance_schema` are views onto the server's state and cannot be restored.
- `mysql` holds users and grants. On a managed service you cannot restore it anyway, and between major versions you should not try. Use `SHOW CREATE USER` and `SHOW GRANTS` to record users separately if you need them.
- `sys` is a set of helper views that MySQL recreates itself.

`--single-transaction` reads each InnoDB database from a consistent snapshot without locking it, so the applications using the server carry on as normal.

`--routines --triggers --events` makes sure stored procedures, functions and scheduled events come along. Triggers are on by default; the other two are not.

`--databases "$db"` (rather than just `"$db"`) adds `CREATE DATABASE` and `USE` statements to the top of the file, so it restores into a database of the same name without you having to create it first.

`set -o pipefail` matters with the `| gzip`. Without it, a mysqldump that fails part-way still leaves a valid-looking `.sql.gz`, and the script reports success.

## Restoring one

```bash
gunzip < /var/backups/mysql/2024-09-26/shop.sql.gz | mysql --defaults-extra-file="$HOME/.backup.cnf"
```

Because of `--databases`, that recreates the database under its original name. To restore it somewhere else, use the dump without `--databases`, or edit the `CREATE DATABASE` and `USE` lines first.

## Things to watch for

- Dumps hold everything in the databases, personal data included. Keep the backup directory readable only by the user that runs the job, and do not leave copies lying around on laptops.
- Test a restore. A directory full of `.sql.gz` files is not a backup until you have loaded one of them back in.