MySQL – Backup and Restore with mysqldump

mysqldump writes a database out as a text file of SQL statements — the schema, the data, or both. It is the backup everyone starts with, and it is genuinely good up to a point that this lesson tries to be honest about.

The command you actually want

mysqldump -h 127.0.0.1 -P 3308 -u root \
  --single-transaction \
  --routines --events --triggers \
  pizza > pizza-backup.sql

Every flag there is load-bearing:

The two defaults worth internalising: a plain mysqldump silently omits your procedures and events. Restore that backup and the schema comes back without them, and nothing says so.

What is in the file

-- MySQL dump 10.13  Distrib 8.4.10, for macos15 (arm64)
--
-- Host: 127.0.0.1    Database: pizza
-- ------------------------------------------------------
-- Server version	8.4.11

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

Plain SQL, which is the format's great virtue: you can read it, grep it, edit it, and restore it into a different MySQL version. Those /*!40101 ... */ comments are version-gated SQL — a comment to any other database, an executed statement to MySQL at or above that version.

Note FOREIGN_KEY_CHECKS=0. That is what lets the restore insert tables in any order without tripping over foreign keys, and it is why a restore does not validate referential integrity.

Variations

mysqldump ... --no-data pizza > schema-only.sql        # structure, no rows
mysqldump ... --no-create-info pizza > data-only.sql   # rows, no structure
mysqldump ... pizza product product_size > menu.sql    # named tables only
mysqldump ... --databases pizza pizza_lab > both.sql   # several, with CREATE DATABASE
mysqldump ... --all-databases > everything.sql         # the whole server

# only some rows
mysqldump ... pizza customer_order --where="created_at >= '2026-01-01'" > recent.sql

--databases matters more than it looks: without it the dump contains no CREATE DATABASE or USE, so you must create the target database yourself and name it on restore. With it, the dump decides where it lands.

Restoring

mysql -h 127.0.0.1 -P 3308 -u root -e "CREATE DATABASE pizza_restored"
mysql -h 127.0.0.1 -P 3308 -u root pizza_restored < pizza-backup.sql

There is no mysqlrestore — a dump is a script, so you feed it to the client. Two things to know. It is single-threaded and rebuilds every index as it goes, so restoring is far slower than dumping: a dump that takes ten minutes can take an hour to come back. And mysql stops at the first error by default, which is what you want — running it with --force gives you a half-restored database.

The only test that counts

Restore it somewhere and look. A backup you have never restored is a hypothesis. The failures are mundane and total: the file was truncated because the disk filled, the cron job has been writing to a deleted directory for six months, the dump omitted the routines, the character set was wrong and every accented name is mangled.

mysql ... -e "CREATE DATABASE restore_test"
mysql ... restore_test < pizza-backup.sql
mysql ... restore_test -e "SELECT COUNT(*) FROM customer_order; SELECT COUNT(*) FROM order_item;"
mysql ... -e "DROP DATABASE restore_test"

Automate that and alert when it fails. Compare row counts against the source. This is the difference between having backups and having recovery.

Where mysqldump stops being the answer

It is a logical backup: it re-derives every row as an INSERT and replays them. That is portable and readable, and it does not scale.

The other limit is granularity. A nightly dump means losing up to a day. Point-in -time recovery — restore last night's dump, then replay the binary log up to the moment before the mistake — is what closes that gap, and it is the reason the binlog matters.

A workable routine

#!/usr/bin/env bash
set -euo pipefail

DEST=/backups/pizza-$(date +%F).sql.gz

mysqldump --defaults-file=/etc/mysql/backup.cnf \
  --single-transaction --routines --events --triggers \
  pizza | gzip > "$DEST"

# fail loudly if the file is implausibly small
[ "$(stat -f%z "$DEST")" -gt 1000000 ] || { echo "backup too small"; exit 1; }

find /backups -name 'pizza-*.sql.gz' -mtime +30 -delete

Note --defaults-file: putting the password on the command line exposes it in the process list to every user on the machine. A credentials file with 0600 permissions is the standard answer.

And set -euo pipefail is not decoration — without it, a failing mysqldump in a pipeline still produces a gzip file, exits 0, and your monitoring sees a successful backup of nothing.

What to remember

• --single-transaction, or you lock the tables you are backing up.
• --routines and --events are not included by default.
• Restoring is much slower than dumping, and single-threaded.
• An untested backup is a hypothesis. Restore it on a schedule and check the counts.
• Logical backups stop scaling; past that it is physical backups plus binlog replay.