Split MySQL dump SQL file into one file per table or extract a single table

mysql_splitdump.sh

#!/bin/bash

####
# Split MySQL dump SQL file into one file per table
# based on http://blog.tty.nl/2011/12/28/splitting-a-database-dump
####

if [ $# -lt 1 ] ; then
  echo "USAGE $0 DUMP_FILE [TABLE]"
  exit
fi

if [ $# -ge 2 ] ; then
  csplit -s -ftable $1 "/-- Table structure for table/" "%-- Table structure for table \`$2\`%" "/-- Table structure for table/" "%40103 SET TIME_ZONE=@OLD_TIME_ZONE%1"
else
  csplit -s -ftable $1 "/-- Table structure for table/" {*}
fi

[ $? -eq 0 ] || exit

mv table00 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
  mv $FILE foot
else
  csplit -b '%d' -s -f$FILE $FILE "/40103 SET TIME_ZONE=@OLD_TIME_ZONE/" {*}
  mv ${FILE}1 foot
fi

for FILE in `ls -1 table*`; do
  NAME=`head -n1 $FILE | cut -d$'\x60' -f2`
  cat head $FILE foot > "$NAME.sql"
done

rm head foot table*

thanks a lot.

works just great.

although it throws some errors:

mv: Aufruf von stat f�r �table991� nicht m�glich: Datei oder Verzeichnis nicht gefunden
cat: foot: Datei oder Verzeichnis nicht gefunden
cat: foot: Datei oder Verzeichnis nicht gefunden
cat: foot: Datei oder Verzeichnis nicht gefunden

---

@rubo77 Check csplit -b '%d' -s -f$FILE $FILE "/SEARCH_STRING/-1" {*}. SEARCH_STRING is the first of the SQL commands to restore the global variables in the bottom of the dump file.

You can also just skip that part, as it isn't that imporant.

#!/bin/bash

####
# Split MySQL dump SQL file into one file per table
# based on http://blog.tty.nl/2011/12/28/splitting-a-database-dump
####

if [ $# -ne 1 ] ; then
  echo "USAGE $0 DUMP_FILE"
fi

csplit -s -ftable $1 "/-- Table structure for table/" {*}
mv table00 head

for FILE in `ls -1 table*`; do
      NAME=`head -n1 $FILE | cut -d$'\x60' -f2`
      cat head $FILE > "$NAME.sql"
done

rm head table*

Interesting.
Trying to solve the large dump sql file issue, I was going for a different approach:

. $tables = SHOW TABLES FROM mydb ;
. foreach( $tables as $table) { mysqldump mydb $table > mydb_$table.sql }

From your experience, do you think this may do the job?

Thanks for sharing!

I enhanced it a bit: http://stackoverflow.com/a/9949414/1069083

I've created MySQLDumpSplitter.java which, unlike bash scripts, works on Windows. It's
available here https://github.com/Verace/MySQLDumpSplitter.

I get this error:

csplit: *}: bad repetition count

seems to have a bug. Extracting a single table (by providing the second arg) the result file misses "40103 SET TIME_ZONE=@OLD_TIME_ZONE"

Try out http://sqlsplit.com

Thanks! This worked perfectly.

csplit: *}: bad repetition count

csplit -s -ftable $1 "$START" {**}

->

csplit -s -ftable $1 "$START" {9999999}

The foot part doesn't work if you have over 100 tables. The ls has to be sorted numerically with -v so you get the correct last file (otherwise it sees table99 or table999 as the last file):

FILE=`ls -1v table* | tail -n 1`

This script doesn't work if there is more than 100 tables. If there is more than 100 tables, tail -n 1 doesn't get the last files (as ls doesn't sort files by number : table101 is before table29). To fix it, add -n4 in csplit to get the number formatted to 4 number and change line #21 to mv table0000 head.

Hello There, I took a different approach and have written a node module (installable as a cli command) that splits a 16G dump file containing more than 150 tables in less than 2 minutes (on my machine).

If this is of your interest please take a look at mysqldumpsplit

Take note that the script only works if the dump file does NOT contain more than one database.

All generated .sql-files will USE the first database in the dump file. Furthermore, if several databases contain tables with the same name, the files will overwrite each other.

Hi there,
the SQLDumpSplitter3 should do the trick here, too. :)
https://philiplb.de/sqldumpsplitter3/

SQLDumpSplitter3 caused issues for me with the dump and it also cannot split by table. The ls -1v table* | tail -n 1 fix from @tmirks was an awesome patch. 👍

Hello There, I took a different approach and have written a node module (installable as a cli command) that splits a 16G dump file containing more than 150 tables in less than 2 minutes (on my machine).

If this is of your interest please take a look at mysqldumpsplit

I'll vouch for this. I just used it on a 2 GiB, 470 table dumpfile. 10.3 seconds, no errors, works fine.

Thank you vekexasia!

Any tool that could export all tables from a specific database inside of a multi database dump would be absolutely amazing. If anyone knows of such a tool please share ;)

This script doesn't work if there is more than 100 tables. If there is more than 100 tables, tail -n 1 doesn't get the last files (as ls doesn't sort files by number : table101 is before table29). To fix it, add -n4 in csplit to get the number formatted to 4 number and change line #21 to mv table0000 head.

which csplit command? there are several :(

there is a simple fix to those saying issue with more than a 100 files: -

1. add -n5 in both csplit commands to allow upto 5 digits zero-spaced in the name.
2. change mv table00 head to mv table00000 head.

Now this will handle upto 100000 tables starting from 00000 to 99999.
Upgrade as required!

Bonus: add rm $FILE after cat head $FILE foot to remove the old file as well so as to save some space if there are too many files. My scenario had over 2500 tables with the base sql file at 130GB!

Bonus 2 hours later: for the really lazy ones, heres a link to my version of this script including output and timing
#https://gist.github.com/m-faraz/4e32cbae283e3a9454118cd1d281f8cf