O'Reilly logo

Linux Server Hacks by Rob Flickenger

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Hack #83. Restoring a Single Table from a Large MySQL Dump

Here is a method for restoring a single mysql table from a huge mysqldump

Like a good admin, you faithfully dump your mysql tables every night, and save them to the filesystem in compressed form (presumably to be picked up by a backup script later). You probably have something like this running in cron on your database server (or one of its replicated slaves):

for x in `mysql -Bse show databases`; do
mysqldump $x | gzip -9 > /var/spool/mysqldump/$x.`date +%Y%m%d`.gz
done

This will cover you if anything catastrophic happens to your live database. But if your database grows to an appreciable size, doing partial restores can be difficult. On a database with several million rows, your dumps suddenly become massive piles of data that need to be sifted through. How can you easily restore a single table out of a several hundred megabyte compressed dump?

Here's a simple method using Perl. Create a script called extract-table , with this in it:

#!/usr/bin/perl -wn
BEGIN { $table = shift @ARGV }
print if /^create table $table\b/io .. /^create table (?!$table)\b/io;

To extract the User table from the dump of a database called randomdb, try something like this:

# zcat /var/spool/mysqldump/randomdb.20020901.gz | extract-table Users > ~/
Users.dump

Now you can restore your Users table with a simple:

# mysql randomdb -e "drop table Users"
# mysql randomdb < ~/Users.dump

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required