Introduction

mysqldump normally uses extended inserts, which means one INSERT statement can contain many rows. If you want one INSERT statement per row instead, the option you need is --skip-extended-insert.

The Flag That Changes the Output

A regular dump command might look like this:

mysqldump -u root -p my_database my_table > dump.sql

To force one insert statement per row, add the option:

mysqldump -u root -p \
  --skip-extended-insert \
  my_database my_table > dump.sql

That changes the formatting of the dumped SQL without changing the data itself.

What the SQL Looks Like

With extended inserts, the output is compact:

INSERT INTO users VALUES (1,'Ava'),(2,'Liam'),(3,'Noah');

With --skip-extended-insert, the same rows appear as separate statements:

INSERT INTO users VALUES (1,'Ava');
INSERT INTO users VALUES (2,'Liam');
INSERT INTO users VALUES (3,'Noah');

Both forms restore the same table contents. The difference is readability, file size, and restore speed.

Why You Might Want One Row per Statement

Single-row inserts are useful when you want to:

• inspect rows manually
• compare dump files in a diff tool
• isolate one problematic row during debugging
• feed the dump into tooling that expects simple SQL statements

For debugging, auditing, and version-control workflows, this output can be much easier to work with than dense multi-row inserts.

Combine It with --complete-insert

If readability matters even more than size, add --complete-insert so each statement includes column names.

1mysqldump -u root -p \
2  --skip-extended-insert \
3  --complete-insert \
4  my_database my_table > dump.sql

That produces output closer to:

INSERT INTO users (id, name) VALUES (1, 'Ava');

This is longer, but it is often much easier to inspect or edit.

Understand the Tradeoff

The reason extended inserts are the default is efficiency. A dump with one insert per row is larger and usually slower to import because the server has to parse and execute far more statements.

So the choice depends on purpose:

• use extended inserts for compact, fast backups
• use one-row inserts for readability and troubleshooting

For large production backups, the default format is usually better. For debugging a small export, single-row statements can be worth the cost.

Restoring the Dump

The restore command stays the same:

mysql -u root -p my_database < dump.sql

The server still runs SQL statements from the file. The only difference is how many statements it must process.

When This Format Helps in Real Work

The one-row-per-insert format is especially useful when you are debugging a bad migration or reviewing data changes in version control. A single changed row often becomes a small, isolated change in the dump instead of disappearing inside one very large multi-row statement.

That makes the output easier to inspect by humans, even though it is less efficient for bulk backup and restore performance.

Common Pitfalls

Assuming mysqldump already outputs one statement per row is a common misconception because the default is extended inserts.

Using --skip-extended-insert on very large datasets can make both the dump and the restore noticeably slower.

Combining readability flags without realizing the file-size cost can produce unexpectedly large dump files.

Treating this option as a data-transformation feature is incorrect. It changes SQL formatting, not the logical contents of the backup.

Forgetting column names in debugging workflows can still make the output harder to inspect, which is why --complete-insert is often paired with the main flag.

Summary

• Use --skip-extended-insert to make mysqldump emit one INSERT statement per row.
• The dumped data stays the same; only the SQL formatting changes.
• Single-row inserts are useful for diffing, debugging, and manual inspection.
• Extended inserts remain better for compact and faster large backups.
• Add --complete-insert when you also want readable column names in the dump.