Restore a single table from a mysqldump database backup to a different table in the same database?

Question

I've got a 3.5gb database dump. Is there a way to restore just a single table from that file to a differently named table in the same database without editing the file, using mysqladmin, or some other commonly available command line application that runs on FreeBSD 6?

Answer 1

You would need to create the table in restore-db and run something like:

grep "^INSERT INTO table" dump-file | mysql -u user -p restore-db

First make sure that your pattern matches correctly.

Answer 2

cat THE_DUMP_FILE.SQL | sed -n "/^-- Table structure for table \`THE_TABLE_NAME\`/,/^-- Table structure for table/p" > THE_OUTPUT_SQL_FILE_NAME

I googled around for a while on this, this solution worked great for me, and seemed to be one of the fastest solutions for a large dump file, I got the idea from: http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump