If you need to restore a single table from a full MySQL backup, you may find yourself wondering “how do I do that?”. There are a few steps required, I outline them here for you to restore the contents of just one table back into the database from the mysqldump using Bash.

Restore a MySQL table, using Bash

As outlined in the intro, there are a few required steps you need to perform, because all your tables and data are in one file. That mysqldump backup file might be hundreds MB’s in size. Therefore, you first need to single out the table you want restored.

In my examples I use the WordPress wp_options table.

Talking about wp_options, did you know that you can optimize wp_options by adding an index on the autoload column?

On your bash shell, use sed to separate the data belonging to your table that needs restoring:

sed -n -e '/DROP TABLE.*`wp_options`/,/UNLOCK TABLES/p' mysql_examplecom.sql > examplecom_wp_options.sql

This will copy data in the dump file mysql_examplecom.sql what is between DROP TABLE.*`wp_options` and reads until mysql is done dumping data to your table (UNLOCK) into a new file.

Next you can import the newly created table dump file into MySQL:

mysql -u <user> -p < examplecom_wp_options.sql

And that’s it! This saved me more than once, that’s why I’ve now documented it here.

Other methods to single out the table required do exist. Always be careful and work with additional backups.

References: uloBasEI and bryn @ StackOverflow’s Can I restore a single table from a full mysql mysqldump file?


Want to say thanks?

If I’ve helped you out and you want to thank me, why not buy me a coffee?

If I’ve helped you out and you want to thank me, why not buy me a coffee?

Thank you for your support. ♥


Jan Reilink

My name is Jan. I am not a hacker, coder, developer, programmer or guru. I am merely a system administrator, doing my daily thing at Vevida in the Netherlands. With over 15 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, WordPress, websites & optimization. Want to support me and donate? Use this link: https://paypal.me/jreilink.

2 Comments

Nikolai · 26 December 2018 at 18:34

Thanks! It’s realy help me!

How to restore a deleted Open-Xchange context? - Sysadmins of the North · 7 February 2019 at 23:56

[…] how to restore a single MySQL table from a full mysqldump backup […]

Leave a Reply

Your email address will not be published. Required fields are marked *

20 queries, 0.166 seconds running PHP version 7.3.2