Extract one table from mysqldump backup file
Starting from the difficulties I experienced, when I wanted to retrieve a table from a large mysqldump backup file… which was around 500mb and above, while the conventional method was too time-consuming even though the table was needed as soon as possible. After doing a search on google, I finally found an effective way, namely by using the grep and sed scripts.
Grep and Sed are actually commands that come from linux, but don’t worry for windows versions there are also.
Actually there are 2 ways that I usually do to extract tables from the mysqldump backup file, namely:
1. Open the backup file with an editor application such as ultraedit and take a specific table script.
2. Restore the backup file to the database and then retrieve the table via the database
However, as the data in the database increases, the first method can cause the editor application to hang if the backup file size is too large, then the second method is less effective and very time-consuming because restoring a backup file can take approximately 1 hour.
Here are the steps to extract a table from the mysqldump backup file:
1. Download additional tools, namely Grep and Sed.
2. Determine the mysql backup file you want to extract, for example you can use northwind.sql which is included in the bundle link below.
3. Type the following command in the command prompt
grep -n “Table structure” “northwind.sql”
Explanation: the above command prints the line number of the search for the word “Table structure”
4. Determine which table you want to extract, in this example the employees table and type the following command:
sed -n “129.168 p” “northwind.sql” > tmp.sql
Explanation: the above command sends output to a file with the name tmp.sql from the northwind.sql file with the provisions of rows from 129 to 168. Notice in the picture, the employees table is on line 129 then the next table employeeterritories is on line 169.
5. Restore the employees table in the tmp.sql file to the mysql database
Here are the tools grep and sed that have been downloaded plus an example of a mysql dump backup file.
After downloading, change the file extension to 7z and extract the file using Winrar or 7zip. good luck
This tutorial has been successfully practiced on computers with the following specifications:
– OS Windows XP
– 1gb ram memory
– mysql 5.0.17
Oh, I didn’t forget to include the original link