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.
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.
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.
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.
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.
1[client]
2user=backup_user
3password=strong-secret
4host=127.0.0.1
Then run:
mysqldump --defaults-extra-file=/secure/path/my.cnf app_db \
--no-create-info --single-transaction > app_db_data.sql
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:
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.
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.
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.
