Backing Up & Restoring MySQL

A backup is a copy of your data you can use to bring a database back to life after a mistake, a crash, or a bad upgrade. For MySQL (a popular open-source relational database), the classic tool is mysqldump (a command that reads your database and writes its contents out as a plain text file full of SQL statements). This page shows you how to dump one database, dump all of them, restore from a dump, and run a scheduled compressed backup. Most importantly, it shows you how to verify that your backups actually work, because a backup you have never tested is just a hope, not a safety net.

What mysqldump actually produces

mysqldump connects to the MySQL server, reads your tables, and prints a series of SQL statements (CREATE TABLE, INSERT INTO, and so on) to standard output. That stream is usually saved to a .sql file. To restore, you simply feed that file back into the mysql client, which replays every statement and rebuilds the data.

This format is called a logical backup (a backup made of SQL commands, not raw disk files). It is portable, human-readable, and works across MySQL versions. The trade-off is that it is slower than copying raw files for very large databases (hundreds of gigabytes), but for the vast majority of servers it is the right default.

Backing up a single database

Run mysqldump and redirect its output into a file. On Ubuntu, MySQL is usually configured so the root system user can log in without typing a password using sudo.

sudo mysqldump --single-transaction --quick myapp > /var/backups/myapp-2026-06-15.sql

Output:

(no output on success — the SQL is written to the file)

Check that the file actually contains something:

ls -lh /var/backups/myapp-2026-06-15.sql
head -n 5 /var/backups/myapp-2026-06-15.sql

Output:

-rw-r--r-- 1 root root 4.2M Jun 15 09:14 /var/backups/myapp-2026-06-15.sql
-- MySQL dump 10.13  Distrib 8.0.39, for Linux (x86_64)
--
-- Host: localhost    Database: myapp
-- ------------------------------------------------------
-- Server version       8.0.39-0ubuntu0.24.04.1

Why —single-transaction matters

If your tables use the InnoDB storage engine (the default engine in modern MySQL, which supports transactions), add --single-transaction. This tells mysqldump to take a consistent snapshot inside one transaction, so the dump reflects a single point in time even while your application keeps writing to the database. Without it, MySQL locks tables to stay consistent, which can freeze writes on a busy production server.

When to use this: Always use --single-transaction for InnoDB databases on a live server. Do NOT use it if your tables are MyISAM (an older engine that does not support transactions) — there it has no effect and you need --lock-tables instead. To check your engine, run SHOW TABLE STATUS; inside the mysql client.

Option	What it does	When to use
--single-transaction	Consistent snapshot without locking writes	InnoDB tables on live servers
--quick	Streams rows instead of buffering in memory	Large tables (avoids high RAM use)
--routines	Includes stored procedures and functions	Databases that use stored routines
--events	Includes scheduled events	Databases using the MySQL event scheduler
--all-databases	Dumps every database on the server	Full-server backups

Backing up all databases

To capture the entire server in one file, use --all-databases. This also includes the system databases, so a restore can recreate users and grants.

sudo mysqldump --single-transaction --quick --routines --events --all-databases > /var/backups/mysql-all-2026-06-15.sql

Output:

(no output on success)

Restoring from a dump

Restoring is the reverse: pipe the SQL file into the mysql client. For a single-database dump, the database must already exist (the dump file does not always create it), so create it first if needed.

sudo mysql -e "CREATE DATABASE IF NOT EXISTS myapp;"
sudo mysql myapp < /var/backups/myapp-2026-06-15.sql

Output:

(no output on success — the database is rebuilt)

For an --all-databases dump, you do not name a database, because the dump itself switches between databases:

sudo mysql < /var/backups/mysql-all-2026-06-15.sql

Gotcha: Restoring an --all-databases dump can overwrite the mysql.user table, changing or removing existing accounts. Test this kind of restore on a throwaway server first, never on production by accident.

A scheduled, compressed backup script

In real operations you want backups to run automatically every night, compressed to save disk space, and old ones cleaned up. SQL text compresses extremely well, so piping mysqldump straight into gzip typically shrinks the file by 80-90%.

Create the script at /usr/local/bin/mysql-backup.sh:

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

BACKUP_DIR="/var/backups/mysql"
KEEP_DAYS=14
STAMP="$(date +%F_%H-%M)"

mkdir -p "$BACKUP_DIR"

# Dump every database, compress on the fly.
mysqldump --single-transaction --quick --routines --events --all-databases \
  | gzip > "$BACKUP_DIR/mysql-all-$STAMP.sql.gz"

# Delete backups older than KEEP_DAYS days.
find "$BACKUP_DIR" -name 'mysql-all-*.sql.gz' -mtime +"$KEEP_DAYS" -delete

echo "Backup complete: $BACKUP_DIR/mysql-all-$STAMP.sql.gz"

Make it executable and give the root system user credentials so it can run unattended. The cleanest way is a credentials file (mode 600) read automatically by MySQL clients:

sudo chmod +x /usr/local/bin/mysql-backup.sh
sudo tee /root/.my.cnf >/dev/null <<'EOF'
[client]
user=root
password=YOUR_ROOT_PASSWORD
EOF
sudo chmod 600 /root/.my.cnf

Run it once by hand to confirm it works:

sudo /usr/local/bin/mysql-backup.sh

Output:

Backup complete: /var/backups/mysql/mysql-all-2026-06-15_02-30.sql.gz

Schedule it nightly with cron (a Linux service that runs commands on a timer). Edit root’s crontab:

sudo crontab -e

Add this line to run every day at 02:30:

30 2 * * * /usr/local/bin/mysql-backup.sh >> /var/log/mysql-backup.log 2>&1

To restore a compressed dump, decompress it on the way into mysql:

gunzip < /var/backups/mysql/mysql-all-2026-06-15_02-30.sql.gz | sudo mysql

Always verify your restores

A backup is only real once you have restored it successfully. Test restores into a separate database (never overwrite production) and confirm the data is there.

sudo mysql -e "CREATE DATABASE restore_test;"
sudo mysql restore_test < /var/backups/myapp-2026-06-15.sql
sudo mysql restore_test -e "SELECT COUNT(*) AS rows_in_users FROM users;"

Output:

+---------------+
| rows_in_users |
+---------------+
|          1842 |
+---------------+

If the row counts match production, your backup is trustworthy. Drop the test database afterward with sudo mysql -e "DROP DATABASE restore_test;".

Best Practices

• Always pass --single-transaction for InnoDB databases so live writes do not block and the snapshot stays consistent.
• Compress dumps with gzip and prune old ones (the find ... -mtime step) so backups do not silently fill the disk.
• Store credentials in a chmod 600 file like /root/.my.cnf, never as plain text on the command line where ps could expose them.
• Run a real test restore into a throwaway database on a schedule — an untested backup is not a backup.
• Keep at least one copy off the server (object storage or a remote host) so a disk failure does not destroy both the data and its backups.
• Log backup runs to /var/log/mysql-backup.log and check it regularly, because silent cron failures are how data loss sneaks up on you.

Related Topics

• Installing MySQL on Ubuntu
• Securing MySQL with mysql_secure_installation
• Creating MySQL Users & Databases
• Backing Up & Restoring PostgreSQL
• Automating Database Backups