| A lot of articles have been written on splitting | | | | further.e.g., it can be parameterised to get individual |
| mysqldump and grabbing the required tables. | | | | table-names or a single-dump of all extracted tables |
| A long while back, when Shlomi had suggested a | | | | amongst other improvements. |
| “sed” way, I actually shell scripted this activity, | | | | Below is the dump splitter script: |
| which I am now publishing in this article. | | | | #!/bin/sh |
| This shell script will grab the tables you want and pass | | | | #SPLIT DUMP FILE INTO INDIVIDUAL TABLE DUMPS |
| it to tablename.sql. | | | | # Text color variablestxtund=$(tput sgr 0 1) # |
| It’s capable of using regular expressions as I’ve | | | | Underlinetxtbld=$(tput bold) # Boldtxtred=$(tput setaf 1) |
| added the sed -r option. | | | | # Redtxtgrn=$(tput setaf 2) # Greentxtylw=$(tput |
| Also MyDumpSplitter can split the dump file in to | | | | setaf 3) # Yellowtxtblu=$(tput setaf 4) # |
| individual table dumps. | | | | Bluetxtpur=$(tput setaf 5) # Purpletxtcyn=$(tput setaf |
| Check the output on Linux shell: | | | | 6) # Cyantxtwht=$(tput setaf 7) # Whitetxtrst=$(tput |
| $>sh MyDumpSplitter.sh | | | | sgr0) # Text reset |
| Usage:sh MyDumpSplitter.sh DUMP-FILE-NAME -- | | | | TARGET_DIR="." |
| Extract all tables as a separate file from dump.sh | | | | DUMP_FILE=$1 |
| MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME | | | | TABLE_COUNT=0if [ $# = 0 ]; thenecho |
| -- Extract single table from dump.sh MyDumpSplitter.sh | | | | "${txtbld}${txtred}Usage: sh MyDumpSplitter.sh |
| DUMP-FILE-NAME "TABLE-NAME-REGEXP" -- | | | | DUMP-FILE-NAME${txtrst} -- Extract all tables as a |
| Extract tables from dump for specified regular | | | | separate file from dump."echo "${txtbld}${txtred} sh |
| expression. | | | | MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME |
| How to use MyDumpSplitter to split tables from | | | | ${txtrst} -- Extract single table from dump."echo |
| MySQL Dump: | | | | "${txtbld}${txtred} sh MyDumpSplitter.sh |
| 1. To create individual tables sql from a single dump:sh | | | | DUMP-FILE-NAME \"TABLE-NAME-REGEXP\" |
| MyDumpSplitter.sh database_fulldump.sql | | | | ${txtrst} -- Extract tables from dump for specified |
| The above command will create individual dumps for | | | | regular expression."exit;elif [ $# = 1 ]; then |
| each table from a main fulldump sql file. | | | | #Loop for each tablename found in provided |
| They will be stored as tablename.sql in the same | | | | dumpfilefor tablename in $(grep "Table structure for |
| directory. | | | | table " $1 | awk -F"\`" {'print $2'})do |
| 2. To extract a single table dump from a single | | | | #Extract table specific dump to tablename.sqlsed -n " |
| dump:sh MyDumpSplitter.sh database_fulldump.sql | | | | ^-- Table structure for table \`$tablename\`/,/^-- Table |
| tablename | | | | structure for table/p" $1 > $TARGET_DIR |
| The above command will create a dump for the | | | | $tablename.sql |
| specified table from a main fulldump sql file and store it | | | | TABLE_COUNT=$((TABLE_COUNT+1))done;elif [ $# |
| to tablename.sql. | | | | = 2 ]; thenfor tablename in $(grep -E "Table structure |
| 3. To extract tables matching certain regular | | | | for table \`$2" $1| awk -F"\`" {'print $2'})doecho |
| expression criteria from a single dump:sh | | | | "Extracting $tablename..." |
| MyDumpSplitter.sh database_fulldump.sql | | | | #Extract table specific dump to tablename.sqlsed -n " |
| "tablename(.*)" | | | | ^-- Table structure for table \`$tablename\`/,/^-- Table |
| The above command will extract all tables which | | | | structure for table/p" $1 > $TARGET_DIR |
| match the regular expression “tablename(.*)” | | | | $tablename.sql |
| from a main fulldump sql file and store each table | | | | TABLE_COUNT=$((TABLE_COUNT+1))done;fi |
| matching to regular expression it to tablename.sql. | | | | #Summaryecho "${txtbld}$TABLE_COUNT Table |
| An illustration of all three in action is below to help | | | | extracted from $DUMP_FILE at |
| convey what might be missed by the words above | | | | $TARGET_DIR${txtrst}" |
| and give you a feel for output of proper execution: | | | | Suggestions are welcome and I’ll update it |
| Note: MyDumpSplitter.sh is a very basic script and can | | | | whenever time permits. |
| be extended with more features and modified | | | | |