How to Restore a MySQL Database Backup

The worst phone call any DBA can get is “production is down and we need last night’s backup restored right now.” I’ve had that call. What made it survivable wasn’t luck — it was having practiced restores ahead of time, on a schedule, so that when it actually mattered I wasn’t reading documentation for the first time under pressure. I want to walk through restoring MySQL backups the way I actually do it, including the mistakes that taught me to be careful.

The Golden Rule: Untested Backups Are Not Backups

Before any restore technique, I want to say this plainly: a backup you have never test-restored is a hope, not a backup. I schedule quarterly (at minimum) restore drills into a sandbox environment specifically to confirm the backup files are valid, the restore process actually works end-to-end, and the restored data matches expectations.

Restoring from a Logical Backup (mysqldump)

If your backup was created with mysqldump (covered in depth in my companion article on backups), restoring it is straightforward because the backup file is just plain SQL.

mysql -u root -p mydatabase < mydatabase_backup.sql

For a full-server dump that includes multiple databases:

mysql -u root -p < full_server_backup.sql

If the backup file is compressed:

gunzip < mydatabase_backup.sql.gz | mysql -u root -p mydatabase

Restoring to a Fresh Database

If the target database doesn’t exist yet, I create it first (unless the dump itself includes CREATE DATABASE statements, which mysqldump --databases includes by default):

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

mysql -u root -p mydatabase < mydatabase_backup.sql

Restoring a Single Table from a Full Dump

One of the trickiest real-world scenarios: someone accidentally truncated one table, and I only need to restore that one table from a full-database backup file, not the whole database. Since a plain mysqldump file is just sequential SQL, I extract the relevant section with sed:

sed -n '/-- Table structure for table `orders`/,/-- Table structure for table `products`/p' full_backup.sql > orders_only.sql

I then review the extracted file carefully before running it, trimming anything from the next table’s section that got captured accidentally, since this extraction is pattern-based and not always perfectly precise.

mysql -u root -p mydatabase < orders_only.sql

Restoring from a Physical Backup (Percona XtraBackup)

Physical backups restore dramatically faster for large databases because they copy the actual data files rather than replaying SQL statement by statement.

# Prepare the backup (applies the redo log to make it consistent)
xtrabackup --prepare --target-dir=/backups/full_backup/

# Stop MySQL before restoring
sudo systemctl stop mysql

# Move or clear the existing data directory
sudo mv /var/lib/mysql /var/lib/mysql_old

# Restore the prepared backup files
xtrabackup --copy-back --target-dir=/backups/full_backup/

# Fix ownership (xtrabackup files are typically owned by root after copy)
sudo chown -R mysql:mysql /var/lib/mysql

# Start MySQL
sudo systemctl start mysql

The --prepare step is critical and easy to forget — a raw, unprepared XtraBackup directory is not yet transactionally consistent, since it may have been captured mid-write. Skipping --prepare and restoring directly can leave the restored database in a corrupted state.

Restoring Incremental Backups

If your backup strategy includes a full backup plus incremental backups (covered in my backup article), restoring requires applying them in the correct order:

# Prepare the full backup, but keep it ready to accept incrementals
xtrabackup --prepare --apply-log-only --target-dir=/backups/full_backup/

# Apply the first incremental
xtrabackup --prepare --apply-log-only --target-dir=/backups/full_backup/ --incremental-dir=/backups/incremental_1/

# Apply the final incremental (no --apply-log-only on the last one)
xtrabackup --prepare --target-dir=/backups/full_backup/ --incremental-dir=/backups/incremental_2/

# Then copy-back as with a full restore
xtrabackup --copy-back --target-dir=/backups/full_backup/

Getting the --apply-log-only flag right on all but the final step is the detail that trips people up most — omit it too early and later incrementals won’t apply cleanly.

Point-in-Time Recovery Using Binary Logs

A full backup only gets you to the moment the backup was taken. To recover to a specific point in time — say, right before an accidental DELETE — I combine a full backup restore with replaying binary logs up to (but not including) the damaging statement.

# First, restore the most recent full backup as shown above

# Identify the exact binlog position of the harmful statement
mysqlbinlog --start-datetime="2026-07-30 09:00:00" --stop-datetime="2026-07-30 09:45:00" mysql-bin.000123 | less

Once I’ve identified the exact position or timestamp just before the damaging statement:

mysqlbinlog --stop-datetime="2026-07-30 09:42:17" mysql-bin.000123 | mysql -u root -p

This replays every transaction from the binary log up to that exact timestamp, effectively undoing only the damage and nothing else — assuming the full backup restore already brought the database up to the point where that binlog file begins.

flowchart LR
    A[Restore last full backup] --> B[Identify binlog position of incident]
    B --> C[Replay binlogs from backup time up to just before incident]
    C --> D[Database restored to point-in-time, minus the damaging statement]

Verifying a Restore

I never consider a restore “done” until I’ve verified it:

-- Check row counts against expectations
SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydatabase';

-- Spot-check specific known records
SELECT * FROM orders WHERE order_id = 12345;

-- Confirm foreign key integrity
SELECT COUNT(*) FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id WHERE c.customer_id IS NULL;

I also check application-level smoke tests — logging into the restored environment and confirming core workflows function, not just that raw row counts look plausible.

Restoring to a Different Server (Migration Scenario)

The same restore techniques double as migration tools. When I move a database to a new server, I restore the same backup file there, then update connection strings, users, and grants (since mysqldump backups of user grants need to be restored separately from mysql.user or via SHOW GRANTS output captured ahead of time).

mysqldump --no-data --routines --triggers --events mydatabase > schema_only.sql
mysqldump --no-create-info mydatabase > data_only.sql

Splitting schema and data like this gives me more control during complex migrations — I can adjust the schema for a new server’s collation defaults or storage engine settings before loading the data.

Real-World DBA Scenarios

• Accidental data deletion: point-in-time recovery is the standard playbook for “someone ran an UPDATE without a WHERE clause.”
• Disaster recovery drills: quarterly full restore tests into an isolated environment, timed to measure actual Recovery Time Objective (RTO) against the business’s requirements.
• Environment refresh: restoring a scrubbed production backup into staging so developers test against realistic data volumes.
• Server migration: restoring backups onto new hardware or a new cloud instance as part of planned infrastructure upgrades.

Common Mistakes

• Restoring over a live production database without first confirming you’re targeting the correct server — I always double, triple-check hostnames before running a restore command.
• Forgetting to stop the MySQL service before a physical (XtraBackup) restore, leading to file conflicts.
• Skipping the --prepare step on a physical backup, resulting in a database that appears to start but is actually inconsistent.
• Not testing restores until an actual emergency, discovering only then that a backup file was corrupted or incomplete.
• Assuming binary logs are automatically retained forever — without proper binlog_expire_logs_seconds configuration, older logs needed for point-in-time recovery may have already been purged.

Troubleshooting Table

FAQs

Do I need to stop MySQL before restoring a mysqldump (logical) backup? No — logical restores run as normal SQL against a running server, though for a full-server restore I typically do it during a maintenance window to avoid conflicting writes.

Do I need to stop MySQL before restoring a physical (XtraBackup) backup? Yes — physical restores replace the actual data directory files, which requires MySQL to be stopped first.

Can I restore just one table from a full mysqldump file? Yes, by extracting that table’s section from the dump file (commonly with sed or a small script) and running just that portion.

What is point-in-time recovery, and when do I need it? It’s restoring a full backup and then replaying binary logs up to a specific moment, used when you need to recover to a point after your last full backup but before a damaging event — like an accidental deletion.

How do I verify a restore was successful? Check row counts against expectations, spot-check known records, verify referential integrity, and run application-level smoke tests against the restored environment.

Interview Questions

1. What’s the difference between restoring a logical backup and a physical backup?
2. Why is the --prepare step mandatory before restoring an XtraBackup physical backup?
3. How would you perform point-in-time recovery after an accidental DELETE statement?
4. How would you restore a single table from a full mysqldump file without restoring the entire database?
5. What’s the difference between --apply-log-only used mid-sequence versus the final step of an incremental backup restore?
6. Why should foreign key checks be disabled during certain restores, and re-enabled afterward?
7. What would your verification checklist look like immediately after a production restore?

Optimization Tips

• For very large databases, prefer physical backup/restore (XtraBackup) over logical (mysqldump) restores — replaying millions of INSERT statements is far slower than copying data files directly.
• Keep binary log retention long enough to cover your realistic recovery window, balanced against disk space — I typically retain at least 7 days of binlogs in addition to daily full backups.
• Automate restore verification scripts (row count checks, checksum comparisons) so a restore drill produces a clear pass/fail signal rather than requiring manual inspection every time.
• Practice restores on a schedule, not just when documentation says to — muscle memory under pressure is what actually saves you during a real incident.

Summary and Key Takeaways

Restoring a MySQL backup successfully depends far more on preparation than on the specific commands — knowing whether you’re dealing with a logical or physical backup, whether you need full recovery or point-in-time recovery, and having already tested the process before an actual emergency. Logical restores (mysqldump) are simple SQL replays; physical restores (XtraBackup) are faster for large databases but require the --prepare step and stopping MySQL first; and binary log replay lets you recover to the exact moment before a mistake happened. The single habit that’s made the biggest difference for me is scheduling regular restore drills, so a real incident is a matter of following a well-practiced runbook rather than improvising under pressure.

References

• MySQL 8.0 Reference Manual — Backup and Recovery
• MySQL 8.0 Reference Manual — Point-in-Time (Incremental) Recovery
• MySQL 8.0 Reference Manual — mysqlbinlog — Utility for Processing Binary Log Files
• Percona XtraBackup Documentation