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
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 https://stackoverflow.com/a/1013956 and bryn https://stackoverflow.com/a/15857815 @ StackOverflow’s Can I restore a single table from a full mysql mysqldump file?
Please Support Saotn.org
Each post on Sysadmins of the North takes a significant amount of time to research, write, and edit. Therefore, your donation helps a lot! For example, a donation of $3 U.S. buys me a cup of coffee, and as you know: things jsut work better with coffee. A $10 U.S. donation buys me one month of web hosting (yes, hosting costs money). But seriously, thank you for any amount. Much appreciated!
Please donate to support this site if you found a post interesting or if it helped you solve a problem. Thanks! (Tip: no Paypal account required)