Backup and restore

Last reviewed Jun 1, 2026 Content v20260601

Track mode

sql_sandbox

Means

SQL sandbox

Reading

~1 min

Level

advanced

This lesson

This lesson teaches Backup and restore: the SQL patterns, schema habits, and query reasoning you need before advancing in MySQL.

Teams query Backup and restore on every MySQL codebase—skipping it leaves gaps in debugging and data reviews.

You will apply Backup and restore in contexts like: RDS/Aurora, cPanel backups, and on-call restore drills.

Copy MySQL SQL into the mysql client, local MySQL/MariaDB, or DB Fiddle (MySQL dialect)—use DESCRIBE and EXPLAIN where lessons show them. The in-browser lab ships later; mysql client is the practice path now.

When InnoDB, indexes, and EXPLAIN from intermediate lessons make sense in the mysql client.

mysqldump for logical backups; Percona XtraBackup for large physical hot backups on busy primaries.

mysqldump

# Shell:
mysqldump -u root -p --single-transaction practice > practice.sql
# Restore:
mysql -u root -p practice < practice.sql

Practice: Run read-only EXPLAIN on practice tables where possible.

--single-transaction

InnoDB consistent snapshot without locking all tables—use for OLTP dumps.

Test restores

Schedule restore drills—untested backups are not disaster recovery.

Important interview questions and answers

Q: Logical vs physical backup?
A: mysqldump is SQL logical; XtraBackup copies files hot.
Q: WHERE during restore?
A: Restore to new instance before swapping DNS.

Self-check

Why --single-transaction?
Why test restore?

Tip: mysqldump --single-transaction for InnoDB consistency.

Interview prep

single-transaction?: Consistent InnoDB dump without global read lock.
Restore test?: Proves backup integrity and RTO.

Discussion

Past discussion is visible to everyone. Only logged-in users can post comments and replies.

Starter discussion topics

single-transaction?
Restore drill?

No discussion yet. Be the first to ask a question.