Database

How To Extract Tables From MySQL Dump File

Extract-Tables-From-MySQL-Dump-File
mm
Written 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 [email protected]_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 [email protected]_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:)

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.

Leave a Comment