AWK – Extract single table from MySQL Backup


Just few notes for my own sake:

The usual way to backup MySQL DB and restore it is as follows:

Backup a Database ‘northwind’:
>>mysqldump -u root -p northwind > northwind.sql

Backup a Single Table customers from Database northwind:
>>mysqldump -u root -p northwind customers > northwind_customers.sql

Restore the table northwind_customers.sql to Database northwind:
>>mysql -u root -p northwind < northwind_customers.sql

In case you ONLY have just the full backup of datbase northwind.sql, and you have to restore a single table, then you can use awk to extract the single table from full backup and then restore the single extracted table to the database. This is how it’s done:

(Concept is: awk ‘/from_line/,/to_line/ {print}’ full_backup.sql > extracted_table.sql)

awk ‘/Table structure for table `emp_norway`/,/Table structure for table `emp_usa`/ {print}’ northwind.sql > northwind_customers.sql


This is how we extract a single table information from the full Database backup.

Now, you can restore the extracted table to the database
mysql -u root -p northwind < northwind_customers.sql

That’s it.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s