How to Verify a MySQL Backup After Restoring It

A successful import message does not establish that the backup contains everything the application needs. The wrong database may have been selected, some tables may be absent, characters may have changed, or required triggers may be missing. Verification means comparing the restored data with a known expected state, not simply reaching the final SQL statement.

Use an environment isolated from production. Prevent the restored application from sending real emails, payments, webhooks, or scheduled jobs. Keep database event execution disabled or explicitly controlled too. Testing a backup should not replay historical business actions.

Is the file intact, and is its content complete?

A SHA-256 hash helps check that a copied file has not changed. On Windows, run this against both copies and compare the values:

Get-FileHash -LiteralPath 'C:\Backups\site.sql' -Algorithm SHA256

Matching hashes are strong evidence of identical file bytes. An incomplete source backup and its perfect copy also match. Keep the backup command, exit status, errors, and object inventory; do not replace logical verification with a file checksum.

Which moment does the reference represent?

Comparing an hour-old backup with counts from a live database that still receives writes can be misleading. Reference measurements must correspond to the backup's consistent snapshot. Plan a controlled write pause or suitable snapshot method. Running several count queries consecutively does not itself give all tables the same reference point.

mysqldump --single-transaction provides a consistent read for suitable transactional tables such as InnoDB, not equivalent protection for MyISAM. Concurrent schema changes also need consideration. Record which engines and objects the chosen backup method covers.

Inventory tables and count actual rows

SHOW FULL TABLES FROM restore_test;
SELECT COUNT(*) AS user_count FROM restore_test.users;
SELECT COUNT(*) AS order_count FROM restore_test.orders;

Adapt these example names to your schema. InnoDB TABLE_ROWS values in information_schema may be estimates, so do not treat a panel's approximate totals as exact counts. COUNT(*) can be expensive on large tables; plan checks in the test environment.

Compare table names, engines, column types, defaults, and indexes too. A table meant to be empty looks the same as a table accidentally exported without data unless an expected-state inventory exists.

Equal counts can conceal different records

This example assumes two fixed copies, source_snapshot and restore_test, on the same test server. Each articles table has a unique id, title, and content. The first query finds missing or changed rows; the second finds extra rows:

SELECT s.id
FROM source_snapshot.articles AS s
LEFT JOIN restore_test.articles AS t ON t.id = s.id
WHERE t.id IS NULL
   OR NOT (CAST(s.title AS BINARY) <=> CAST(t.title AS BINARY))
   OR NOT (CAST(s.content AS BINARY) <=> CAST(t.content AS BINARY));

SELECT t.id
FROM restore_test.articles AS t
LEFT JOIN source_snapshot.articles AS s ON s.id = t.id
WHERE s.id IS NULL;

Null-safe equality distinguishes NULL from an empty string. Binary comparison prevents collation rules from hiding case or accent differences. Add all relevant columns; this example checks only the fields shown. Document differences that a planned conversion intentionally introduces.

For large tables, compare primary-key ranges in batches. A few correct samples do not prove whole-table equality. Do not treat a context-free table checksum as absolute proof across differing engines or versions.

Look for orphan relationships

Re-enabling foreign-key checks after a load does not retroactively validate every row inserted while checks were disabled. Test each relationship explicitly. Here NULL user_id represents an allowed anonymous order and is excluded:

SELECT o.id, o.user_id
FROM restore_test.orders AS o
LEFT JOIN restore_test.users AS u ON u.id = o.user_id
WHERE o.user_id IS NOT NULL AND u.id IS NULL;

Expect no rows. If your rules forbid NULL, count those values separately. If conflicting IDs were remapped, verify that dependent tables used the same mapping.

Check objects and application behavior

Compare views, triggers, procedures, functions, and events against the source inventory. Export options may omit some objects. A missing DEFINER account or changed privileges can cause runtime failures even if an object exists. Do not solve this by granting broad privileges indiscriminately.

In the isolated application, test login, creation, and editing. Check Turkish text and emoji using the byte-level encoding workflow. Do not switch the live connection while unexplained differences remain. Record their cause, affected tables, and the repeated verification result.

References: mysqldump, TABLES metadata, and foreign-key checks.