mysql_backup.cgi

Chinaxp 论坛源代码

                             # place the name of your database in the
                             # @selected_databases array.

                             # Someone else might want to process all of the
                             # databases, with possible exceptions. If so,
                             # place the databases to skip in the
                             # skip_databases array below.

@skip_databases              = qw[];
                             # Note: skip_databases is only parsed
                             # if process_all_databases is set to 'yes'
                             # Leave it blank if you don't want to use it., i.e:
                             # qw[];

@skip_tables                 = qw[];
                             # skip_tables is always parsed.
                             # Leave it blank if you don't want to use it., i.e:
                             # qw[];

$tar_options                 = '-p';
                             # hardcoded options include 'c, f'
                             # p = retain permissions
                             # v = verbose (can be set below)

if ( $show_file_list_in_email eq 'yes' )
      {
      $tar_options .= ' -v';
      }

# Set $find_commands to 'yes' or 'no'
# depending upon whether you want to have the program
# search for the command line utilities.
# This is a weak attempt at a ./configure concept
# and won't work on WinX, since it depends upon whereis.
# Do we need it, since one can edit the lines below?
# Probably not. :-)

$find_commands = 'yes';
if ( $find_commands eq 'yes' )
      {
      ($name, $mysqlshow_cmd, $rest) = split / /, `whereis mysqlshow`, 3;
      ($name, $mysqldump_cmd, $rest) = split / /, `whereis mysqldump`, 3;
      ($name, $nice_cmd,      $rest) = split / /, `whereis nice`,      3;
      ($name, $tar_cmd,       $rest) = split / /, `whereis tar`,       3;
      ($name, $gzip_cmd,      $rest) = split / /, `whereis gzip`,      3;
      }
else
      {
      $mysqlshow_cmd         = '/usr/bin/mysqlshow';
      $mysqldump_cmd         = '/usr/bin/mysqldump';
      $nice_cmd              = '/bin/nice';
      $tar_cmd               = '/bin/tar';
      $gzip_cmd              = '/bin/gzip';
      }

# backup file prefix

$file_prefix                 = 'bak.mysql';
                             # the file prefix is also used to match files
                             # for the deletion of old files. It's a real
                             # 'PREFIX', it will be placed at the front of
                             # each file name

$mysql_dump_file_ext       = 'txt';
$mysqldump_params          = '--quick --add-drop-table -c -l';
                             # I used '-v' (for verbose on this, and it
                             # crashed. still checking this...)

$backup_type                 = 'mysqldump';
# $backup_type               = 'outfile';
# $backup_type               = 'normal_select';

                             # set $backup_type to one of these 3 types:

                             # 'mysqldump'
                             # 'outfile'
                             # 'normal_select'

                             # (mysqldump is the best choice, followed by outfile)

                             # ! NOTE: for the 'outfile' method,
                             # you must have MySQL file privilege
                             # status, or the file will not be written
                             # (it will be 0 bytes long)

                             # 'normal_select' uses a normal
                             # select/write process; it's clunky,
                             # but some hosts don't provide access to
                             # mysqldump or 'select into outfile'
                             # (sometimes mysqldump is on a different
                             # server, and sometimes a user doesn't have
                             # 'file_privileges' for mysql.)

                             # NOTE: for LARGE data sets, 'normal_select'
                             # may not work well, because of memory problems

$backup_field_terminate    = '|';
$backup_field_enclosed_by  = '';
$backup_line_terminate     = "\n";
                             # params for 'normal_select' file writing
                             # note that the "\n" must be interpolated
                             # via " double quotes or the qq method

$outfile_params            = qq~ fields terminated by '|' lines terminated by '\n' ~;
                             # params for 'select * from $table_name
                             # into $outfile ($outfile is created in
                             # the backup routine)

$chmod_backup_file         = 'yes'; # set to 'yes' if you want to use it
                             # (you DO NOT want to set the backup file to 600
                             # unless you can ftp in as the user that
                             # the script runs as.)

# end of mysqldump variables
# ...........................

# END OF SETUP VARIABLES

###################################################################################
# YOU NORMALLY WON'T HAVE TO MODIFY ANYTHING BELOW THIS LINE
###################################################################################

chdir ("$mysql_backup_dir");

# now make a tar sub directory for this backup

$tar_dir = $file_prefix . "." . $date_text;
mkdir $tar_dir, 0777;
chdir ("$tar_dir");

print qq~Processing Backups in $mysql_backup_dir/$tar_dir\n~;

$body_text  = "Processing Backups in $mysql_backup_dir/$tar_dir\n\n";
$body_text .= "Databases / Tables:\n";

# get username and password for $mysqlshow_cmd, from .cnf file
#............................................................................

if ( $password_location eq 'cnf' )
        {
        # open file
        unless ( open(CNF_FILE, "$cnf_file" ))
                {
                &error_message(qq~Error!\n
                Can't open File $cnf_file.~);
                }

        # $cnf_group is ignored;
        # so if you have more than one group, it won't work
        # see notes above. We're looking for: $user=$password

        while ( <CNF_FILE> )
                {
                chomp;
                s/#.*//;
                s/^\s+//;
                s/\s+$//;
                next unless length;
                my ($var, $value) = split(/\s*=\s*/, $_, 2);
                $$var = $value;
                }

        close(CNF_FILE);
        }

# test and create the initial database array
# first convert the exception database and table arrays
# to hashes for speed searching
#............................................................................

%skip_databases = ();
%skip_tables    = ();

foreach my $database_name ( @skip_databases )
        {
        $skip_databases{$database_name} = $database_name;
        }

foreach my $table_name ( @skip_tables )
        {
        $skip_tables{$table_name} = $table_name;
        }

# test to see if we should process all databases

if ( $process_all_databases eq 'yes' )
        {
        if ( $password_location eq 'cnf' )
            {
            @databases = `$mysqlshow_cmd --host=$db_host`;
            }
        else
            {
            @databases = `$mysqlshow_cmd --host=$db_host --user=$user --password=$password`;
            }

        chomp ( @databases );
        }
else
        {
        @databases = @selected_databases;
        }

# here's where the backup is actually done
#............................................................................

foreach $db_main ( @databases )
        {
        if ( $db_main =~ /Databases/ )   {next;}
        if ( $db_main !~ /\w+/ )         {next;}
        $db_main =~ s/\|//g;
        $db_main =~ s/\s+//g;

        if ( $process_all_databases eq 'yes' and exists $skip_databases{$db_main} )
                {
                print "\nSkipping: [$db_main\]\n";
                $body_text .= "\nSkipping: [$db_main\]\n";
                next;
                }

        # connect to db
        &connect_to_db($db_main);

        print "\nDatabase: [$db_main\]\n";
        $body_text .= "\nDatabase: [$db_main\]\n";

        # now grab table names for this databases
        # we use 'show tables' to avoid problems with mysqlshow % with older versions
        # ............................................................................

        # switched from this method (see above)
        # @tables = `$mysqlshow_cmd --user=$user --password=$password $db_main`;

        $sth = $dbh->prepare("show tables") or &error_message(qq~Error!\n
                                               Can't execute the query: $DBI::errstr~);
        
        $rv = $sth->execute or &error_message(qq~Error!\n
                               Can't execute the query: $DBI::errstr~);
        
        while ( ( $table_name ) = $sth->fetchrow_array ) 
                {
                if ( exists $skip_tables{$table_name} )
                        {
                        print "\nSkipping: [$table_name\]\n";
                        $body_text .= "\nSkipping: [$table_name\]\n";
                        next;
                        }

                print " " x 10 . "table: [$table_name\]\n";

                if ( $show_file_list_in_email eq 'yes' )
                        {
                        $body_text .= " " x 10 . "table: [$table_name\]\n";
                        }

                # now backup the table

                $backup_text = &do_backup($db_main, $table_name);

                if ( $show_file_list_in_email eq 'yes' )
                        {
                        $body_text .= $backup_text;
                        }
                }

        # disconnect from each database
        &logout;

        }

# now tar and compress
#............................................................................

chdir ("$mysql_backup_dir");

print qq~\nTarring and Gzipping Files:\n~;
$body_text .= "\nTarring and Gzipping Files:\n";

$backup_tar_file = $mysql_backup_dir . "/" . $file_prefix . "." . $date_text . "_.tar";

$body_text .= `$nice_cmd $tar_cmd $tar_options -c -f $backup_tar_file $tar_dir`;
$body_text .= `$nice_cmd $gzip_cmd $backup_tar_file`;

$backup_gzip_file     = $backup_tar_file . ".gz";
$upload_gzip_filename = $file_prefix . "." . $date_text . "_.tar" . ".gz";

if ( $chmod_backup_file eq 'yes' )
        {
        chmod 0600, $backup_gzip_file;
        }

$body_text .= "\nCreated Tar.Gzip File: $backup_gzip_file\n";

# now check option to delete text files
#............................................................................

if ( $delete_text_files eq 'yes' )
        {
        chdir ("$tar_dir");

        my $print_text = '';

        $print_text   .= "\nRemoving Intermediate files\n";
        $print_text   .= "Directory: $mysql_backup_dir/$tar_dir\n";
        $print_text   .= "File Spec: $file_prefix\.$date_text\_*.$mysql_dump_file_ext\n";

        $num_to_delete = `find $mysql_backup_dir -name "$file_prefix\.$date_text\_*.$mysql_dump_file_ext" -print | wc -l`;
        $print_text   .= "\nNumber of Files to be removed: $num_to_delete\n\n";

        # now delete files

        $delete = `find $mysql_backup_dir -name "$file_prefix\.$date_text\_*.$mysql_dump_file_ext" -print | xargs rm`;

        # check if all gone
        $num_deleted = `find $mysql_backup_dir -name "$file_prefix\.$date_text\_*.$mysql_dump_file_ext" -print | wc -l`;

        if ( $num_deleted == 0 )
            {
            $print_text .= "\nFiles Removed.\n";
            }
        else
            {
            $print_text .= "\nProblem Removing Files - $num_deleted files still exist.\n";
            }

        chdir ("$mysql_backup_dir");
        $removed_dir = `rm -r $tar_dir`;

        $print_text .= "\nRemoved temporary tar dir: $tar_dir\n";
        $body_text .= $print_text;
        print $print_text;
        }

# now clean old files from main dir (gzip files)
# includes old tar_dirs

$print_text = "\nCleaning Old Files\n";
$body_text .= $print_text;
print $print_text;

&clean_old_files("$mysql_backup_dir");

# now do ftp, if option is set

if ( $ftp_backup eq 'yes' )
        {
        $print_text = "\nConnecting via FTP to $ftp_host\n";
        $body_text .= $print_text;
        print $print_text;

        # Connect to the server:
        $ftp = Net::FTP->new("$ftp_host",
                         Timeout => "30",
                         Port    => "$ftp_port",
                         Passive => "$ftp_passive",
                         Debug   => "0") or
               &error_message(qq~Error! Net::FTP couldn't connect to $ftp_host : $@\n~);
        
        $print_text = "\nLogging in with FTP.\n";
        $body_text .= $print_text;
        print $print_text;

        # Login with the username and password
        $ftp->login("$ftp_user", "$ftp_password") or
              &error_message(qq~Error! Net::FTP couldn't login to $ftp_host : $!\n~);

        $print_text = "\nSetting FTP transfer to binary.\n";
        $body_text .= $print_text;
        print $print_text;

        # set the type to binary
        $ftp->binary or
              &error_message(qq~Error! Net::FTP couldn't set the type to binary for $ftp_host : $!\n~);

        $print_text = "\nChanging to FTP dir: $ftp_dir\n";
        $body_text .= $print_text;
        print $print_text;

        # Change to the right directory
        $ftp->cwd("$ftp_dir") or
              &error_message(qq~Error! Net::FTP couldn't change to $ftp_dir at $ftp_host : $!\n~);

        $print_text = "\nUploading file: $backup_gzip_file\n";
        $body_text .= $print_text;
        print $print_text;