In this post, we’ve seen how to split a large MySQL dump by database.

I’ve been asked a script to automate the process. Here you are.

Note: On FreeBSD, replace AWK=awk by AWK=gawk and install lang/gawk port, so we can use GNU awk.

#!/usr/bin/env bash

AWK=awk
REGEX_NAME="Current Database: \`(.*)\`"

# Checks argument and prints usage if needed
if [ "$#" -lt "1" ]
then
    echo "Usage: $0 <dump.sql>"
    exit 1
fi

# Splits dump into temporary files
$AWK '/Current Database\: .*/{g++} { print $0 > g".tmpsql" }' $1

# Renames files or appends to existing one (to handle views)
for f in *.tmpsql
do
    DATABASE_LINE=`head -n1 $f`
    [[ $DATABASE_LINE =~ $REGEX_NAME ]]
    TARGET_FILE="${BASH_REMATCH[1]}.sql"
    if [ -f $TARGET_FILE ]; then
        cat $f >> $TARGET_FILE
        rm $f
    else
        mv $f ${BASH_REMATCH[1]}.sql
    fi
done