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

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 2

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

From the documentation:

--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.