Single table restore

How do I restore a single database or table out of an all-inclusive dump?

MySQL Administrator

MySQL Administrator has the option to restore only part of a backup. Under the "Restore" section, load your backup file, then switch to the "Restore Content" tab. Click "Analyze Backup File Content" and select the databases or tables you wish to restore.

Pros: Graphical interface

Cons: Slow for a large file; requires Administrator

Restrict Permissions

Another approach is to create a user that has privileges only to the tables you wish to restore, and then restore the dump as that user, adding the --force option to ignore the permission denied errors you will get.

mysql> GRANT SELECT ON *.* TO 'singleTableRestorer';
mysql> GRANT ALL ON giantDatabase.singleTable TO 'singleTableRestorer'; -- for a single table
mysql> GRANT ALL ON someDatabase.* TO 'singleTableRestorer'; -- for a single database
SHELL> mysql --force -u singleTableRestorer < /path/to/FullBackup.sql

Pros: Easy; fast

Cons: Requires privileges to create users.

Hint: Don't forget afterwards to DROP USER 'singleTableRestorer' for security reasons. (SixThreeOh pointed that out, thanks.)

Load and Dump

Load the whole backup onto some server, then run mysqldump again, specifying only the database or tables you want to keep.

mysql -e "CREATE DATABASE someNewDb"
mysql someNewDB < /path/to/FullBackup.sql
mysqldump someNewDB tablename > /path/to/PartialBackup.sql
mysql originalDB < /path/to/PartialBackup.sql

Pros: Results in a backup you actually want; easy to repeat partial restore

Cons: Requires enough space on a server to reload whole database

Edit the source

Parse the dump file, either with stream editors like sed or awk, or with general languages like PHP, Perl, Python, etc.

Pros: Configurable to the extreme

Cons: Hard; requires knowledge of the dump file format and scripting skills