split a mysqldump sql file into 1 file per table

gistfile1.rb

require 'optparse'

$verbose = false
options = {
  :file => nil,
  :dir => '.'
}

optparse = OptionParser.new do|opts|
  opts.banner = <<-EOF
#{__FILE__}: Split a giant mysqldump.sql file into 1 file per table.

If you need just 1 table out of that massive ted_prod.sql, this script should
help you out. BE SURE to manually review the SQL since this is just a hack and
no error checking is done on the files which get generated.

EOF

  opts.on( '--verbose', "be verbose" ) do |val|
    $verbose = true
  end
  opts.on( '--file VAL', 'sql file to split.' ) do |val|
    options[:file] = val.to_s
  end
  opts.on( '--dir VAL', "dir to write new files in." ) do |val|
    options[:dir] = val.to_s
  end
  opts.on( '-h', '--help', 'Display this screen' ) do
    puts opts
    exit
  end
end


# VALIDATE INPUT

# if we get called with no args at all, show help & exit.
if ARGV.size == 0
  puts optparse
  exit
end

optparse.parse!


puts "Writing new files to #{options[:dir]}" if $verbose

exp = /DROP TABLE IF EXISTS `([^`]+)`;/
new_file = nil

File.open(options[:file], 'r').each_line do |line|
  next if line[0..1] == '--'

  if matches = line.match(exp)
    new_file.close if new_file
    new_filename = File.join(options[:dir], "#{matches[1]}.sql")
    new_file = File.open(new_filename, 'w')
    puts "Creating #{new_filename}" if $verbose
  end

  new_file.write(line) if new_file
end