Database Backups with mysqldump0%
Restoring Databases & Running SQL Dump Scripts

Database Backups with mysqldump

Beginner12 min read•Updated: 2026-09-12
Study Materials

Database Backups with mysqldump: Command-Line Disaster Recovery

A database without an automated, verified backup strategy is a disaster waiting to happen. Hardware crashes, ransomware infections, accidental drop table commands, and failed application migrations can destroy years of business data in seconds. The primary logical backup utility provided with MySQL is mysqldump.

1. What is mysqldump?

mysqldump is a command-line client program that reads table structures and data from your MySQL server and writes them out as a sequential SQL script file (a .sql dump). This file contains:

  1. 1
    DROP TABLE IF EXISTS ...;
  2. 2
    CREATE TABLE ...; (complete with column definitions, constraints, indexes, and storage engine settings).
  3. 3
    Batches of INSERT INTO ... VALUES (...); statements to repopulate every row.
[!IMPORTANT] Command-Line Note: mysqldump is executed directly from your operating system shell (Windows PowerShell, Command Prompt, Linux Bash, macOS Terminal)—NOT inside the mysql> prompt!

2. Core mysqldump Recipes

Bash / Terminal
# 1. Back up a single database to a .sql file:
mysqldump -u root -p ecommerce_db > ecommerce_backup.sql
 
# 2. Back up only specific tables from a database:
mysqldump -u root -p ecommerce_db customers orders > customers_orders.sql
 
# 3. Back up multiple specific databases at once:
mysqldump -u root -p --databases db_one db_two > multi_db_backup.sql
 
# 4. Back up EVERY database on the entire MySQL server:
mysqldump -u root -p --all-databases > full_server_backup.sql
 
# 5. Back up ONLY schema structures (No data rows):
mysqldump -u root -p --no-data ecommerce_db > schema_only.sql
 
# 6. Back up ONLY data rows (No CREATE TABLE statements):
mysqldump -u root -p --no-create-info ecommerce_db > data_only.sql

3. Production Best Practice: The --single-transaction Flag

In busy e-commerce or financial systems, customers are actively placing orders 24/7 while a backup runs. If a dump takes 20 minutes, how do you prevent tables from locking or becoming inconsistent (e.g., an order saved without its corresponding payment)?

Bash / Terminal
# Enterprise Production Backup Command:
mysqldump -u root -p \
--single-transaction \
--quick \
--lock-tables=false \
--routines \
--triggers \
ecommerce_db > enterprise_backup.sql

Why These Flags Matter:

  • --single-transaction: Uses InnoDB's MVCC (Multi-Version Concurrency Control) to create an isolated snapshot of the database at a single moment in time without locking read or write operations!
  • --quick: Streams rows directly to disk page-by-page instead of buffering the entire multi-gigabyte table in RAM.
  • --routines & --triggers: Ensures stored procedures, stored functions, and triggers are included in the backup.

4. Compressing Backups on the Fly

For large databases, saving uncompressed plain text wastes disk space. You can pipe the dump output directly into gzip:

Bash / Terminal
# Linux / macOS / Git Bash:
mysqldump -u root -p ecommerce_db | gzip > ecommerce_backup.sql.gz

5. Best Practices & Common Pitfalls

  • Test Your Backups Regularly: A backup you have never tested restoring is not a backup. Senior DBAs regularly restore random backups to staging servers to verify backup integrity.
  • Off-Site Replication: Never store database backups on the exact same physical server or SSD partition as the active database. Copy dumps to AWS S3, Google Cloud Storage, or a remote NAS.

Multiple Choice Questions

1. Where should the mysqldump command be executed?

A. Inside the interactive mysql> prompt B. In the operating system terminal / command prompt (PowerShell, Bash) C. In an HTML form D. Inside a SELECT query Answer: B Explanation: mysqldump is an independent command-line executable that connects to the MySQL server from the OS shell.

2. Which mysqldump flag creates a consistent snapshot of InnoDB tables without locking tables or blocking user writes?

A. --no-lock B. --single-transaction C. --fast-mode D. --ignore-locks Answer: B Explanation: --single-transaction establishes a snapshot isolation transaction in InnoDB, allowing online backups with zero downtime or write blocking.

3. Which command exports only the schema definitions (CREATE TABLE statements) without exporting any row data?

A. mysqldump --empty-tables B. mysqldump --no-data C. mysqldump --structure-only D. mysqldump --schema Answer: B Explanation: The --no-data (or -d) flag suppresses INSERT statements, dumping exclusively the DDL table creation statements.

4. What output format does mysqldump produce?

A. A binary compiled executable B. A plain-text SQL script containing DDL and INSERT statements C. A proprietary encrypted image D. A spreadsheet in Excel (.xlsx) format Answer: B Explanation: mysqldump produces a standard plain-text .sql file consisting of sequential SQL statements that reconstruct the schema and data.

5. Why are the --routines and --triggers flags important when backing up enterprise databases?

A. They make the dump 50% smaller B. By default, mysqldump omits stored procedures, stored functions, and triggers unless these flags are specified C. They turn on encryption D. They prevent network disconnections Answer: B Explanation: By default, standard mysqldump does not include stored routines or triggers; adding --routines and --triggers ensures all database logic is preserved.

Next Lesson

Restoring Databases & Running SQL Dump Scripts

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Using DISTINCT with Aggregate FunctionsRestoring Databases & Running SQL Dump Scripts

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext