sql files containing a single database

Splitting a sql file containing a whole database into per-table files is quite easy:

1. Grep the .sql for any occurence of DROP TABLE.
2. Generate the file name from the table name that is included in the DROP TABLE statement.
3. Echo the output to a file.

Here is a little script that expects a .sql file as input:

#!/bin/bash

file=$1 # the input file
directory="$file-splitted" # the output directory
output="$directory/header" # the first file containing the header
GREP="DROP TABLE" # what we are looking for

mkdir $directory # create the output directory

while read line
do
   # if the current line contains the wanted statement
   if [ $(echo "$line" | grep -c "$GREP") == "1" ]
   then
      # extract the file name
      myfile=$(echo $line | awk '{print $5}' | sed -e 's/`//g' -e 's/;//g')
      # set the new file name
      output="$directory/$myfile"
   fi
       echo "$line" >> $output # write to file
done < $file

Here you can download the script if you don’t want to copy & paste.

[UPDATE] sql files containing multiple databases

Stephan Schier [thanks a lot] extended the script in a way that it’s working with complete MySQL snapshots:

#!/bin/bash

file=$1 # the input file
directory="$file-splitted" # the output directory
output="$directory/__header" # the first file containing the header
GREPTABLE="DROP TABLE" # the string we are looking for to find a new table
GREPDB="Current Database: " # the string we are looking for to find a new database

mkdir $directory # create the output directory
while read line
do
  # if the current line contains the wanted DB
  if [ $(echo "$line" | grep -c "$GREPDB") == "1" ]
    then
    # extract database name
    mydb=$(echo $line | awk '{print $4}' | sed -e 's/`//g')
    # create db directory
    mkdir $directory/$mydb
    # set the new header file name
    output="$directory/$mydb/__header"
  elif [ $(echo "$line" | grep -c "$GREPTABLE") == "1" ]
  then
    # extract the file name
    myfile=$(echo $line | awk '{print $5}' | sed -e 's/`//g' -e 's/;//g')
    # set the new file name
    if [ -z "$mydb" ]
    then
      output="$directory/$myfile"
    else
      output="$directory/$mydb/$myfile"
    fi
  fi
  echo "$line" >> $output # write to file
done < $file

Here you can download the updated script if you don’t want to copy & paste.