Restoring and Verifying a Backup

A backup is only useful if you can restore it, and you know the result is correct. This lesson restores the dump from mysqldump for real and verifies it.

The basic restore

A dump is a SQL script. The mysql client runs it:

1
2
3
4
5
6
7
8
9

# the dump was made with --databases, so it contains CREATE DATABASE and USE
mysql -u root -p < shop_2026-10-05.sql

# a compressed dump
gunzip -c shop_2026-10-05.sql.gz | mysql -u root -p

# a dump made WITHOUT --databases: create the database first, then name it
mysql -u root -p -e "CREATE DATABASE shop_restored"
mysql -u root -p shop_restored < shop_tables.sql

Always use the mysql client, not a program that sends the whole file as one statement. The dump uses client commands such as DELIMITER ;;, which only the mysql client understands. (A web tool that cannot handle DELIMITER fails on stored routines.)

Use the same character set as the dump: mysql --default-character-set=utf8mb4, especially on Windows.

Restore somewhere safe first

DROP TABLE IF EXISTS is in every dump, so restoring over a live database replaces its tables. Never test a restore on the production server. Restore on a different server, or into a different database name:

1
2

# restore the same dump into a copy named shop_copy (the dump uses the name in backticks)
sed 's/`shop`/`shop_copy`/g' shop_2026-10-05.sql | mysql -u root -p

This is a quick way to make a copy in the same server. (The sed replacement changes every ` shop `, so use it only for a quick test, never in a script that must handle data that contains that text.)

Verify: counts, sums and checksums

Compare the original and the restored copy. The numbers below are real results from the MySQL 8.4 test server:

1
2
3

SELECT 'original' AS source, COUNT(*) AS orders, SUM(total) AS total FROM shop.orders
UNION ALL
SELECT 'restored', COUNT(*), SUM(total) FROM shop_copy.orders;

1
2
3
4
5
6

+----------+--------+---------+
| source   | orders | total   |
+----------+--------+---------+
| original |      6 | 1559.75 |
| restored |      6 | 1559.75 |
+----------+--------+---------+

Counts and sums catch missing rows. CHECKSUM TABLE computes a checksum of the whole table, so any differing value shows up:

1

CHECKSUM TABLE shop.customers, shop_copy.customers, shop.orders, shop_copy.orders;

1
2
3
4
5
6
7
8

+---------------------+------------+
| Table               | Checksum   |
+---------------------+------------+
| shop.customers      | 3255710406 |
| shop_copy.customers | 3255710406 |
| shop.orders         | 2768047267 |
| shop_copy.orders    | 2768047267 |
+---------------------+------------+

The checksums of the original and the restored table are identical, so the data is the same, row for row. (Checksums depend on the row format and version, so compare tables from the same or similar versions, and treat a difference as a reason to investigate, not always proof of damage. CHECKSUM TABLE reads the whole table, which is slow and heavy on big tables, so run it on a restored copy, not on production.)

Also check the other objects the dump should contain:

1
2
3

SELECT shop.order_count(1) AS original_fn, shop_copy.order_count(1) AS restored_fn;
SELECT * FROM shop_copy.paid_orders;
SHOW FULL TABLES FROM shop_copy;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

+-------------+-------------+
| original_fn | restored_fn |
+-------------+-------------+
|           2 |           2 |
+-------------+-------------+

+----+-------------+--------+
| id | customer_id | total  |
+----+-------------+--------+
|  1 |           1 | 120.50 |
|  2 |           1 |  80.00 |
|  4 |           3 |  45.25 |
+----+-------------+--------+

+---------------------+------------+
| Tables_in_shop_copy | Table_type |
+---------------------+------------+
| customers           | BASE TABLE |
| orders              | BASE TABLE |
| paid_orders         | VIEW       |
+---------------------+------------+

The function and the view work after the restore, and the object list is complete. For a real drill, add an application smoke test: log in, load a page, place a test order.

Restore a single table

A dump of a whole database contains a CREATE TABLE and the INSERTs of each table between comment markers. To restore only customers, extract that section:

1
2

sed -n '/^-- Table structure for table `customers`/,/^-- Table structure for table `orders`/p' shop_2026-10-05.sql > customers_only.sql
mysql -u root -p shop < customers_only.sql

This works because mysqldump writes each table in order. It is easier if you dump tables separately or can restore the full dump to a temporary database and then copy only the table you need:

1
2

-- after restoring the whole dump into shop_copy
INSERT INTO shop.customers SELECT * FROM shop_copy.customers WHERE id = 3;

Restoring into a temporary database and copying selected rows is the usual way to undo a mistaken DELETE or UPDATE without touching anything else.

What the dump does not restore

• Users and grants, unless you dumped the mysql schema. Recreate them from your records (Users).
• Server settings and my.cnf.
• Data changed after the dump (use the binary logs: Point-in-Time Recovery).
• Files that the application keeps outside the database.

Common restore errors

mysql --force continues after errors. Use it only to see all the errors of a bad file, never to ignore them.

Speeding up large restores

• Run it on a server without the application load, then switch over.
• The dump already disables unique and foreign key checks and uses multi-row inserts.
• Consider (temporarily, on the restore server only) innodb_flush_log_at_trx_commit = 2 and sync_binlog = 0, and a large innodb_buffer_pool_size. Return them to safe values afterwards.
• Turn off the binary log for the restore session if you do not need to replicate it: SET sql_log_bin = 0 at the top of the script (needs the right privilege).
• For databases of hundreds of GB, use a physical restore (Physical Backups) or MySQL Shell’s parallel util.loadDump().