MySQL fix. Image via Flickr by Luis M. Gallardo D

Restore single MySQL table from a full mysqldump backup file

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 from a mysqldump backup, using Bash

As outlined in the intro, there are a few required steps you need to perform to restore a single table from a mysqldump backup, because all your tables and data are in one file. Your 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.sqlCode language: Bash (bash)

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.sqlCode language: Bash (bash)

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.

Don’t forget to check, repair and optimize MySQL tables afterwards.

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

Protip: Donate $10, 20 or 30 through Paypal (or see my donate page) and support this site. Thank you <3

Jan Reilink

Hi, my name is Jan. I am not a hacker, coder, developer or guru. I am merely an application manager / systems administrator, doing my daily thing at Embrace - The Human Cloud. In the past I worked for clidn and Vevida. With over 20 years of experience, my specialties include Windows Server, IIS, Linux (CentOS, Debian), security, PHP, websites & optimization. I blog at https://www.saotn.org.

2 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Le Hoang Long
16/10/2019 06:04

Thank you a lot
you save my life
I had to check the multi backup version to gain some piece of information

Nikolai
26/12/2018 18:34

Thanks! It’s realy help me!