CODEWITHSUNDEEP
mysql
samJL
samJL

Reputation: 737

Can mysqldump do one query per line?

Is it possible for mysqldump to dump one query per line?

For example, it currently dumps a CREATE TABLE expressions like so:

--
-- Table structure for table `post`
--

CREATE TABLE IF NOT EXISTS `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(160) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `url` varchar(600) NOT NULL,
  `domain` varchar(90) NOT NULL,
  `author` int(11) NOT NULL,
  `description` text,
  `category` int(11) DEFAULT NULL,
  `score` int(11) NOT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `created` datetime NOT NULL,
  `comment_count` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(12) NOT NULL,
  `password` varchar(45) NOT NULL,
  `email` varchar(150) NOT NULL,
  `about` varchar(300) DEFAULT NULL,
  `last_visit` datetime DEFAULT NULL,
  `ip` varchar(255) DEFAULT NULL,
  `created` datetime NOT NULL,
  `perm_mod` int(11) DEFAULT NULL,
  `perm_admin` int(11) DEFAULT NULL,
  `post_count` int(11) NOT NULL DEFAULT '0',
  `comment_count` int(11) NOT NULL DEFAULT '0',
  `vote_count` int(11) NOT NULL DEFAULT '0',
  `voted_count` int(11) NOT NULL DEFAULT '0',
  `forgot_key` varchar(150) DEFAULT NULL,
  `cookie_key` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

Instead, I want it to dump like this:

--
-- Table structure for table `post`
--

CREATE TABLE IF NOT EXISTS `post` (`id` int(11) NOT NULL AUTO_INCREMENT,`title` varchar(160) NOT NULL,`slug` varchar(255) NOT NULL,`url` varchar(600) NOT NULL,`domain` varchar(90) NOT NULL,`author` int(11) NOT NULL,`description` text,`category` int(11) DEFAULT NULL,`score` int(11) NOT NULL,`ip` varchar(255) DEFAULT NULL,`created` datetime NOT NULL,`comment_count` int(11) NOT NULL DEFAULT '0',PRIMARY KEY (`id`),UNIQUE KEY `uc_slug` (`slug`) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE IF NOT EXISTS `users` (`id` int(11) NOT NULL AUTO_INCREMENT,`login` varchar(12) NOT NULL,`password` varchar(45) NOT NULL,`email` varchar(150) NOT NULL,`about` varchar(300) DEFAULT NULL,`last_visit` datetime DEFAULT NULL,`ip` varchar(255) DEFAULT NULL,`created` datetime NOT NULL,`perm_mod` int(11) DEFAULT NULL,`perm_admin` int(11) DEFAULT NULL,`post_count` int(11) NOT NULL DEFAULT '0',`comment_count` int(11) NOT NULL DEFAULT '0',`vote_count` int(11) NOT NULL DEFAULT '0',`voted_count` int(11) NOT NULL DEFAULT '0',`forgot_key` varchar(150) DEFAULT NULL,`cookie_key` varchar(40) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

I have been going through the args for mysqldump, and can't find anything that would do this

Upvotes: 1

Views: 1158

Answers (3)

Aea
Aea

Reputation: 976

If you truly want individual queries the most pragmatic solution in my book would be to use the built-in functionality of mysqldump. The --skip-extended-insert flag is what you want, it is not clear in the documentation but it gives you the dump format you want.

Upvotes: 1

Azh
Azh

Reputation: 21

I had similar task - to format mysqldump output file to be mysql init_file option compatible. Here is the solution:

sed ':a;N;$!ba;s/\n/THISISUNIQUESTRING/g' | sed -e 's/;THISISUNIQUESTRING/;\n/g' | sed -e 's/THISISUNIQUESTRING//g'

This replaces all newline chars with a weird string that is not present in the file. Next sed replaces the combination of this string and semicolon with semicolon and newline char. This is needed because table data can contain string values that include ";", so straighforward removal of all newline chars and adding them after semicolons is not universal. After that remaining weird strings are just removed from the text.

Btw, I was running mysqldump with --comments=false parameter, as comments are not allowed for "init_file" thing as well.

Upvotes: 1

Francisco Alvarado
Francisco Alvarado

Reputation: 2813

Indeed, there is no command-line switch to achieve this. Instead I recommend you to do the dump normally and then use a good text editor like Notepad++ to open dump file.

First select block to convert to single line, then select menu option of the folowing image.

Unwrap Text

This is the result (repeat for every block to convert to single line):

enter image description here

Upvotes: 2

Related Questions