MySQL Forums
Forum List  »  Utilities

Single Table Dump Extraction From A Combined Dump
Posted by: Jasmeet Singh
Date: September 28, 2017 05:00AM

Post from: Sonika (Sr. DBA)

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.

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.

Options: ReplyQuote

Written By
Single Table Dump Extraction From A Combined Dump
September 28, 2017 05:00AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.