MyDumpSplitter-Extract tables from Mysql dump-shell script

A lot of articles have been written on splitting mysqldump and grabbing the required tables.
A long while back, when Shlomi had suggested a “sed” way, I actually shell scripted this activity, which I am now publishing in this article.

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

I'm maintaining mysqldumpsplitter on GitHub and there has been further development.
Check the output on Linux shell:
 

$>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 "TABLE-NAME-REGEXP" -- Extract tables from dump for specified regular expression.
                      

Select all Open in new window

How to use MyDumpSplitter to split tables from MySQL Dump:

1. To create individual tables sql from a single dump:
 

sh MyDumpSplitter.sh database_fulldump.sql
                      

Select all Open in new window

The above command will create individual dumps for each table from a main fulldump sql file.
They will be stored as tablename.sql in the same directory.

2. To extract a single table dump from a single dump:
 

sh MyDumpSplitter.sh database_fulldump.sql tablename
                      

Select all Open in new window

The above command will create a dump for the specified table from a main fulldump sql file and store it to tablename.sql.

3. To extract tables matching certain regular expression criteria from a single dump:
 

sh MyDumpSplitter.sh database_fulldump.sql "tablename(.*)"
                      

Select all Open in new window

The above command will extract all tables which match the regular expression “tablename(.*)” from a main fulldump sql file and store each table matching to regular expression it to tablename.sql.

An illustration of all three in action is below to help convey what might be missed by the words above and give you a feel for output of proper execution:
Note: MyDumpSplitter.sh is a very basic script and can be extended with more features and modified further.
e.g., it can be parameterised to get individual table-names or a single-dump of all extracted tables amongst other improvements.

Below is the dump splitter script:  
 

#!/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 \"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;
                      fi
                      #Summary
                      echo "${txtbld}$TABLE_COUNT Table extracted from $DUMP_FILE at $TARGET_DIR${txtrst}"
                      

Select all Open in new window

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


To see original posting on my blog: http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/