DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Restore A Single Table From A Large MySQL Backup

11.28.2007
| 11682 views |
  • submit to reddit
        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