Practice Now
mysqldump
data export
MySQL
database backup
command line

Dump only the data with mysqldump without any table information

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Databases

Introduction

A data-only dump is useful when schema is already managed elsewhere and you only need table contents. Typical cases include loading seed data, moving production rows into a staging copy, or refreshing analytics snapshots. mysqldump supports this directly with options that suppress CREATE TABLE statements and keep output focused on INSERT data.

Data-Only Dumps with mysqldump

The core option is --no-create-info. With that flag, output contains row inserts but no table definitions.

bash
mysqldump -u backup_user -p app_db --no-create-info > app_db_data.sql

What this does:

  • Connects to app_db as backup_user.
  • Prompts for a password securely instead of putting it on the command line.
  • Writes only data statements into app_db_data.sql.

If you want to avoid locking behavior and preserve transactional consistency for InnoDB tables, combine with --single-transaction.

bash
1mysqldump -u backup_user -p app_db \
2  --no-create-info \
3  --single-transaction \
4  --quick \
5  > app_db_data.sql

--quick streams rows rather than buffering large result sets in client memory, which helps for big tables.

Targeting Tables and Rows

You can dump only selected tables by listing them after the database name.

bash
mysqldump -u backup_user -p app_db orders invoices customers \
  --no-create-info --single-transaction > subset_data.sql

You can also filter rows with --where for one table at a time. This is helpful for incremental exports.

bash
1mysqldump -u backup_user -p app_db events \
2  --no-create-info \
3  --where="created_at >= '2026-01-01'" \
4  > events_2026.sql

Important behavior:

  • --where applies to a single table dump command.
  • If you need filters for multiple tables, run separate dump commands.
  • Quoting matters, especially for dates and string literals.

For repeatable operations, place credentials in a MySQL option file rather than shell history.

ini
1[client]
2user=backup_user
3password=strong-secret
4host=127.0.0.1

Then run:

bash
mysqldump --defaults-extra-file=/secure/path/my.cnf app_db \
  --no-create-info --single-transaction > app_db_data.sql

Consistent Snapshots and Performance

Data-only dumps are still backups, so consistency rules matter. In InnoDB-heavy databases, --single-transaction starts one consistent read view. That usually avoids long blocking writes.

For mixed engines:

  • InnoDB tables are transaction-safe with --single-transaction.
  • MyISAM tables are not transaction-safe and may require table locking for a coherent snapshot.

If you need deterministic order for diffing files, consider --skip-extended-insert so each row is written as a separate insert statement. This produces larger files but easier review.

bash
1mysqldump -u backup_user -p app_db \
2  --no-create-info \
3  --single-transaction \
4  --skip-extended-insert \
5  > app_db_data_one_row_per_insert.sql

Compression is usually worth it for transfer and storage.

bash
mysqldump -u backup_user -p app_db --no-create-info --single-transaction \
| gzip > app_db_data.sql.gz ``` ## Restore Workflow and Validation A data-only file assumes destination tables already exist with compatible columns and constraints. Basic restore: ```bash mysql -u restore_user -p app_db < app_db_data.sql ``` Restore from compressed dump: ```bash gunzip -c app_db_data.sql.gz | mysql -u restore_user -p app_db ``` Before running restore in production, validate on a staging database: ```bash mysql -u restore_user -p -e "CREATE DATABASE IF NOT EXISTS app_db_restore_test" mysql -u restore_user -p app_db_restore_test < schema.sql mysql -u restore_user -p app_db_restore_test < app_db_data.sql mysql -u restore_user -p -e "SELECT COUNT(*) AS rows_in_orders FROM app_db_restore_test.orders" ``` A practical backup strategy is two artifacts: * Schema dump from migration source or `mysqldump --no-data`. * Data-only dump from production. That separation keeps schema lifecycle explicit and avoids accidental table recreation during routine data refreshes. ## Common Pitfalls * Forgetting `--no-create-info` and accidentally including schema. Fix by checking first lines of dump for `CREATE TABLE` before distribution. * Using `--where` across several tables in one command. Fix by running one command per table when row filters differ. * Assuming `--single-transaction` covers non-transactional engines. Fix by identifying storage engines and planning lock windows when needed. * Restoring into mismatched schema versions. Fix by applying the exact schema migration level before import. * Putting passwords directly in shell commands. Fix by using `--defaults-extra-file` with restricted file permissions. ## Summary * Use `--no-create-info` to produce data-only output. * Combine with `--single-transaction` and `--quick` for large InnoDB datasets. * Use table lists and `--where` for focused exports. * Validate restore against a staging schema before production use. * Separate schema and data artifacts for safer, repeatable operational workflows.
Related reading
Course
Beginner
27 lessons
10 hours
System Design Fundamentals

Build a strong foundation in designing scalable, reliable distributed systems.

View the course
Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

System Design practice on Codemia

Work through 120+ system design problems with detailed solutions, from rate limiters to multi-region storage.

Practice system design
Codemia
© 2026 Codemia
Resources
Legal
Social
All Rights Reserved.