<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

MyDumpSplitter-Extract tables from Mysql dump-shell script

Published on
11,902 Points
5,702 Views
2 Endorsements
Last Modified:
Approved
Community Pick
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.

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

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

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(.*)"

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: mysqldumpsplitter-inaction
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}"

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/
2
Comment
Author:theGhost_k8
3 Comments
LVL 61

Expert Comment

by:Kevin Cross
theGhost_k8:

This worked well for me, thanks for taking the time to script this. Voted Yes above!

mwvisa1
0
LVL 22

Author Comment

by:theGhost_k8
Hi mwvisa1,

Thanks very much for your constant guidance on getting this published.
I really appreciate your efforts as I learnt as well.

Thanks again.
0

Expert Comment

by:cheeta100
What its very important to say here is that, this big dump 1.7 Tbytes is because a very big table 1.7 Terabytes, this database in fact only have one big table.

Thanks for the responses.

Edgar T.
0

Featured Post

Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month