Chapter 5
Chapter 5 — Backup and Restore
Hardware fails. People run DELETE without a WHERE clause. Ransomware encrypts /var/lib/mysql. Migrations go wrong. All of these happen, and all of them feel impossible until the moment they happen to you. A working backup and restore process is the single most important operational habit you can build around a database — and it takes less than an hour to set up properly.
What this chapter covers: What mysqldump produces and how it works. The key flags that matter (and why
--single-transaction is critical for InnoDB). Creating a dedicated backup user with minimal privileges. Credentials files — keeping passwords out of your shell history. Gzip-compressed timestamped dumps. Inspecting and verifying a dump file. Restoring from a dump — single database, all databases, and renaming during restore. A complete automated backup script with rotation. Cron scheduling. Offsite copies with rclone. Testing restores. Binary log and point-in-time recovery basics. Troubleshooting.
Before We Start: The 3-2-1 Rule
The 3-2-1 rule is the minimum viable backup strategy: 3 copies of your data, on 2 different media types, with 1 copy offsite. For a home server this translates to: a daily dump on the server itself, a second copy on an external drive or NAS, and a third copy in cloud storage.
A backup that lives on the same server as the database is not a real backup. Disk failure, ransomware, accidental
rm -rf, or a corrupted RAID volume takes both the database and the backup simultaneously. The backup script in this chapter saves locally and syncs offsite.
Equally important: an untested backup is not a backup. A dump file that can't be restored is worthless. Once your automated backup is running, schedule a monthly test restore to a temporary database. The instructions for this are at the end of the chapter.
What mysqldump Produces
mysqldump connects to MySQL and generates a SQL script that, when executed, recreates the database from scratch — schema and data. The output is a plain text file of SQL statements: DROP TABLE IF EXISTS, CREATE TABLE, then batched INSERT statements for the data.
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`id` int NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`author_id` int NOT NULL,
`price` decimal(6,2) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
LOCK TABLES `books` WRITE;
INSERT INTO `books` VALUES
(1,'The Great Gatsby',1,8.99),
(2,'Dune',2,12.50),
(3,'Neuromancer',3,9.99);
UNLOCK TABLES;
-- Dump completed on 2026-06-15 14:30:01
Check for "Dump completed on" at the end. If mysqldump is interrupted — disk full, network drop, killed process — the dump file will be truncated and lack this line. Always verify this line exists before trusting a backup.
Creating a Dedicated Backup User
Don't run backups as root. Create a minimal backup account with only the privileges mysqldump actually needs.
$ sudo mysql
# Create a backup-only user
mysql> CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'BackupP@ss!9';
# Grant only what mysqldump requires
mysql> GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER
-> ON *.* TO 'backup_user'@'localhost';
# For backing up all databases you also need RELOAD and PROCESS
mysql> GRANT RELOAD, PROCESS ON *.* TO 'backup_user'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> EXIT;
Why not just use root? If your backup script's credentials are ever exposed (read-world config file, leaked cron output in logs), a backup user with SELECT/LOCK can only read data. Root can drop every database, create new admin accounts, and read system tables containing password hashes.
Credentials File — Never Put Passwords in the Command Line
Running mysqldump -u backup_user -pYourPassword bookshop > dump.sql exposes the password in your shell history, in ps aux output, and potentially in cron logs. The correct approach is a credentials file.
# Create the credentials file (as root, in a safe location)
$ sudo nano /etc/mysql/backup.cnf
[client]
user = backup_user
password = BackupP@ss!9
# Lock it down — only root can read this file
$ sudo chmod 600 /etc/mysql/backup.cnf
$ sudo chown root:root /etc/mysql/backup.cnf
# Test it — no password prompt, no password in the command
$ sudo mysqldump --defaults-file=/etc/mysql/backup.cnf bookshop | tail -3
UNLOCK TABLES;
-- Dump completed on 2026-06-15 14:30:01
The Flags That Matter
| Flag | What it does | Use? |
|---|---|---|
| --single-transaction | Takes a consistent snapshot of InnoDB tables using a transaction, avoiding table locks. Allows reads and writes to continue during the dump. | Always for InnoDB |
| --routines (-R) | Include stored procedures and functions in the dump. Not included by default. | Yes, if you use them |
| --events (-E) | Include scheduled events. Not included by default. | Yes, if you use them |
| --triggers | Include triggers. Included by default — shown here for clarity. | Default on — no action needed |
| --databases db1 db2 | Dump multiple named databases. Includes CREATE DATABASE statements, so restore creates the database automatically. | For multi-db dumps |
| --all-databases (-A) | Dump every database, including mysql (user accounts). Good for full server backups. | For full server backup |
| --no-tablespaces | Omits tablespace CREATE statements. Required if your backup user doesn't have PROCESS privilege and you encounter INFORMATION_SCHEMA errors. | Add if you see errors |
| --set-gtid-purged=OFF | Suppress GTID comments in the dump. Needed when restoring between servers that use different GTID configurations. | Add if using replication |
| --lock-tables | Locks tables one by one before dumping. For MyISAM tables only. Blocks all writes during the dump. | Avoid — use --single-transaction instead |
Running mysqldump
# ── Single database, uncompressed ────────────────────────────────
$ sudo mysqldump \
--defaults-file=/etc/mysql/backup.cnf \
--single-transaction \
--routines \
--events \
bookshop > /var/backups/mysql/bookshop.sql
# ── Single database, gzip compressed (much smaller) ──────────────
$ sudo mysqldump \
--defaults-file=/etc/mysql/backup.cnf \
--single-transaction \
--routines \
--events \
bookshop | gzip > /var/backups/mysql/bookshop.sql.gz
# ── With a datestamp in the filename ─────────────────────────────
$ STAMP=$(date +%Y-%m-%d_%H%M)
$ sudo mysqldump \
--defaults-file=/etc/mysql/backup.cnf \
--single-transaction \
--routines --events \
bookshop | gzip > /var/backups/mysql/bookshop_${STAMP}.sql.gz
# Result: bookshop_2026-06-15_1430.sql.gz
# ── All databases (full server backup) ───────────────────────────
$ sudo mysqldump \
--defaults-file=/etc/mysql/backup.cnf \
--single-transaction \
--routines --events \
--all-databases | gzip > /var/backups/mysql/all_databases_${STAMP}.sql.gz
# ── Specific tables only ──────────────────────────────────────────
$ sudo mysqldump \
--defaults-file=/etc/mysql/backup.cnf \
--single-transaction \
bookshop books authors > /var/backups/mysql/bookshop_tables.sql
# ── Verify the dump completed (check for the final line) ─────────
$ zcat /var/backups/mysql/bookshop_${STAMP}.sql.gz | tail -3
UNLOCK TABLES;
-- Dump completed on 2026-06-15 14:30:01
# ── Check compressed file size ────────────────────────────────────
$ ls -lh /var/backups/mysql/
-rw-r--r-- 1 root root 284K Jun 15 14:30 bookshop_2026-06-15_1430.sql.gz
# Suspiciously tiny? 0 bytes? — something went wrong
Restoring from a Dump
Restoring is the reverse operation — you pipe the SQL file back into the mysql client. The database must exist before you restore a single-database dump (one not made with --databases); it's created automatically for dumps made with --databases or --all-databases.
# ── Restore a single-database dump (database must exist first) ────
$ sudo mysql -e "CREATE DATABASE IF NOT EXISTS bookshop CHARACTER SET utf8mb4;"
$ sudo mysql bookshop < /var/backups/mysql/bookshop.sql
# ── Restore a compressed dump ─────────────────────────────────────
$ sudo mysql -e "CREATE DATABASE IF NOT EXISTS bookshop CHARACTER SET utf8mb4;"
$ zcat /var/backups/mysql/bookshop_2026-06-15_1430.sql.gz | sudo mysql bookshop
# ── Restore a --databases dump (creates the database automatically)
$ zcat /var/backups/mysql/bookshop_2026-06-15_1430.sql.gz | sudo mysql
# ── Restore all databases from a full server backup ───────────────
$ zcat /var/backups/mysql/all_databases_2026-06-15_1430.sql.gz | sudo mysql
# ── Restore to a DIFFERENT database name ─────────────────────────
$ sudo mysql -e "CREATE DATABASE bookshop_restored CHARACTER SET utf8mb4;"
$ zcat /var/backups/mysql/bookshop_2026-06-15_1430.sql.gz \
| sudo mysql bookshop_restored
# Useful for: testing a restore without touching the live database
# ── Monitor restore progress on large databases ───────────────────
# (pv = pipe viewer, shows throughput and ETA)
$ sudo apt install pv
$ pv /var/backups/mysql/bookshop.sql | sudo mysql bookshop
Restoring into an existing database appends to or overwrites existing data. Because a single-table dump includes
DROP TABLE IF EXISTS before each CREATE TABLE, restoring will DROP and recreate every table in the dump — losing any data added after the backup was taken. If you need to keep current data, restore to a temporary database name first, then selectively move rows.
The Automated Backup Script
Setup Walkthrough · Automated Daily Backups
Create a backup directory, write the script, test it, then schedule it with cron.
1
Create the backup directory with secure permissions.
$ sudo mkdir -p /var/backups/mysql
$ sudo chown root:root /var/backups/mysql
$ sudo chmod 700 /var/backups/mysql
# Only root can read/write this directory
2
Write the backup script.
$ sudo nano /usr/local/sbin/mysql_backup.sh
3
Script contents — paste this and adjust the variables at the top.
#!/bin/bash
# ── mysql_backup.sh — daily MySQL backup with rotation ────────────
# Adjust the variables below for your setup.
# ── Configuration ─────────────────────────────────────────────────
DEFAULTS_FILE="/etc/mysql/backup.cnf" # credentials file from earlier
BACKUP_DIR="/var/backups/mysql"
DATABASES=("bookshop") # add more: ("bookshop" "blog" "shop")
KEEP_DAYS=7 # keep 7 days of local backups
LOG_FILE="/var/log/mysql_backup.log"
# ── Setup ──────────────────────────────────────────────────────────
STAMP=$(date +%Y-%m-%d_%H%M)
ERRORS=0
log() { echo "[$(date '+%Y-%m-%d %H:%M:%S')] $*" | tee -a "$LOG_FILE"; }
log "=== Backup started ==="
# ── Dump each database ─────────────────────────────────────────────
for DB in "${DATABASES[@]}"; do
OUTFILE="${BACKUP_DIR}/${DB}_${STAMP}.sql.gz"
log "Dumping database: $DB → $OUTFILE"
mysqldump \
--defaults-file="$DEFAULTS_FILE" \
--single-transaction \
--routines \
--events \
"$DB" | gzip > "$OUTFILE"
# Verify the dump completed (check for "Dump completed" line)
if zcat "$OUTFILE" 2>/dev/null | tail -2 | grep -q "Dump completed"; then
SIZE=$(du -sh "$OUTFILE" | cut -f1)
log " ✓ $DB: OK ($SIZE)"
else
log " ✗ $DB: FAILED or incomplete — dump may be corrupted"
ERRORS=$((ERRORS + 1))
fi
done
# ── Rotate old backups (delete files older than KEEP_DAYS) ────────
log "Rotating backups older than ${KEEP_DAYS} days..."
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +${KEEP_DAYS} -delete
log "Rotation complete."
# ── Summary ────────────────────────────────────────────────────────
if [ "$ERRORS" -eq 0 ]; then
log "=== Backup completed successfully ==="
else
log "=== Backup completed with $ERRORS ERROR(S) — check log ==="
exit 1 # non-zero exit lets cron/monitoring detect failure
fi
4
Make it executable and test it manually before scheduling.
$ sudo chmod 700 /usr/local/sbin/mysql_backup.sh
$ sudo /usr/local/sbin/mysql_backup.sh
[2026-06-15 14:30:00] === Backup started ===
[2026-06-15 14:30:00] Dumping database: bookshop → /var/backups/mysql/bookshop_2026-06-15_1430.sql.gz
[2026-06-15 14:30:01] ✓ bookshop: OK (46K)
[2026-06-15 14:30:01] Rotating backups older than 7 days...
[2026-06-15 14:30:01] Rotation complete.
[2026-06-15 14:30:01] === Backup completed successfully ===
$ ls -lh /var/backups/mysql/
-rw-r--r-- 1 root root 46K Jun 15 14:30 bookshop_2026-06-15_1430.sql.gz
5
Schedule with cron — run daily at 2 AM.
$ sudo crontab -e
# Add this line:
0 2 * * * /usr/local/sbin/mysql_backup.sh
Format: minute hour day month weekday command. 0 2 * * * = minute 0, hour 2 (2 AM), every day.
6
Verify cron is running the job.
$ sudo tail -f /var/log/mysql_backup.log
# After 2 AM the next day, you should see a new backup entry here
# Also check cron's own log for errors
$ grep "mysql_backup" /var/log/syslog | tail -5
Jun 16 02:00:01 webserver CRON[8422]: (root) CMD (/usr/local/sbin/mysql_backup.sh)
The script exits with code 1 on failure. If you set up logwatch (Chapter 8 of the Web Security course), it will catch cron job failures in its daily report.
Offsite Copies with rclone
Local backups protect against accidental deletion and software errors. An offsite copy protects against hardware failure, fire, theft, and ransomware. rclone can sync your backup directory to any S3-compatible storage, Backblaze B2, Google Drive, Dropbox, or a remote SSH server.
# Install rclone
$ sudo apt install rclone
# Configure a remote (interactive wizard — run as your own user, not root)
$ rclone config
# Follow the wizard to set up your storage provider (Backblaze B2 shown below):
# n → new remote
# name: b2
# storage type: 5 (Backblaze B2)
# account: your_account_id
# key: your_application_key
# → leave other settings as default
# Test the connection and list your buckets
$ rclone lsd b2:
# Copy local backups to the remote (dry run first)
$ rclone copy --dry-run /var/backups/mysql b2:your-bucket-name/mysql-backups/
# If dry run looks right, run it for real
$ rclone copy /var/backups/mysql b2:your-bucket-name/mysql-backups/
Add the rclone sync to your backup script, after the local dumps complete:
# Add to mysql_backup.sh, after the rotation block:
log "Syncing backups to offsite storage..."
if rclone copy "$BACKUP_DIR" b2:your-bucket-name/mysql-backups/ \
--log-file="$LOG_FILE" --log-level=INFO; then
log "Offsite sync: OK"
else
log "Offsite sync: FAILED — check rclone config and connectivity"
ERRORS=$((ERRORS + 1))
fi
Testing Your Backups — The Restore Test
Once a month, restore your most recent backup to a test database and verify the data looks correct. This confirms the dump is valid and the restore procedure works — before you actually need it in an emergency.
# Find the most recent backup
$ ls -lt /var/backups/mysql/*.sql.gz | head -3
-rw-r--r-- 1 root root 46K Jun 15 02:00 bookshop_2026-06-15_0200.sql.gz
# Restore to a test database (leave the real database untouched)
$ sudo mysql -e "DROP DATABASE IF EXISTS bookshop_test; \
CREATE DATABASE bookshop_test CHARACTER SET utf8mb4;"
$ zcat /var/backups/mysql/bookshop_2026-06-15_0200.sql.gz \
| sudo mysql bookshop_test
# Verify data looks right
$ sudo mysql -e "USE bookshop_test; SHOW TABLES; SELECT COUNT(*) FROM books;"
+------------------------+
| Tables_in_bookshop_test|
+------------------------+
| authors |
| books |
| customers |
| orders |
+------------------------+
+----------+
| COUNT(*) |
+----------+
| 127 |
+----------+
# Clean up the test database
$ sudo mysql -e "DROP DATABASE bookshop_test;"
Binary Log and Point-in-Time Recovery
A mysqldump captures the database as it was at the moment of the dump. Binary logs record every change made after that point. Together they enable point-in-time recovery (PITR): restore a Monday backup, then replay Wednesday morning's binlog events up to the exact moment before someone ran DELETE FROM orders; without a WHERE clause.
# Check if binary logging is on (it should be by default on Ubuntu 22.04+)
mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
# List current binary log files
mysql> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| binlog.000001 | 1048576 |
| binlog.000002 | 209712 |
+------------------+-----------+
# For a PITR scenario: restore the mysqldump first, then replay binlog up to
# a point in time, skipping the bad query
#
# 1. Restore from dump:
$ zcat bookshop_2026-06-15_0200.sql.gz | sudo mysql bookshop
#
# 2. Find the position of the bad query in the binlog:
$ sudo mysqlbinlog /var/lib/mysql/binlog.000002 | grep -A5 "DELETE FROM orders"
#
# 3. Replay up to just BEFORE the bad query (by position):
$ sudo mysqlbinlog --stop-position=28774 /var/lib/mysql/binlog.000002 \
| sudo mysql bookshop
#
# 4. Then replay AFTER the bad query (skip it):
$ sudo mysqlbinlog --start-position=28920 /var/lib/mysql/binlog.000002 \
| sudo mysql bookshop
Include the binlog position in your dumps. Add
--master-data=2 (MySQL 5.7) or --source-data=2 (MySQL 8.0) to your mysqldump command — it adds a comment near the top of the dump file showing exactly which binlog file and position the dump corresponds to. This is the starting point for PITR.
Backup Tool Comparison
mysqldump
Single-threaded SQL export. Human-readable output, easy to inspect, universally compatible. Slightly slower on large databases. Ships with MySQL.
Recommended for most setups
mysqlpump
Parallel version of mysqldump. Faster for large databases with multiple tables. Being deprecated in MySQL 8.4+ — Oracle recommends MySQL Shell instead.
Avoid for new setups
MySQL Shell dump
Parallel, chunked, fastest option. Uses
util.dumpInstance() or util.dumpSchemas(). Requires MySQL Shell to be installed. Not a SQL file — uses a proprietary format (load with util.loadDump()).For very large databases
Recommended Backup Strategy for a Home Server
Daily — 2 AM
Full mysqldump
All application databases, gzip compressed, timestamped. Local retention: 7 days.
Daily — after dump
Offsite sync
rclone copy to B2/S3/SFTP. Offsite retention: 30 days.
Weekly
Review log
Check /var/log/mysql_backup.log for errors. Verify recent backup file sizes look reasonable.
Monthly
Test restore
Restore latest dump to bookshop_test, verify row counts, drop test db. Takes under 5 minutes.
Troubleshooting
mysqldump: Couldn't execute 'SELECT COLUMN_STATISTICS...' — Unknown table 'COLUMN_STATISTICS'
This happens when a dump made with MySQL 8.0 is being fed to an older MySQL 5.7 client or when the mysql client and server versions mismatch. Quick fix: add
--column-statistics=0 to the mysqldump command. Or ensure the mysql client matches the server version: mysql --version should match mysqld --version.
mysqldump hangs / never finishes
mysqldump is waiting for a table lock that another query holds. Check:
SHOW PROCESSLIST; — look for rows with a long-running query in the State column. If another process is holding a lock, KILL <id>; it. Long-term fix: always use --single-transaction for InnoDB tables — it uses a snapshot and doesn't need table locks. If the hang is on a MyISAM table, schedule the backup when traffic is low.
Restore fails: ERROR 1007 — Can't create database, database exists
The dump was made with
--databases or --all-databases, so it includes a CREATE DATABASE statement — and that database already exists. Either drop it first (DROP DATABASE bookshop; — careful, data loss!) or add --force to the mysql restore command to skip errors. If you want to restore to the same database without dropping it first, use a single-database dump (without --databases) and specify the database name in the restore command.
Dump file is 0 bytes or ends abruptly without "Dump completed"
The dump failed silently. Common causes: disk full during dump (
df -h /var/backups), mysqldump lost the connection partway through, or the backup user lacked privileges on a table. Check: sudo mysqldump --defaults-file=/etc/mysql/backup.cnf bookshop 2>&1 | tail -20 — run without gzip and pipe to tail to see any error messages. Fix the root cause, then rerun. Add set -e to your backup script to make it exit on any error.
Cron backup runs but produces no file (script works fine when run manually)
Cron runs with a minimal environment — it may not find
mysqldump if it's not on the default PATH. Fix by using the full path in the cron job or script: /usr/bin/mysqldump instead of just mysqldump. Also confirm the cron job is running as root (use sudo crontab -e to edit root's crontab, not your user's). Check the cron log: grep mysql_backup /var/log/syslog.
Quick Reference — Chapter 5
| Command | Purpose |
|---|---|
| mysqldump --defaults-file=/etc/mysql/backup.cnf --single-transaction --routines --events db | gzip > db.sql.gz | Full production-quality dump of one database, compressed |
| mysqldump ... --all-databases | gzip > all.sql.gz | Full server backup including all databases and user accounts |
| zcat db.sql.gz | tail -3 | Verify the dump completed — look for "Dump completed on" in the last lines |
| zcat db.sql.gz | sudo mysql dbname | Restore a compressed single-database dump (database must already exist) |
| sudo mysql -e "CREATE DATABASE dbname CHARACTER SET utf8mb4;" | Create the target database before restoring a single-database dump |
| find /var/backups/mysql -name "*.sql.gz" -mtime +7 -delete | Delete backups older than 7 days |
| rclone copy /var/backups/mysql b2:bucket/mysql/ | Sync local backups to Backblaze B2 (or any rclone remote) |
| sudo crontab -e | Edit root's crontab to schedule the backup script |
| SHOW BINARY LOGS; | List binary log files on the server (needed for PITR) |
| mysqlbinlog --stop-position=N binlog.000002 | sudo mysql db | Replay binlog events up to a specific position (point-in-time recovery) |
| tail -f /var/log/mysql_backup.log | Monitor backup job output |