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
[MySQL Health Check]
About Maciej Dobrzanski

A MySQL consultant with the primary focus on systems, databases and application stacks performance and scalability. Expert on open source technologies such as Linux, BSD, Apache, nginx, MySQL, and many more. @linkedin

Comments

  1. Marcos Albe says:

    Nice to find the script you’re needing from the source you can trust :D

    How’s life Mushu? :) Hope you’re enjoying springtime!

    Best,

    Marcos

  2. Many thanks for this script. Had to retrieve just one tiny table from a 2.2Gb dump.

  3. I prefer using csplit above sed. It performs really well even for very big (like 100GB) dumps. Have a look at this gist https://gist.github.com/1608062

Speak Your Mind

*