Using mysqldump

mysqldump is the standard MySQL command-line tool for creating logical backups. It generates a SQL file containing DROP TABLE, CREATE TABLE, and INSERT statements that recreate the database from scratch.

Common mysqldump Options

--single-transaction: For InnoDB tables, takes a consistent snapshot without locking tables. Essential for production backups.
--routines: Includes stored procedures and functions
--triggers: Includes triggers (included by default)
--no-data: Exports only the schema, no row data
--where: Exports only rows matching a condition
--tables: Exports only specified tables

InnoDB vs MyISAM

For InnoDB tables, always use --single-transaction. For MyISAM tables, use --lock-tables instead (the default). Mixing them requires care to avoid inconsistencies.

Example

-- Production-safe InnoDB backup (no table locks)
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --databases myapp_db \
  > myapp_full_2025-05-02.sql

-- Schema only (no data)
mysqldump -u root -p --no-data myapp_db > schema_only.sql

-- Single table backup
mysqldump -u root -p myapp_db orders > orders_backup.sql

SQL

Full Editor

Pro Tip

Always use --single-transaction with InnoDB to get a consistent backup without blocking writes.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned