UPDATE: I've modified the script to cope with extracting the last database or table, and also improved the argument error checking. Extracting only the database is now allowed - the table argument is now optional.
I had to do a restore of a few tables from a fairly large ( 3.4GB ) mysqldump full backup recently. I did it the hard way, and it took way too long: I manually sliced the relevant parts out of the file with vim. The next day, I decided to prevent that from happening again, and wrote this shell script to do the job. It uses my slice.py script from a couple of posts back, to suck the relevant section out of the original file. As a by-product, it also sucks out the entire database that contains the table you are after.
It doesn't automatically clean up after itself - leaving temporary files, some of them potentially quite large - in your working directory. It does this so that it doesn't have to do the heavy-lifting more than once if you have more than one table that you want to extract from a given backup.
You use it like this:
Make a working directory and change to it:
mkdir -p restores/20091010
cd restores/20091010
/var/lib/mysql/backups/mysqldump-full-20091008.sql.gz
Unzip the backup into this directory (unless your backup isn't compressed, in shich case move, link or copy it):
gunzip --to-stdout /var/lib/mysql/backups/mysqldump-full-20091008.sql.gz > mysqldump-full-20091008.sql
mysqldump-extract.sh mysqldump-full-20091008.sql thatslinuxDB articles
It then creates an index of the databases in the backup file, and then slices out the relevant section into a file named after the database concatenated to the backup-file name, so in this case, it's thatslinuxDB-mysqldump-full-20091008.sql.
Next, it creates an index of all the tables in this database backup, and then slices out the relevant section of the database backup into a file named similarly to the above: articles-thatslinuxDB-mysqldump-full-20091008.sql.
Load the file up into vim and check it looks ok (make sure the header and footer are OK and that the data is in there):
vim articles-thatslinuxDB-mysqldump-full-20091008.sql
When you are happy with it, you can then proceed to restore from this file with:
mysql -uroot -p <articles-thatslinuxDB-mysqldump-full-20091008.sql
WARNING: BUG - If you want to extract the LAST database or the LAST table, you will have to fake the beginning of another database or table yourself, or modify the script to cope with this situation. It was a "quick'n'dirty (TM)" solution that worked for me and I put in the minimum effort to get it working for now. I may at a later point put up a revised version with this issue fixed. No promises!
So without further ado, here's the script:
#!/bin/bash
# © 2009 Andy D'Arcy Jewell v. 0.9
# Licensed under the GPL v3
# Check parameters
backupfile=$1
database=$2
table=$3
error="F"
if [[ "$backupfile" == "" ]]; then
echo "Backup file not specified. Cannot continue."
error="T"
fi
if [[ "$database" == "" ]]; then
echo "Database not specified. Cannot continue."
error="T"
fi
if [[ $error == "T" ]]; then
echo
echo "Usage: $0 backupfile database [table]"
echo
echo "backupfile: A backup file created by mysqldump."
echo "database: The database you wish to extract."
echo "table: (Optional) The table you wish to extract from within"
echo " the specified database. "
echo
exit 1
fi
if [[ "$table" == "" ]]; then
echo "Table not specified, so table extraction will not be performed."
fi
if [[ -e $backupfile ]]; then
if [[ -e $backupfile.db.index ]]; then
echo "Using pre-existing $backupfile.index."
else
# index the backup file
echo "Indexing backup file $backupfile.index..."
grep -b "^USE" $backupfile > $backupfile.db.index
# Add dummy entry to end of index to allow extraction of last database
backupfile_size=`stat -c%s $backupfile`
echo "$backupfile_size USE end-of-backup" >> $backupfile.db.index
fi
else
echo "Backup file \"$backupfile\" does not exist. Cannot continue."
exit 1
fi
echo
echo "Calculating $database database extent..."
database_start=$( grep -A1 \`$database\` $backupfile.db.index|cut -d":" -f1| head -n1 )
database_end=$( grep -A1 \`$database\` $backupfile.db.index|cut -d":" -f1| tail -n1 )
echo " Start: $database_start End: $database_end"
if [[ $database_start == "" || $database_end == "" ]]; then
echo "Cannot find a database named $database in $backupfile. Cannot continue."
exit 1
fi
if [[ -e $database-$backupfile ]]; then
echo "Using existing $database-$backupfile..."
else
echo "Extracting $database from $backupfile to $database-$backupfile..."
python /root/slice.py $backupfile $database_start $database_end > $database-$backupfile
fi
if [[ "$table" == "" ]]; then
echo "Skipping table extraction because table not specified."
else
if [[ -e $database-$backupfile.table.index ]]; then
echo "Using existing $database-$backupfile.table.index..."
else
echo "Creating table index $database-$backupfile.table.index..."
grep -b -- "^-- Table structure for table" $database-$backupfile > $database-$backupfile.table.index
# Add dummy entry to end of index to allow extraction of last table
dbfile_size=`stat -c%s $database-$backupfile`
echo "$dbfile_size -- end-of-table-structures" >> $database-$backupfile.table.index
fi
echo "Calculating $table table extent..."
table_start=$( grep -iA1 \`$table\` $database-$backupfile.table.index|cut -d":" -f1| head -n1 )
table_end=$( grep -iA1 \`$table\` $database-$backupfile.table.index|cut -d":" -f1| tail -n1 )
echo " Start: $table_start End: $table_end"
echo
if [[ $table_start == "" || $table_end == "" ]]; then
echo "Cannot find a table named $database in $database-$backupfile. Cannot continue."
exit 1
fi
echo "Extracting $table definition from $database-$backupfile to $table-$database-$backupfile..."
python /root/slice.py $database-$backupfile $table_start $table_end > $table-$database-$backupfile
fi
