Database

How To Extract Tables From MySQL Dump File

by Santosh Prasad

Mysqldump is used to dump MySQL/MariaDB databases and tables. It is an utility which takes a full dump of all databases on regular basis, but some you need to recover a single or multipal tables instead of all tables from dump file. So question is how to restore specific table from full backup file.

In this article we will explain how to extract single or multipal tables backup from full database backup file. We can also learn how to extract all tables backup in individual files per table.

Step #1: Create Dump Split Script

You will need to dump split script to split a full mysqldump database backup file into separate table file backup. Create a file named “splitdump.sh” with below content.

#!/bin/bash

####
# Split MySQL dump SQL file into one file per table
####

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 file and set executable permisson on it.

# chmod +x splitdump.sh

Step #2: Extract All Tables From Dump File

To to this task I have a dump file named ” allmysqldb.sql” and want to split it into small backup per table.

# mkdir /opt/split-table
# cd /opt/split-table/
# sh splitdump.sh mysqldb.sql

Step #3: Extract Single Table From Dump Files

If you want to extract single table from dump file follow the below command. I my example I am going to split single table named ” report_tb1 ”

# cd /opt/split-table/
# sh splitdump.sh mysqldb.sql report_tb1

That’s it.

Thanks:)

Thank you! for visiting LookLinux.

If you find this tutorial helpful please share with your friends to keep it alive. For more helpful topic browse my website www.looklinux.com. To become an author at LookLinux Submit Article. Stay connected to Facebook.

You may also like

About the author

mm

Santosh Prasad

Hi! I'm Santosh and I'm here to post some cool article for you. If you have any query and suggestion please comment in comment section.

View all posts

1 Comment

  • I had some trouble with the last part of the script (was getting an error message, sadly I don’t have it around right now) so I made it work with some changes. If someone else runs into the same, this would be my suggestion:

    for FILE in `ls -1 table*`; do
    NAME=`head -n1 $FILE | awk -F ‘\`’ ‘{print $2}’ | awk -F ‘\`’ ‘{print $1}’`
    mv $FILE “$NAME.sql”
    done

    awk splits the table name delimited by the grave accents (the ` ) and mv will just rename the tableNNNN files into meaningful table names. The last rm is then not needed.

    Reply

Leave a Comment