[BlueOnyx:19295] Re: Extracting MySQL Dump

[bob richards]

Here's the perl script I use to chop databases down.
It will grab all the txt files from the "from_folder"
create a folder under "to_folder" for each file it finds
create a folder under that folder for each db it finds
and then create a txt file of each table it finds in that folder.

Very useful when you only want to restore one table from a multi gig
database dump.
I have it set up on a cron job to run after the db dumps are done for the
night so I can easily grab any table I want.

Bob



#########################################

#!/usr/bin/perl
use File::NCopy qw(copy);

$from_folder = '/home/backup/dbbackups';
$to_folder = '/home/backup/chop';

# change to from dir
        chdir $from_folder;

# get all the files in there
        @f = <*>;

# make dir if not there
        if ( (-e $to_folder) != 1){ mkdir $to_folder,0755}

        foreach $f (@f){

                &log("chopping $f");
# get file name and create dir of it
# assumes dumps end in .txt, need to change this if yours don't
                ($f2) = $f =~ m/(.*)\.txt/;
                $curdir = "$to_folder/$f2";
                if ( (-e $curdir) != 1){ mkdir $curdir,0755}
                chdir $curdir;

# open and read through dump
                open FILE, "$from_folder$f";
                open DATA, ">temp.txt";
                $db="";
                while (my $line = <FILE>) {

                        # db found? if so create new folder

                        if ($line =~ /-- Current Database: `(.*?)`/){
                                $db = $1;
                                $dbdir = "$curdir/$db";
                                if ( (-e $dbdir) != 1){ mkdir $dbdir,0755}
                                chdir $dbdir;
                        }

        # new table found

                        if ($db){
                                if ($line =~ /-- Table structure for table
`(.*?)`/){
                                        $table = $1;
                                        print "    $table\n";

                                        close DATA;
                                        open DATA, ">$table.txt";
                                }
                                print DATA "$line";

                        }



                }
                close FILE;
                &log("done chopping $f");
        }
        close DATA;


sub log{
        ($log)=@_;
        $timex = localtime;
        open(DATA,">>/home/backupxz1/bulog.txt");
        print DATA "$timex\t$log\n";
        close (DATA);
        print "$timex\t$log\n";
}

##########################################




On Thu, Mar 10, 2016 at 10:34 AM, Chris Gebhardt - VIRTBIZ Internet <
cobaltfacts at virtbiz.com> wrote:

> Hi Richard,
>
> On 3/10/2016 10:22 AM, Richard Sidlin wrote:
> > Thanks Chris. I'm sure it was these instructions that I followed and came
> > completely unstuck. It was probably me putting an arrow the wrong way
> > around!
>
> Yes, computers can be quite literal.  And unlike work in Windows or even
> the BlueOnyx GUI, we don't get prompted with "Are you sure that's really
> what you want?"  "Hey, look again fella... are you still sure?"
>
> That said, I've used the process many a time with excellent results.
> It should work out just fine so long as you're following the exact syntax.
>
> Good luck!
> --
> Chris Gebhardt
> VIRTBIZ Internet Services
> Access, Web Hosting, Colocation, Dedicated
> www.virtbiz.com | toll-free (866) 4 VIRTBIZ
> _______________________________________________
> Blueonyx mailing list
> Blueonyx at mail.blueonyx.it
> http://mail.blueonyx.it/mailman/listinfo/blueonyx
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.blueonyx.it/pipermail/blueonyx/attachments/20160310/a1e7d6bd/attachment.html>