Split MySQL dump SQL file into one file per table

mysql_splitdump_v2.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
####
####
# This version modified from https://gist.github.com/jasny/1608062
####

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

echo -n "Starting to split the dump @ "
date

if [ $# -ge 2 ] ; then
  csplit -n5 -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 -n5 -s -ftable $1 "/-- Table structure for table/" {*}
fi

[ $? -eq 0 ] || exit

echo -n "Done with splitting files @ "
date

mv table00000 head

FILE=`ls -1 table* | tail -n 1`
if [ $# -ge 2 ] ; then
  mv $FILE foot
else
  csplit --suppress-matched -b '%d' -s -f$FILE $FILE "/UNLOCK TABLES/" {*}
  rm ${FILE}
  mv ${FILE}0 $FILE
  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"
  rm $FILE
done

echo -n "Done with renaming tables @ "
date
rm head foot table*

So I originally after looking around in a lot of places stumbled upon the gist mentioned in the code. However, there were some things that I required for myself and just wanted to be done a certain way.

Also, upgraded the script to handle 99,998 tables - the first file becomes the head and the last file becomes the foot - by increasing the filename to have 5 digits.

In my test on a 4core 16GB RAM VM on a 130GB sql file with 2680 tables (please dont ask...and please dont laugh either!), the result output was as follows: -

Starting to split the dump @ Fri Feb  2 05:34:18 PM UTC 2024
Done with splitting files @ Fri Feb  2 05:43:11 PM UTC 2024
Done with renaming tables @ Fri Feb  2 05:49:31 PM UTC 2024