Back up and restore MySQL

A successful backup job does not prove you can recover. The file might be incomplete, or the restore might fail against a real server. You only have a usable backup after you restore and test it. This lesson does both.

mysqldump produces a logical dump: a plain SQL text file containing the CREATE TABLE and INSERT statements that rebuild the database. Create one for the InnoDB database:

mysqldump -u root -p \
  --single-transaction \
  --routines --events --triggers \
  notes_app > notes_app.sql

--single-transaction gives a consistent snapshot for transactional InnoDB tables: the dump reads the database as it existed at one instant, even while the application keeps writing. It does not protect nontransactional tables, and schema changes must not run during the dump. A database dump also does not include MySQL accounts and their grants — after restoring on a fresh server, you recreate accounts with the statements from the privileges module.

Because the dump is plain text, you can sanity-check it immediately:

head -n 20 notes_app.sql

You should see a MySQL dump header followed by SQL. An empty or truncated file means the dump failed, and better to learn that now than during an incident.

Restore and verify

Restore into a clean database, never over the original:

mysql -u root -p -e "CREATE DATABASE notes_restore CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci"
mysql -u root -p notes_restore < notes_app.sql

Restoring over notes_app itself would destroy your only good copy while testing whether the backup works. The separate database makes the drill safe to repeat.

Verify the restored schema and data:

mysql -u root -p notes_restore -e "SHOW TABLES; SELECT COUNT(*) AS notes FROM notes; CHECK TABLE notes, tags, note_tags;"

The table list must match the original, the count must be plausible, and CHECK TABLE must report OK for each table. Then start a non-production application against notes_restore too. A successful dump command alone does not prove recovery works — only a restored database that your application can actually read does.