Restore a single table from a large MySQL backup

jnewland (Jesse Newland) — Wed, 28 Nov 2007 14:10:03 GMT

Say, for some reason, you need to restore the entire contents of a single table from a HUGE mysqldump generated backup containing several tables. For example:



create table `baz`;



GIGS OF SQL YOU DON'T WANT;



create table `foo`;



A COUPLE THOUSAND LINES YOU DO WANT;



create table `bar`;



MORE SQL YOU DON'T WANT;

With a little dash 'o ruby, you can extract just the part you want:



$ ruby -ne '@found=true if $_ =~ /^CREATE TABLE `foo`/i; next unless @found; exit if $_ =~ /^CREATE TABLE (?!`foo`)/i; puts $_;' giant_sql_dump.sql > foo.sql

$ cat foo.sql

create table `foo`;



A COUPLE THOUSAND LINES YOU DO WANT;

You can then easily restore that entire table:



$ mysql mydatabase -e 'drop table foo'

$ mysql mydatabase < foo.sql

DZone Snippets: backup code

Restore a single table from a large MySQL backup