Single table dump extraction from a combined dump

Start Here

Get in touch with a
TriCore Solutions specialist

Blog | Jun 16, 2015

Single Table Dump Extraction From A Combined Dump

A MySQL DBA has at least once run into a situation where a 'key' small-sized table needs to be restored, and it is a part of a larger combined dump making it a time-consuming task to restore the entire dump in order to get the missing part restored. This blog helps you out of this "dump" situation.


 

Introduction- The Issue

A MySQL DBA has at least once run into a situation where a 'key' small-sized table needs to be restored,MySQL_database and it is a part of a larger combined dump making it a time-consuming task to restore the entire dump in order to get the missing part restored.

Taking an outline scenario where a 7MB  dump of the 'mysql' database having the 'user' table containing the  access credentials needs to be urgently restored, however this data is a part of the combined 10GB dump of all the databases in the server.

This post will provide you with the options on how to go about achieving the above objective in a simple and a timely manner.

The Resolution

Find a way out of this “dump” situation. By using the Linux command line stream editor 'sed' the problem was resolved in no time.

sed -n '/^CREATE TABLE `user`/,/^UNLOCK TABLES/p' alldatabases.sql > user.sql 

The Internal Workings

A mysqldump file when opened in any text editor, is simply a collection of SQL commands to recreate the entire database/databases. The file is heavily commented detailing what is being done. 

The above Linux ‘sed’ command simply extracts all the required lines that state the activities that need to be recreated in the required table.

The highlighted part of the command indicates that anybody using the command will have to modify it as per details that one is trying to retrieve from the dump.

 The steps for extracting ‘user’ table from the dump 'fulldump.sql' would be:

  sed -n '/^CREATE TABLE `user`/,/UNLOCK TABLES/p' fulldump.sql > user.sql

 Now I get the file 'user.sql' containing the sql commands to restore the table with the below command

mysql -u username -p <user.sql

There is another Linux command that can be used:

awk '/CREATE TABLE `user`/,/UNLOCK TABLES/{print}' alldatabases.sql > user.sql

The 'awk' command works just like the 'sed' one and extracts the information in between the highlighted texts, which contains the commands necessary to recreate the table.

Other than Linux editors, mysql also has a provision for selective restoration, although here it is limited to the database level.

mysql -u root -p --one-database test < alldatabases.sql

The switch '--one-database' is given to ignore statements except those for the default database named on the command line. For one to explore this option one needs to have access to the database.

For example if we want to restore the database named 'abc' from a combined dump named 'fulldump.sql' then:

mysql -uusername -ppassword –one-database test < fulldump.sql

NOTE: Before the above command a blank (empty) database named ‘test’ would need to be created.

This (--one-database) command has the limitation that the database containing the table will have to be restored to get to the required table. However this is less costly than restoring the entire dump containing several databases.

Conclusion

As we can see, there can be several ways to resolve the extraction of the smaller dump. However, personally I would recommend the former Linux editor commands as it is a more clear and precise method to resolve the issue.