Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Split MySQL dump SQL file into one file per table or extract a single table
#!/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*
@rubo77

This comment has been minimized.

Show comment Hide comment
@rubo77

rubo77 Mar 30, 2012

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 commented Mar 30, 2012

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


@jasny

This comment has been minimized.

Show comment Hide comment
@jasny

jasny Mar 31, 2012

@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*
Owner

jasny commented Mar 31, 2012

@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*
@augustofagioli

This comment has been minimized.

Show comment Hide comment
@augustofagioli

augustofagioli Feb 27, 2013

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!

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!

@rubo77

This comment has been minimized.

Show comment Hide comment
@rubo77

rubo77 Mar 20, 2013

@Verace

This comment has been minimized.

Show comment Hide comment
@Verace

Verace Feb 7, 2014

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

Verace commented Feb 7, 2014

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

@missha

This comment has been minimized.

Show comment Hide comment
@missha

missha Mar 15, 2014

I get this error:

csplit: *}: bad repetition count

missha commented Mar 15, 2014

I get this error:

csplit: *}: bad repetition count

@smmortazavi

This comment has been minimized.

Show comment Hide comment
@smmortazavi

smmortazavi Jul 15, 2014

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"

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"

@vbarbarosh

This comment has been minimized.

Show comment Hide comment
@vbarbarosh

vbarbarosh Aug 3, 2014

@jotson

This comment has been minimized.

Show comment Hide comment
@jotson

jotson Oct 1, 2014

Thanks! This worked perfectly.

jotson commented Oct 1, 2014

Thanks! This worked perfectly.

@castiel

This comment has been minimized.

Show comment Hide comment
@castiel

castiel Jan 28, 2015

csplit: *}: bad repetition count

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

->

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

castiel commented Jan 28, 2015

csplit: *}: bad repetition count

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

->

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

@tmirks

This comment has been minimized.

Show comment Hide comment
@tmirks

tmirks Apr 8, 2015

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`

tmirks commented Apr 8, 2015

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`
@maxigit

This comment has been minimized.

Show comment Hide comment
@maxigit

maxigit May 1, 2015

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.

maxigit commented May 1, 2015

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.

@vekexasia

This comment has been minimized.

Show comment Hide comment
@vekexasia

vekexasia Mar 9, 2016

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

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment