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 resetTARGET_DIR=”.”
DUMP_FILE=$1
TABLE_COUNT=0if [ $# = 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 ]; thenif [ $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.
Related posts:
- How to echo colored text in linux shell script Try following command on you bash: bash $] tput setaf...
- MySQL Stored procedure to Generate-Extract Insert Statement A lot of places I saw people asking for ways...
- Simple Shell Script to Monitoring Disk Space on a Linux Machine This article explain a way to get a mail as...
[...] Today found this great post, here is a quick excerpt : A lot of articles have been written on splitting mysqldump and grab required tables. Long back when Shalomi had suggested a sed way, I actually shell. Read the rest of this great post Here [...]
Hi Kedar,
Good work!
I was also considering writing such a script (python). What I see is still missing is as follows:
- working with streaming (e.g. mysqldump mydb | mydumpsplit.sh)
- Managing those little thinks, such as disabling FK checks per split table, charsets…
- Handling triggers, stored routines.
PS, doing the reverse: loading the distinct tables back into mysql can be easily accomplished with Maatkit’s mk-parallel-restore (which also serves as a general-purpose parallelizing tool last I checked).
[...] link: MyDumpSplitter-Extract tables from Mysql dump-shell script … Share and [...]
[...] Today found this great post, here is a quick excerpt : 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. Read the rest of this great post Here [...]
Thanks! This saved me a bunch of time. I had a 35 GB dump file from which I needed to extract two tables. It took about 28 minutes to process the file. That was faster than loading the whole dumpfile and then dumping just the tables I needed.
One issue I had was that the script didn’t separate tables with the same name, but in different databases. So, I ended up with one .sql file with the drop, create and insert statements for two tables. I was confused for a while, because the resulting table was much too small. Finally, I realized that the smaller second table was replacing the first table when I loaded that .sql file.
Also, when specifying the table name, a regular expression is used that causes any suffix to also match. I had a table named “tasks” and a much larger table named “tasks_archive”, but I wanted only the statements for the tasks table. I assume it took a little extra time to also write out the tasks_archive table statements, but it wasn’t that big of a deal, since I could obviously ignore it.
Hi Robert,
I’m glad to see it’s working as expected.
Now about your regexp, for sure if you want to extract “tasks” table you shouldn’t have used REGEXP mode… you should have directly specified the table name as “tasks_XYZ” will obviously a match to the regexp!
I hope I got what you’ve said?!!
Meanwhile, for extracting Databases from a fulldump following will help:
#Shell Script To Extract Database from Full Dump
for dbz in $(grep “\-\- Current Database” $1 | sed ’s/`//g’ | awk -F “: ” {‘print $2′}|sort|uniq)
do
echo $dbz;
sed -n “/^– Current Database: \`$dbz\`/,/^– Current Database: \`/p” $1 > $dbz.db.sql
done;
For extracting databases from a mysql dump the following php script is much quicker. The shell script parses the file for table names, then passes the whole file to sed once per table. This takes quite a while for a large dump (I’m currently looking at a 30GB dump). The php script gets the tables, splitting the file in one pass. It uses little memory as it only holds the currently examined line in memory. The first argument to the script is the filename of the full dump. Script is quick and dirty, so no error checking or option to just get one db, but both could be added trivially. Hope someone finds this useful.
<?php
$db = null;
$outfp = null;
$infp = fopen($argv[1], 'r');
while($line = fgets($infp)){
if(preg_match('/– Current Database: `([^`]+)`/',$line,$matches)){
if($outfp){
fclose($outfp);
}
$db = $matches[1];
$outfp = fopen($db.'.db.sql','w');
}
if($outfp){
fwrite($outfp, $line);
}
}
[...] see original posting on my blog: kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/ Tags: DumpShell, From, MydumpsplitterExtract, MySQL, Script, [...]
1. Script Updated.
2. Video “How to use” attached!!
p.s.: It’s my first screen cast – I’ll improve soon.
[...] To see original posting on my blog: http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/ [...]
You saved my life ! Thanks dude !!!
I definitely owe you a beer, started scripting one out then did a quick google and found your scripts many thanks!
@guroot: Welcome dude!
@will pink: So where can we have it anyways ? Glad to know it helped as it meant to be
[...] To see original posting on my blog: http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/ [...]
[...] To see original posting on my blog: http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/ [...]
[...] To see original posting on my blog: http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/ [...]
Title…
This is my Excerpt…
[...] see original posting on my blog: http://kedar.nitty-witty.com/blog/mydumpsplitter-extract-tables-from-mysql-dump-shell-script/ Tags: DumpShell · From · MydumpsplitterExtract · Mysql · [...]
nice tips, thanks for share.