MyDumpSplitter-Extract tables from Mysql dump-shell script

By
Kedar
– February 11, 2010

A lot of articles have been written on splitting mysqldump and grab required tables.
Long back when Shlomi had suggested a “sed” way, I actually shell scripted it, and now publishing.

This shell script will be grabbing the tables you want and pass it to tablename.sql.
It’s capable to understand regular expressions as I’ve added sed -r option.
Also MyDumpSplitter can split the dump in to individual table dumps.

You may directly download the script at the end of the page.

$>sh MyDumpSplitter.sh
Usage: sh MyDumpSplitter.sh DUMP-FILE-NAME — Extract all tables as a separate file from dump.
sh MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME — Extract single table from dump.
sh MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP  – Extract tables from dump for specified regular expression.

How to use MyDumpSplitter to split tables from MySQL Dump: (Watch Video Below)

1. To create individual tables’ sql from sinlge dump:
sh MyDumpSplitter.sh database_fulldump.sql

Above command will create individual dumps for each table from main fulldump sql file.
They will be stored as table-name.sql in same directory.

2. To extract single table dump from sinlge dump:
sh MyDumpSplitter.sh database_fulldump.sql tablename

Above command will create dump for specified table from main fulldump sql file and store it to tablename.sql.

3. To extract tables matching matching certain regular expression from sinlge dump:
sh MyDumpSplitter.sh database_fulldump.sql -S tablename

Above command will extract all tables which matches regular expression “tablename(.*)” from main fulldump sql file and store each tabke matching to regular expression in to tablename.sql.

Watch how to use mysqldumpsplitter:

MyDumpSplitter.sh is a very basic script and can be added more features and modified further.
For eg. it can be parameterised to get individual table-names or single-dump of all extracted tables and others.

Suggestions are welcome. I’ll update it whenever time permits.

The shell script is as follows, you may also download it from end of the page:

#!/bin/sh
#SPLIT DUMP FILE INTO INDIVIDUAL TABLE DUMPS
# Text color variables
txtund=$(tput sgr 0 1) # Underline
txtbld=$(tput bold) # Bold
txtred=$(tput setaf 1) # Red
txtgrn=$(tput setaf 2) # Green
txtylw=$(tput setaf 3) # Yellow
txtblu=$(tput setaf 4) # Blue
txtpur=$(tput setaf 5) # Purple
txtcyn=$(tput setaf 6) # Cyan
txtwht=$(tput setaf 7) # White
txtrst=$(tput sgr0) # Text reset

TARGET_DIR=”.”
DUMP_FILE=$1
TABLE_COUNT=0

if [ $# = 0 ]; then
echo “${txtbld}${txtred}Usage: sh MyDumpSplitter.sh DUMP-FILE-NAME${txtrst} — Extract all tables as a separate file from dump.”
echo “${txtbld}${txtred} sh MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME ${txtrst} — Extract single table from dump.”
echo “${txtbld}${txtred} sh MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP ${txtrst} — Extract tables from dump for specified regular expression.”
exit;
elif [ $# = 1 ]; then
#Loop for each tablename found in provided dumpfile
for tablename in $(grep “Table structure for table ” $1 | awk -F”\`” {‘print $2′})
do
#Extract table specific dump to tablename.sql
sed -n “/^– Table structure for table \`$tablename\`/,/^– Table structure for table/p” $1 > $TARGET_DIR/$tablename.sql
TABLE_COUNT=$((TABLE_COUNT+1))
done;
elif [ $# = 2 ]; then
for tablename in $(grep -E “Table structure for table \`$2\`” $1| awk -F”\`” {‘print $2′})
do
echo “Extracting $tablename…”
#Extract table specific dump to tablename.sql
sed -n “/^– Table structure for table \`$tablename\`/,/^– Table structure for table/p” $1 > $TARGET_DIR/$tablename.sql
TABLE_COUNT=$((TABLE_COUNT+1))
done;
elif [ $# = 3 ]; then

if [ $2 = "-S" ]; then
for tablename in $(grep -E “Table structure for table \`$3″ $1| awk -F”\`” {‘print $2′})
do
echo “Extracting $tablename…”
#Extract table specific dump to tablename.sql
sed -n “/^– Table structure for table \`$tablename\`/,/^– Table structure for table/p” $1 > $TARGET_DIR/$tablename.sql
TABLE_COUNT=$((TABLE_COUNT+1))
done;
else
echo “${txtbld}${txtred} Please provide proper parameters. ${txtrst}”;
fi
fi

#Summary
echo “${txtbld}$TABLE_COUNT Table extracted from $DUMP_FILE at $TARGET_DIR${txtrst}”

Download MyDumpSplitter.sh

Update: The script had some issues with regular expression export etc, which  Kevin pointed me. I have updated the script and tested for the same.

Special thanks to mwvisa1.

You might also like::

  1. How to echo colored text in linux shell script
  2. Simple Shell Script to Monitoring Disk Space on a Linux Machine
  3. Search / find through all databases, tables, columns in MySQL
  4. Perl Script for Analyze – Optimize – Repair Mysql Databases
  5. Linux Shell Commands – quick how-tos
Tags: , , , ,