Blog

Improved script for extracting table from MySQL text dump

A few days ago I showed a quick way to extract one table form a mysqldump output. Here is a more complete version which supports extracting either a full schema or a table from the specified schema. If full schema is being extracted, the script also looks for any associated views and routines.

Usage is simple:
garfield ~ # ./extract.sh -f dump.sql -d redmine > redmine.sql
garfield ~ # ./extract.sh -f dump.sql -d redmine -t workflows > redmine-workflows.sql

You can grab the script from Downloads page.

extract.sh

#!/bin/bash

#
# Extracts a schema or a table from a text dump generated by mysqldump or phpMyAdmin
# (c) 2012 Maciej Dobrzanski http://www.dbasquare.com/
# Released under GNU General Public License, version 2
#

function help()
{
  cat <<EOF
  Usage: [-h] -f <dump file> -d <database name> [-t <table>]
EOF
}

while getopts "hf:d:t:" flag ; do
  case $flag in
    h)
      help
      exit 255
      ;;
    f)
      EX_DFILE=${OPTARG}
      ;;
    d)
      EX_SCHEMA=${OPTARG}
      ;;
    t)
      EX_TABLE=${OPTARG}
      ;;
    ?)
      echo "Invalid option: -$OPTARG"
      ;;
  esac
done

: ${EX_DFILE?"Missing argument: -f must be specified; try '$0 -h' for details"}
: ${EX_SCHEMA?"Missing argument: -d must be specified; try '$0 -h' for details"}

if [ ! ${EX_TABLE} ] ; then
  sed -n 
      -e '1,/^-- (Current )?Database/{/^(--.*)*?$/d ; p } ; /^-- (Current )?Database: `'"${EX_SCHEMA}"'`/,/^-- (Current )?Database/ { /^-- (Current )?Database/b ; /^/*![0-9]+.*=@OLD/be ; /^--$/d ; p }' 
      -e ':e /^/*![0-9]+.*=@OLD/,${ p }' "${EX_DFILE}"
else
  sed -n 
      -e '1,/^-- (Current )?Database/{/^(--.*)*?$/d ; p } ; /^-- (Current )?Database: `'"${EX_SCHEMA}"'`/,/^-- (Current )?Database/{ /^-- Table.*`'"${EX_TABLE}"'`/,/^-- (Table|Temporary|(Current )?Database)/ { /^-- (Table|Temporary|(Current )?Database)/b ; /^/*![0-9]+.*=@OLD/be ; /^--$/d ; p } }' 
      -e ':e /^/*![0-9]+.*=@OLD/,${ p }' "${EX_DFILE}"
fi

Take care of your MySQL performance.

MySQL audits available from only €99 per server. Learn More
blog comments powered by Disqus