209

with the following statement:

mysqldump --complete-insert --lock-all-tables --no-create-db 
--no-create-info --extended-insert --password=XXX -u XXX 
--dump-date yyy > yyy_dataOnly.sql

I get INSERT statements like the following:

INSERT INTO `table` VALUES (1,'something'),(2,'anything'),(3,'everything');

What I need in my case is something like this:

INSERT INTO `table` VALUES (1,'something');
INSERT INTO `table` VALUES (2,'anything');
INSERT INTO `table` VALUES (3,'everything');

Is there a way to tell "mysqldump" to create a new INSERT statement for each row? Thanks for your help!

rexford
  • 5,192
  • 5
  • 27
  • 40

4 Answers4

329

Use:

mysqldump --extended-insert=FALSE

Be aware that multiple inserts will be slower than one big insert.

Community
  • 1
  • 1
driis
  • 161,458
  • 45
  • 265
  • 341
  • 81
    --skip-extended-insert appears the be the correct syntax for me (using mysqldump version 10.13) – Isaac Betesh May 22 '13 at 20:17
  • 22
    Slower, yes, but a lot of text editors have trouble with hugely long lines of text and if the tables have a huge amount of data, that is what will happen. – James John McGuire 'Jahmic' Sep 17 '13 at 12:16
  • 2
    `mysqldump --opt --skip-extended-insert` or `mysqldump --opt --extended-insert=FALSE` work – Yzmir Ramirez Nov 03 '16 at 00:27
  • 1
    OMG! I migrated to a new version which caused this issue and a lot of my automated scripts to fail. I thought I was screwed but this seems to be like an easy fix. Thanks for sharing! – Varun Verma Sep 12 '17 at 14:43
  • I have a question, option "--complete-insert" and "--skip-extended-insert" can be worked together? – tquang Nov 10 '17 at 07:27
  • @tquang yes they can, I just tested it. – Rolf Jun 14 '18 at 07:52
  • 20
    This is nice because now I can do a `git diff` on two different database dumps and have a clear picture of what was changed. – Rolf Jun 14 '18 at 07:54
  • i have a situation where mariadb is not reading its own dump files, and the simple workaround is using your solution. if i become more motivated, i may try to isolate the problem. – edwardsmarkf Nov 11 '21 at 15:27
  • This makes **git version management of .sql dumps** somehow feasable. When changing just one word in one blogpost then my Wordpress `wp_posts.sql` table dump with `--extended-insert=TRUE` has one line holding ~99.9% of the file contents, which is the diff. With `--extended-insert=FALSE` the dump has separate insert statements per each line. Then each changed blogpost means only 1 changed row (~0.1% of file). Still 50 paragraphs of that post as one line. But with wrapping + word/character-diff coloring it gets comprehensible. Thx! **W/o that option git scm of SQL dumps is useless.** – porg Dec 19 '22 at 23:35
21

In newer versions change was made to the flags:

from the documentation:

--extended-insert, -e

Write INSERT statements using multiple-row syntax that includes several VALUES lists. This results in a smaller dump file and speeds up inserts when the file is reloaded.

--opt

This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

--skip-extended-insert

Turn off extended-insert

Flimm
  • 136,138
  • 45
  • 251
  • 267
israel berko
  • 556
  • 1
  • 5
  • 18
1

For MariaDB you would use

mysqldump -u root -p'secret' --skip-extended-insert dbnamehere > y.sql

MaraiDB mysqldump Docs

DevWL
  • 17,345
  • 6
  • 90
  • 86
-1

for people who don't want to --skip-extended-insert
// cause (each record has one insert sentence) performance loss when later execute/import sql.

but want keep the sql readability (avoid records in one long line) this may be work
// didn't find offical solution, just use sed to format the sql

NOTE: It's not safe if ),( in raw data.
TODO: handle ),( in row data

use sed


mysqldump ... | sed 's/),(/),\n  (/g'

// no newline before first item.


mysqldump ... | sed -e 's/),(/),\n  (/g' -e 's/VALUES (/VALUES\n  (/g'

// add newline before first item.

outputs

0. original:

INSERT INTO `role_permission` VALUES (1328,106,1),(1329,171,1),(1330,144,1),(1331,147,1),(1333,157,1),(1334,88,1);

1. become:

INSERT INTO `role_permission` VALUES (1328,106,1),
  (1329,171,1),
  (1330,144,1),
  (1331,147,1),
  (1333,157,1),
  (1334,88,1);

INSERT INTO `role_permission` VALUES
  (1328,106,1),
  (1329,171,1),
  (1330,144,1),
  (1331,147,1),
  (1333,157,1),
  (1334,88,1);


http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html

https://stackoverflow.com/q/15750535#answer-19961480
Solution came from here, I didn't do fully test

yurenchen
  • 1,897
  • 19
  • 17
  • The problem with this is that it will break for strings that happen to contain `),(` – Flimm Dec 24 '22 at 14:42
  • Useful for diff'ing SQL dumps, but not good for dumps where you need to trust the data. (If you are just diffing, you might as well just remove each `),(` and replace with a single linebreak. – mwfearnley Jan 10 '23 at 10:45
  • yes, you were right, it's not safe for `),(`, I wonder is there a easy way to escape or protect it – yurenchen Jan 15 '23 at 16:34
  • This does not solve _anything_, as far as "performance loss" is involved. – Luuk Jan 15 '23 at 16:47
  • @Luuk this solution avoid split into one value each insert (which generate too many inset sentence that slow down when later execute import them) by use multi-lines values split by sed. but not process special string safely – yurenchen Jan 15 '23 at 16:54
  • @Luuk the point is keep sql readability (be close to --skip-extended-insert dose), but avoid generate too many insert sentence for each row value (not safe workaround by sed) – yurenchen Jan 15 '23 at 17:11