Peter Davies

Ramblings

Back up every database on a server

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:

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

chmod 600 ~/.backup.cnf

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

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

#!/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.

--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

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