Splitting up SQL server dump

Question

Is there a way how to split up dump of whole database server (with such tables as 'mysql' or 'information_schema') to single databases?

Looking for better way than insert this whole dump and export needed databases...

Need to recover only part of this dump (only single databases)

pQd · Answer 1 · 2011-10-09 13:48:24Z

up vote 2 down vote accepted

try this:

awk '/USE `/{i++}{print > "file."i".sql"}' yourFullMysqlDump.sql

it'll create file.X.sql for each of the databases

answered Oct 9 '11 at 13:48

pQd
16.1k22044

	nice, thanks. But I think this will skip the step `CREATE DATABASE ...`, isn't it? – Marek Sebera Oct 9 '11 at 13:50
	@MarekSebera - yes, but you can add it easily. – pQd Oct 9 '11 at 16:35
	ok, helped me, marking as correct answer. thanks – Marek Sebera Oct 9 '11 at 17:00

feedback

Kruno · Answer 2 · 2011-10-09 13:30:38Z

up vote 2 down vote

Dump is a text file, so just open in in text editor and extract the part you need.

answered Oct 9 '11 at 13:30

	my dump is unfortunately >100 MB in uncompressed version, and in gzipped version I can't obviously edit it. – Marek Sebera Oct 9 '11 at 13:36
	'text file' isn't quite correct, as it can also contain binary data – pauska Oct 10 '11 at 11:05

feedback

2 Answers