Extracting one table from mysqldump or phpMyAdmin backup

Using mysqldump is a quick way to do backups, although usually limited to only smaller databases - perhaps up to a few gigabytes large. It is still a fairly popular solution as majority of databases aren't even that big. Also phpMyAdmin provides a variant of mysqldump format through its Export function. Everything works well for plain dump and restore, but in certain situations it is necessary to restore only a single table. With all data being in a single text file, it may not be a trivial task. Here is how I deal with the problem.

Rather than editing out parts of the file that I don't need, I wrote simple one-liners that do that for me. These are not perfect as for example they can't deal with multiple tables by the same name existing in several different schemas, but that so far never turned out to be a problem. I encourage you to suggest improved ways of doing this, just let me know in the post comments! :-)

But now, sed to the rescue!

# Extract table structure for table 'customer'
sed -ne '1,/^-- (Current|Database)/{/^(--.*)*?$/d;p};/^-- Table.*`customer`/,/^-- (Dumping|Table|Temporary)/{/^(--.*)*?$/d;/^DROP/d;p}' dump.sql

# Extract data dump for table 'customer'
sed -ne '1,/^-- (Current|Database)/{/^(--.*)*?$/d;p};/^-- Dumping.*`customer`/,/^-- (Dumping|Table|Temporary)/{/^(--.*)*?$/d;p}' dump.sql

These should work with both mysqldump text dumps as well as phpMyAdmin exports, although in the second case only when the UI language is set to English.

How to use it? Simply redirect the output into a new .sql file:

sed -ne '1,/^-- (Current|Database)/{/^(--.*)*?$/d;p};/^-- Table.*`customer`/,/^-- (Dumping|Table|Temporary)/{/^(--.*)*?$/d;/^DROP/d;p}' dump.sql > dump-customers-create.sql

You should also check out the related post Improved script for extracting table from MySQL text dump

Take care of your MySQL performance.

MySQL audits available from only €99 per server. Learn More
blog comments powered by Disqus