How to restore a single table from a mysql dump of a whole database

The problem

I found that I had made a mistake with an SQL change and wanted to roll a table back to the state it was in before. I didn't make an SQL dump of the table before my change (how clever am I) so there I was with a table in a mess and wishing there was an "undo" button on it.

Having the nightly database dump (this table gets changed only once or twice a week) I figured "fantastic, no problem to restore". Realising that the database dump would restore the whole DB (removing today's orders etc...) I searched around and didn't see anything that provided a simple solution (I'm aware that some software has very full features bud didn't want to install anything that big) or one that was easy to do and I felt safe doing (there were scripts but I didn't want to spend too much time fiddling).

The solution

After some thought I went to the database backup and ran grep on it to see if I could extract the bits I wanted.

First I need to find in the 4G worth of dump where my table starts and finishes.

• -n causes the line no. to be printed
• the "." is "any character" this allows me to get round obscure characters like "`" being used to separate names.
• the <tablename> is the name of the table I'm interested in.

grep -n "Table structure for table ." database.dmp grep -n "ALTER TABLE .<tablename>" database.dmp

Now I know where my table begins and ends in the code. I can subtract the start line no. from the end line no. and have the number of lines it takes:

• -A xx says to print xx (in this case 27) lines after the line has been found. xx is the difference between the two line nos found previously.
• We still search for the same start string.
• ">" redirects the output to a text file
• The name "out-$(date +%F).sql" produces a file with todays date eg: out-2008-09-04.sql

grep -A 27 "DROP TABLE IF EXISTS .<tablename>" database.dmp > out-$(date +%F).sql

Now we (should) have a dump of the table we want. I took it and checked it in a text editor before running the single table dump on the database server (backing up each step of the way and testing on a dev environment first).