Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

Thanks in advance,

Ian

share|improve this question

2 Answers 2

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.