Summary
There are times when you need to restore a table and what
you have is a MySQL dump of the entire database. We want to be able to get a single table restored
without having to restore the entire database.
Here we will cover a way to do that.
Process
1.
Get your Full MySQL Dump in a location that we
can worth with that has the space we need to be able to unzip and work with the
file without filling up the space.
2.
Make sure the mysql dump is not zipped/compressed
if so, make sure it is uncompressed as we need it in the text format.
3.
Determine the table(s) you want to restore that
exist in the mysql dump backup that you have.
Keep in mind that tables can have dependencies for foreign keys and when
restoring tables to prior points in time you may need to actually restore table
dependencies.
4.
First Lets extract the table(s) we want from the
mysql dump to restore from the full dump into files that are just the tables. On a Linux/unix system we can use sed to
accomplish this.
Example:
sed -n -e '/CREATE TABLE.*``/,/CREATE
TABLE/p'
For Example we want the cr_pads and the projects
tables from the mysql dump
sed -n -e '/CREATE TABLE.*`cr_pads`/,/CREATE TABLE/p' MySQLDB_06-03-2020.sql
> c_pads_06_03_2020.dump
sed -n -e '/CREATE TABLE.*`projects`/,/CREATE TABLE/p' MySQLDB_06-03-2020.sql
> projects_06_03_2020.dump
5.
Now that we have the tables we want to restore
out into separate dump files we need to check the file at the end for any drop/create
for the next table in the dump file as our sed command extracted everything
between the create table for the table we want to restore and the next create
table in the file.
tail -100
** Find out how many lines are at the end of the file
that we do not need, look for drop table, create table those for sure we need
to remove.
6.
If the dumpfile contains drop table and create
table of next table we need to remove those from the dump file. We can do this with vi or use head command to
remove the last so many lines
Example:
head --lines=-{number of lines to remove} {dumpfile} >
new_dumpfile
Real Example:
head --lines=-5 projects_06_03_2020.dump > projects_06_03_2020.sql
head --lines=-5 cr_pads_06_03_2020.dump > cr_pads_06_03_2020.sql
7.
Now that we have actual table extracted we are
ready to replace our table or load table into another temporary database to
work with.
Example to a new temporary database:
mysql -u root -p
create databse mytemp
use mytemp
source projects_06_03_2020.sql
source cr_pads_06_03_2020.sql
To replace table in existing database, keep in mind
existing state of table will be destroyed:
mysql -u root -p
use mydatabase
source projects_06_03_2020.sql
source cr_pads_06_03_2020.sql
8.
Now you have the copy of the table(s) back in
the databases as of the last mysql dump.
