MySQL dump, output each table row on a new line whilst using --extended-insert

Question

I'm having an issue, where for ease of use, I'd like to be able to format a command line MySQL dump so that each row of a given table is on a new line when using the --extended-insert option.

Usually when using --extended-insert, every row of a given table is outputted on one line, and as far as I am aware there's no way to change this, other than post-processing the dump with perl or such like.

The format I'm looking for is:

-- 
-- Dumping data for table `ww_tbCountry`
-- 

INSERT INTO `ww_tbCountry` (`iCountryId_PK`, `vCountryName`, `vShortName`, `iSortFlag`, `fTax`, `vCountryCode`, `vSageTaxCode`) VALUES (22, 'Albania', 'AL', 1, 0.00, '8', 'T9'),
(33, 'Austria', 'AT', 1, 15.00, '40', 'T9'),
(40, 'Belarus', 'BY', 1, 0.00, '112', 'T9'),
(41, 'Belgium', 'BE', 1, 15.00, '56', 'T9'),
(51, 'Bulgaria', 'BG', 1, 15.00, '100', 'T9'

However, when I dump a database using Phpmyadmin, using --extended-insert, each row is dumped on a new line (as shown by the example above). I've gone through Phpmyadmin and can't find any documentation that would explain this.

Is anyone able to shed any light on this?

Answer 1

From the googling I've done, it appears that this isn't possible through mysqldump itself. You have to pipe the output to something else, like sed:

$ mysqldump --extended-insert ... database | sed 's$),($),\n($g'

That particular sed command will look for the string "),(" and put a newline after the comma.

Answer 2

Using a regex to split lines is not enough, you need a parser that will properly understand quotes and escaping characters.

I just wrote a parser since I couldn’t find one: http://blog.lavoie.sl/2014/06/split-mysqldump-extended-inserts.html

Answer 3

I found a post at Stackoverflow how-to-deal-with-enormous-line-lengths-created-by-mysqldump. Talking about a discussion from mysql forum about "adding a newline" in the source of mysqldump command.

Until they realy make this change there is no nice way to do this. But in the answer they write:

You can get a little closer with --net-buffer-length=4096 That will start a new INSERT more often than the default. Alas, net-buffer-length seems to be rounded up to the next multiple of 4K, so you cannot get the INSERTs very small.

That is not the final solution, but I think it should at least a few more linebreaks, than without this option.