[BlueOnyx:19296] Re: Extracting MySQL Dump

[Richard Sidlin]

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";

}

 

 

Thanks Bob. That’s great.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.blueonyx.it/pipermail/blueonyx/attachments/20160310/81de3a83/attachment.html>