How to restore a single table from a full database dump fille
9:01 PM
We have to figure out where in your
mysqldump output you want to begin your extraction, and where you want
to end it. The key here is finding something unique at the beginning
and ending of the block that won't be found anywhere else.
A sample mysqldump contains something like the following:
–
-- Table structure for table `test1`
–
. . .
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (. . .
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,0,'2. . .
UNLOCK TABLES;
. . .
–
-- Table structure for table `test2`
–
As you can see, we have a line with the comment "Table structure for table `test1`", then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.
To extract the dump for a single table from an entire database dump, run the following from a command prompt:
'mydumpfile.sql>/tmp/extracted_table.sql">$ awk '/Table structure for table .test1./,/Table structure for table .test2./A sample mysqldump contains something like the following:
–
-- Table structure for table `test1`
–
. . .
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (. . .
LOCK TABLES `test1` WRITE;
INSERT INTO `test1` VALUES (1,0,'2. . .
UNLOCK TABLES;
. . .
–
-- Table structure for table `test2`
–
As you can see, we have a line with the comment "Table structure for table `test1`", then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.
To extract the dump for a single table from an entire database dump, run the following from a command prompt:
Unknown macro: {print}
Unknown macro: {print}
' mydumpfile.sql > /tmp/extracted_table.sql
The awk command is very powerful - the above command searches through
the dump file, and as soon as it matches a line containing the first
search string (denoted by the first set of slashes), it prints that line
and every subsequent line until it encounters a line containing the
second search string (denoted by the second set of slashes).
If the backup file is big and you cannot determine which is the next table to search upto. You can use the following alternative using sed.
sed -n -e '/CREATE TABLE.*mytable/,/CREATE TABLE/p' mysql.dump > mytable.dump
where mytable is the table you want to restore.
0 comments