Split up a mysqldump file with multiple databases, by database

This Perl script should do the trick.

#!/usr/bin/perl -w## splitmysqldump - split mysqldump file into per-database dump files.use strict;use warnings;my $dbfile;my $dbname = q{};my $header = q{};while (<>) {    # Beginning of a new database section:    # close currently open file and start a new one    if (m/-- Current Database\: \`([-\w]+)\`/) {    if (defined $dbfile && tell $dbfile != -1) {        close $dbfile or die "Could not close file!"    }     $dbname = $1;    open $dbfile, ">>", "$1_dump.sql" or die "Could not create file!";    print $dbfile $header;    print "Writing file $1_dump.sql ...\n";    }    if (defined $dbfile && tell $dbfile != -1) {    print $dbfile $_;    }    # Catch dump file header in the beginning    # to be printed to each separate dump file.      if (! $dbname) { $header .= $_; }}close $dbfile or die "Could not close file!"

Run this for the dump file containing all databases

./splitmysqldump < all_databases.sql

Or, it is possible to save every database into separate file directly...

#!/bin/bashdblist=`mysql -u root -e "show databases" | sed -n '2,$ p'`for db in $dblist; do    mysqldump -u root $db | gzip --best > $db.sql.gzdone

Here is a great blog post I always re-refer to to do this kind of thing with a mysqldump.

http://gtowey.blogspot.com/2009/11/restore-single-table-from-mysqldump.html

You can easily extend it to extract individual db's.