19 May 2013

Restoring single table from full MySQL dump file

That would be quick post. To restore single table from full MySQL backup it is required to filter all other sql commands which is not belonging to certain table.

 # sed -n -e '/CREATE TABLE.*Tablename/,/UNLOCK TABLES/p' full_backup.sql > table_recovery.sql

This command read content of  'full_backup.sql' and search for line containing "CREATE TABLE" followed with specified table name (just replace it with desired name). From this line starts sending to standard output. Then search for line "UNLOCK TABLES" - on this line stops sending contents. At the end of this command is redirection to file ">"

It is better to create intermediate file to be sure, that the filtering working as you expected, because this is based on sql commands. After "CREATE TABLE" statements comes pair of statements "LOCK TABLES" and "UNLOCK TABLES" which are used in 99% if not in 100%. You don't need to create intermediate file and you are sure you can pipe it directly to MySQL:

 # sed -n -e '/CREATE TABLE.*Tablename/,/UNLOCK TABLES/p' full_backup.sql | mysql -u user -p dbname

No comments:

Post a Comment