Backup & Restore

Earlier lessons on this track were honest about how destructive a mistyped DROP TABLE, DROP DATABASE, or an UPDATE/DELETE without a WHERE clause can be - every one of those is effectively permanent the moment it runs. Beyond human error, ordinary hardware failure is also a real risk - a disk simply can fail. A backup is the only real protection against either kind of disaster, and this lesson covers the standard tool for taking one.

mysqldump - MySQL's Standard Backup Tool

mysqldump is a command-line tool that ships with MySQL and produces a plain-text file containing every CREATE TABLE and INSERT statement needed to recreate a database exactly as it stood at the moment the dump was taken. Backing up the entire streamhub database looks like this, run from a terminal (not inside a MySQL session):

mysqldump -u root -p streamhub > streamhub_backup.sql

-u root specifies the user to connect as, -p prompts for that user's password, and everything mysqldump writes to standard output gets redirected into streamhub_backup.sql. Opening that file afterward, you'd see nothing exotic - just ordinary SQL, starting with the CREATE TABLE statements for users, movies, genres, movie_genres, reviews, and watchlist, followed by an INSERT statement (or a batch of them) to repopulate every row.

Restoring from a Dump File

Restoring reverses the direction: instead of redirecting MySQL's output into a file, you feed the dump file's contents into MySQL as input, and it runs every statement in the file in order, rebuilding the database from scratch.

mysql -u root -p streamhub < streamhub_backup.sql

This assumes the streamhub database itself already exists (empty or not) for MySQL to restore into - if it doesn't yet, create it first, as covered in Creating & Managing Databases, before running this command.

Backing Up Only Specific Tables

A full database backup isn't always necessary. mysqldump also accepts a list of table names after the database name, backing up only those:

mysqldump -u root -p streamhub movies genres > movies_genres_backup.sql

This produces a dump containing only movies and genres - useful when you specifically need a snapshot of the catalog data without the noise of user accounts, reviews, or watchlists.

A Few mysqldump Options Worth Knowing

Two flags come up often enough to be worth knowing by name:

# Structure only - every CREATE TABLE, but no INSERT statements at all
mysqldump -u root -p --no-data streamhub > streamhub_schema_only.sql

# A consistent snapshot of a live database, without locking it for the duration
mysqldump -u root -p --single-transaction streamhub > streamhub_backup.sql

--no-data is handy when you only need the schema itself - for example, setting up an empty copy of StreamHub's structure for a new environment, without any of the actual rows. --single-transaction relies on InnoDB's transaction support (covered in Transactions & ACID) to take a consistent snapshot of the whole database as of one single point in time, without holding a lock across every table while the dump runs - important for a database that's still actively being written to while the backup happens.

Backup Strategy - Beyond Just Knowing the Command

Knowing how to run mysqldump is only half of having a real backup strategy. A few practical habits matter just as much as the command itself:

• Back up on a regular schedule, not just when you remember to - a nightly job (for example, a cron job piping mysqldump's output somewhere safe) means you're never more than a day out of date.
• Store backups somewhere genuinely separate from the live database server. A backup file sitting on the very same disk as the database it's protecting offers no protection at all if that disk is the thing that fails.
• Actually test a restore periodically, on a spare environment, rather than simply trusting the backup file exists. A backup you've never successfully restored from isn't something you can rely on in an actual emergency - restore problems are much better discovered during a calm test than during a real outage.

Point-in-Time Recovery - A Brief Mention

A mysqldump restore only ever brings a database back to exactly the moment the dump was taken - anything written after that point is gone. MySQL also supports a more advanced technique built on its binary log, which records every change made to the database after a given backup. Combining a full backup with its binary log lets you restore to an exact moment in time - for example, the second before an accidental DROP TABLE - rather than only to the last nightly dump. This is a genuinely more advanced setup than a single mysqldump command, so for now, just knowing it exists as an option is enough.