Data is the most valuable asset in any application. Hardware fails, accidental deletes happen, and deployments sometimes go wrong. A reliable backup strategy ensures that no matter what goes wrong, you can recover your data and get back up and running quickly.
MySQL provides a powerful command-line tool called mysqldump that exports a database — or any part of it — into a plain SQL file. That file can then be used to restore the database at any point in the future.
Exports all tables, data, and structure from one database into a single SQL file.
Exports only one specific table — useful for protecting critical data before a risky update.
Exports every database on the entire MySQL server into one combined SQL file.
Exports just the table definitions (CREATE TABLE statements) without any row data.
mysqldump is a command-line utility that comes bundled with MySQL. It connects to the MySQL server, reads the requested database(s), and writes a series of SQL statements — CREATE TABLE, INSERT INTO, and others — into a .sql file. Running that file later recreates the database exactly as it was at the time of the backup.
All mysqldump commands are run from your operating system's terminal (Command Prompt, PowerShell, or Bash) — not from inside the MySQL console.
The most common backup command exports one complete database:
After running this command, MySQL will prompt you for the user's password. Once entered, the file shop_db_backup.sql will be created in your current directory containing the complete database.
shop_db_2026_05_04.sql. This makes it easy to find the right backup when you need it.
If you only need to back up certain tables from a database, list them after the database name:
To create a complete server-wide backup — all databases at once — use the --all-databases flag:
--all-databases backup includes the mysql system database, which contains user accounts and privileges. This makes it a useful disaster recovery backup for the entire server.
Sometimes you only want the table definitions — perhaps to recreate the schema on a new server without copying the data. Use --no-data:
Conversely, if the tables already exist on the target server and you only want to import the data rows, use --no-create-info:
Restoring from a backup is done with the mysql command — the standard MySQL client. You pipe the SQL file into it and MySQL replays all the statements inside:
CREATE DATABASE shop_db;If your backup was created with --all-databases, you do not need to specify a database name — the SQL file already contains CREATE DATABASE and USE statements:
Here is a reference of commonly used options:
| Option | What it does |
|---|---|
--all-databases |
Dumps all databases on the server |
--no-data |
Exports table structure only — no row data |
--no-create-info |
Exports row data only — no CREATE TABLE statements |
--single-transaction |
Takes a consistent snapshot without locking tables (InnoDB only — recommended) |
--routines |
Includes stored procedures and stored functions in the dump |
--triggers |
Includes triggers (included by default) |
--compress |
Compresses data transferred between client and server |
--result-file=file.sql |
Alternative to > — writes output directly to a file |
For InnoDB databases in production, this command produces a clean, consistent backup without locking your tables:
Running backups manually every day is easy to forget. On Linux or macOS you can automate backups using a cron job. On Windows you can use Task Scheduler.
Here is an example Linux cron entry that backs up shop_db every day at 2:00 AM:
~/.my.cnf) with restricted file permissions (chmod 600) and using -p without a password value in the command.
mysqldump exports a database into a plain SQL file that can be used to recreate it.mysqldump from the terminal — not from inside the MySQL console.> to write the backup to a file and < to restore from one.--single-transaction for InnoDB databases to get a consistent backup without locking tables.