How to Extract Table from MySQL dump file

MySQL is a popular database system that allows you to easily backup & restore databases using MySQLdump utility. But sometimes you may need to restore single table, import single table, extract specific table or multiple tables from your database, but not all tables. In this article, we will learn how to extract table from MySQL dump file.

How to Extract Table from MySQL dump file

Let us say you have a sample database products. Here is the command to take a backup of entire database. Replace <username>, <password>, <database_name> with the MySQL username, password and database name respectively

$ sudo mysqldump -u<username> -p<password> <database_name> > /path/to/backup/file

When you run the above command, mysqldump utility will generate a .sql file that is also known as MySQL dump file. It contains the different SQL queries to recreate the database, its tables, data and constraints.

Here is an example to take backup of our database.

$ mysqldump -utest_user -ptest_password product > /home/ubuntu/product.sql

We will use cplit linux function in order to split our .sql file. Create a blank shell script.

$ vi split_db.sh

Add the following lines to this file.

#!/bin/bash

####
# Split MySQL dump SQL file into one file per table
# based on http://blog.tty.nl/2011/12/28/splitting-a-database-dump
####

if [ $# -lt 1 ] ; then
  echo "USAGE $0 DUMP_FILE [TABLE]"
  exit
fi

if [ $# -ge 2 ] ; then
  csplit -s -ftable $1 "/-- Table structure for table/" "%-- Table structure for table `$2`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1"
else
  csplit -s -ftable $1 "/-- Table structure for table/" {*}
fi

[ $? -eq 0 ] || exit

mv table00 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
  mv $FILE foot
else
  csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*}
  mv ${FILE}1 foot
fi

for FILE in `ls -1 table*`; do
  NAME=`head -n1 $FILE | cut -d$'x60' -f2`
  cat head $FILE foot > "$NAME.sql"
done

rm head foot table*

Save and close the file. In the above code, we split all tables from dump file if no table name has been specified as command line argument. If it has been specified, then we extract only that table and not others.

Make it an executable.

$ sudo chmod +x split_db.sh

Now let us say you have the dump file products.sql that contains multiple tables, then you can run the above script as shown below to create separate .sql files for each table in your database.

./split_db.sh products.sql

When you run the above command you will see separate .sql files for each table in your .sql file (your database).

Now if you want to extract only 1 table from database .sql file, mention the table name after the above command. Here is the command to extract catalog table from products.sql dump file.

$ sh ./split_db.sh products.sql catalog

If you want to extract more than 1 tables, say, catalog, orders, sales, you need to separately run the above command for each table.

$ sh ./split_db.sh products.sql catalog
$ sh ./split_db.sh products.sql orders
$ sh ./split_db.sh products.sql sales

In this article, we have learnt how to extract single table from MySQL dump file.

How to Extract Table from MySQL dump file

How to Extract Table from MySQL dump file

Related posts:

Leave a Reply Cancel reply