Backup & Restore

Introduction

No matter how carefully you design a database, things can still go wrong — someone runs a DELETE without a WHERE clause, a hard drive fails, or a bug corrupts data. A recent backup is the difference between a minor inconvenience and a catastrophic, unrecoverable loss.

This lesson covers mysqldump, MySQL's standard command-line backup tool, how to restore a database from a backup file, and how to automate the whole process so it happens without you needing to remember.

Why Backups Matter

A backup is simply a saved copy of your data, taken at a specific point in time, that you can use to restore your database if something goes wrong. Without one, any data-destroying mistake or failure is permanent.

Creating a Backup with mysqldump

mysqldump is a command-line tool, bundled with MySQL, that exports an entire database — its table structures and all its data — into a single SQL file full of CREATE TABLE and INSERT statements.

It is run from your terminal (not inside a MySQL SQL session), like this:

mysqldump -u root -p shop > shop_backup.sql

Here, -u root specifies the username, -p prompts you for the password, shop is the name of the database to back up, and > shop_backup.sql redirects the output into a file instead of printing it to the screen.

-- MySQL dump 10.13
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` int NOT NULL,
  `name` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `products` VALUES (1,'Wireless Mouse',19.99),(2,'USB-C Cable',9.50);

The resulting file is plain, readable SQL — you can open it in a text editor, and it can be replayed against any MySQL server to fully recreate the database from scratch.

Restoring from a Backup

Restoring simply means running the backup file's SQL statements back against a MySQL server. You do this from the terminal using the mysql command-line client, with the backup file redirected in as input.

mysql -u root -p shop < shop_backup.sql

Note the direction of the redirect: an export uses > (output goes into the file), while a restore uses < (the file feeds into the command as input). The target database (shop, here) must already exist before you restore into it.

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS shop;"
mysql -u root -p shop < shop_backup.sql

Automating Backups

Manually remembering to run mysqldump every day is unreliable — automation is what actually keeps you safe. Two common approaches are using an OS-level scheduler, or a MySQL Event you learned about in an earlier lesson.

On Linux or macOS, a cron job can run mysqldump automatically every night:

# Run every day at 2:00 AM
0 2 * * * mysqldump -u root -pYOURPASSWORD shop > /backups/shop_$(date +\%F).sql

Alternatively, since mysqldump itself runs outside MySQL, an internal MySQL Event cannot invoke it directly — but Events are still useful for scheduling in-database maintenance (like archiving old rows) that complements an OS-level backup schedule.

Common Mistakes

Best Practices

Frequently Asked Questions

Key Takeaways

Summary

Backups are not optional for any database that matters — they are the safety net that turns a disaster into a minor inconvenience.

In this lesson, you learned why backups matter, how to create one with mysqldump, how to restore from it, and how to automate the process. Next, you will learn how to manage the users who are allowed to connect to your MySQL server in the first place.