Manipulating giant MySQL dump files

Question

What's the easiest way to get the data for a single table, delete a single table or break up the whole dump file into files each containing individual tables? I usually end up doing a lot of vi regex munging, but I bet there are easier ways to do these things with awk/perl, etc. The first page of Google results brings back a bunch of non-working perl scripts.

Answer 1

Save yourself a lot of hassle and use "mysqldump -T" if you can

From the man page:

--tab=path, -T path

Produce tab-separated data files. For each dumped table, mysqldump creates a tbl_name.sql file that contains the CREATE TABLE statement that creates the table, and a tbl_name.txt file that contains its data. The option value is the directory in which to write the files.

By default, the .txt data files are formatted using tab characters between column values and a newline at the end of each line. The format can be specified explicitly using the --fields-xxx and --lines-terminated-by options.

Note
This option should be used only when mysqldump is run on the same machine as the mysqld server. You must have the FILE privilege, and the server must have permission to write files in the directory that you specify.

Answer 2

When I need to pull a single table from an sql dump, I use a combination of grep, head and tail.

Eg:

grep -n "CREATE TABLE" dump.sql

This then gives you the line numbers for each one, so if your table is on line 200 and the one after is on line 269, I do:

head -n 268 dump.sql > tophalf.sql
tail -n 69 tophalf.sql > yourtable.sql

I would imagine you could extend upon those principles to knock up a script that would split the whole thing down into one file per table.

Anyone want a go doing it here?

Another bit that might help start a bash loop going:

grep -n "CREATE TABLE " dump.sql  | tr ':`(' '  ' | awk '{print $1, $4}'

That gives you a nice list of line numbers and table names like:

200 FooTable
269 BarTable

Answer 3

I use sed quite a bit when the dump is corrupt.

Usually need to delete all the #------------------------------------------ lines for some reason.

Answer 4

Maatkit seems quite appropriate for this with mk-parallel-dump and mk-parallel-restore.

Answer 5

yes... http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/ This shell script will be grabbing the tables you want and pass it to splitted.sql

It’s capable to understand regular expressions as I’ve added sed -r option.

Also MyDumpSplitter can split the dump in to individual table dumps