How to get a single table out of a massive MySQL .sql database backup file (mysql dump splitter)

Imagine the following situation: Somebody backs up an entire MySQL database – a very large one – with common tools. This will usually result in a tar’ed and gzip’ed file, containing ALL the create/fill statements for ALL tables of the database. All tables in one file. This is also very common when third party clients deliver database backups.

This situation gets annoying when your backup is in the 100+ GB area and you just need ONE table (or at least not all). Re-deploying backups in these dimensions from scratch (for testing for example) is a huge time-killer (running these statements can take days!), and for sure using just that 2 GB table is by far the better option than running the whole script when you really just need that one table.

This was a common task at my former company, and unfortunately all professional tools were not able to read such a file or totally crashed while doing so. The final solution to solve this problem was a tiny self-written bash script I found in on Change is inevitable, a very nice dev blog. This awesome script is extremely easy to use and simply extracts one specific table out of the giant file (and puts it into another file) or grabs a set of tables, you can even use regex here. Fantastic!

Kedar of Change is inevitable has even made a little tutorial on Youtube, check the blog post for the video. Big thanks, man!

As the original blog post shows the script in a unformatted way, here’s the full code again:

#!/bin/sh
# http://kedar.nitty-witty.com
#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}"

Usage

You can use the mysqldumpsplitter like this (assuming you have put the script inside a file called mysqldumpsplitter.sh):

Extract all tables of file XXX to lots of single sql files:

sh mysqldumpsplitter.sh XXX

Extract table ZZZ of file XXX to single file:

sh mysqldumpsplitter.sh XXX ZZZ

Extract table(s) via regex of file XXX:

sh mysqldumpsplitter.sh XXX -S REGEX