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

[MySQL Health Check]
About Maciej Dobrzanski

A MySQL consultant with the primary focus on systems, databases and application stacks performance and scalability. Expert on open source technologies such as Linux, BSD, Apache, nginx, MySQL, and many more. @linkedin

Comments

  1. Hi Maciej
    I worte on this some time ago; here’s my solution.
    The dicussion that ran today on planet.mysql.com was tageting the opposite issue: of dropping a single database from the list.
    It’s easyly done with sed/awk, but only as aftermath. The debate was on how to avoid exporting the schema in the first place, which is more complex.

  2. Erick Franco says:

    There’s one issue though, you will have problems when there’s auto increment tables and you’re trying to reload the data, and it happens to have a 0 in the auto_increment column. You need to keep the set commands in the beginning and the end as well to bypass this issue. This is a good way to extract it from a file already created, but you can also create the file using mysqldump for just one table using “mysqldump “.

  3. Erick Franco says:

    Comment stripped out my command, it should read “mysqldump db_name table_name”

  4. Piotr Biel says:

    If table name is unique, you can do it with awk:

    awk ‘/DROP TABLE IF EXISTS `customer`/,/ENABLE KEYS/’ dump.sql | mysql test_restore

  5. I find the http://www.mydumper.org to be a great alternative to mysqldump that also solves this problem and gives much more btw.
    But in case I have to deal with single file dump this script works pretty well for me: http://www.tsheets.com/downloads/oss/extract_sql.pl

  6. Also check out the script `mysql_splitdump`, which you can use to extract a single table from a big dump file: https://gist.github.com/jasny/1608062
    It uses csplit instead of sed.

Speak Your Mind

*