public
Last active

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

  • Download Gist
mysql_splitdump.sh
Shell
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
#!/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'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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.