mysqlhotcopy

开启mysql的远程连接的方法 mysql-noinstall-5.1.6-alpha-win32.zip

  foreach my $rdb ( @db_desc ) {
    $rdb->{target} = "$datadir/$rdb->{src}$opt{suffix}";
  }
}

print Dumper( \@db_desc ) if ( $opt{debug} );

# --- bail out if all specified databases are empty ---

die "No tables to hot-copy" unless ( length $hc_locks );

# --- create target directories if we are using 'cp' ---

my @existing = ();

if ($opt{method} =~ /^cp\b/)
{
  foreach my $rdb ( @db_desc ) {
    push @existing, $rdb->{target} if ( -d  $rdb->{target} );
  }

  if ( @existing && !($opt{allowold} || $opt{addtodest}) )
  {
    $dbh->disconnect();
    die "Can't hotcopy to '", join( "','", @existing ), "' because directory\nalready exist and the --allowold or --addtodest options were not given.\n"
  }
}

retire_directory( @existing ) if @existing && !$opt{addtodest};

foreach my $rdb ( @db_desc ) {
    foreach my $td ( '', @{$rdb->{raid_dirs}} ) {

	my $tgt_dirpath = "$rdb->{target}/$td";
	# Remove trailing slashes (needed for Mac OS X)
    	substr($tgt_dirpath, 1) =~ s|/+$||;
	if ( $opt{dryrun} ) {
	    print "mkdir $tgt_dirpath, 0750\n";
	}
	elsif ($opt{method} =~ /^scp\b/) {
	    ## assume it's there?
	    ## ...
	}
	else {
	    mkdir($tgt_dirpath, 0750) or die "Can't create '$tgt_dirpath': $!\n"
		unless -d $tgt_dirpath;
	     if ($^O !~ m/^(NetWare)$/)  
    	    {
	    my @f_info= stat "$datadir/$rdb->{src}";
	    chown $f_info[4], $f_info[5], $tgt_dirpath;
    	    }
	}
    }
}

##############################
# --- PERFORM THE HOT-COPY ---
#
# Note that we try to keep the time between the LOCK and the UNLOCK
# as short as possible, and only start when we know that we should
# be able to complete without error.

# read lock all the tables we'll be copying
# in order to get a consistent snapshot of the database

if ( $opt{checkpoint} || $opt{record_log_pos} ) {
  # convert existing READ lock on checkpoint and/or log_pos table into WRITE lock
  foreach my $table ( grep { defined } ( $opt{checkpoint}, $opt{record_log_pos} ) ) {
    $hc_locks .= ", $table WRITE" 
	unless ( $hc_locks =~ s/$table\s+READ/$table WRITE/ );
  }
}

my $hc_started = time;	# count from time lock is granted

if ( $opt{dryrun} ) {
    print "LOCK TABLES $hc_locks\n";
    print "FLUSH TABLES /*!32323 $hc_tables */\n";
    print "FLUSH LOGS\n" if ( $opt{flushlog} );
    print "RESET MASTER\n" if ( $opt{resetmaster} );
    print "RESET SLAVE\n" if ( $opt{resetslave} );
}
else {
    my $start = time;
    $dbh->do("LOCK TABLES $hc_locks");
    printf "Locked $num_tables tables in %d seconds.\n", time-$start unless $opt{quiet};
    $hc_started = time;	# count from time lock is granted

    # flush tables to make on-disk copy uptodate
    $start = time;
    $dbh->do("FLUSH TABLES /*!32323 $hc_tables */");
    printf "Flushed tables ($hc_tables) in %d seconds.\n", time-$start unless $opt{quiet};
    $dbh->do( "FLUSH LOGS" ) if ( $opt{flushlog} );
    $dbh->do( "RESET MASTER" ) if ( $opt{resetmaster} );
    $dbh->do( "RESET SLAVE" ) if ( $opt{resetslave} );

    if ( $opt{record_log_pos} ) {
	record_log_pos( $dbh, $opt{record_log_pos} );
	$dbh->do("FLUSH TABLES /*!32323 $hc_tables */");
    }
}

my @failed = ();

foreach my $rdb ( @db_desc )
{
  my @files = map { "$datadir/$rdb->{src}/$_" } @{$rdb->{files}};
  next unless @files;
  
  eval { copy_files($opt{method}, \@files, $rdb->{target}, $rdb->{raid_dirs} ); };
  push @failed, "$rdb->{src} -> $rdb->{target} failed: $@"
    if ( $@ );
  
  @files = @{$rdb->{index}};
  if ($rdb->{index})
  {
    copy_index($opt{method}, \@files,
	       "$datadir/$rdb->{src}", $rdb->{target} );
  }
  
  if ( $opt{checkpoint} ) {
    my $msg = ( $@ ) ? "Failed: $@" : "Succeeded";
    
    eval {
      $dbh->do( qq{ insert into $opt{checkpoint} (src, dest, msg) 
		      VALUES ( '$rdb->{src}', '$rdb->{target}', '$msg' )
		    } ); 
    };
    
    if ( $@ ) {
      warn "Failed to update checkpoint table: $@\n";
    }
  }
}

if ( $opt{dryrun} ) {
    print "UNLOCK TABLES\n";
    if ( @existing && !$opt{keepold} ) {
	my @oldies = map { $_ . '_old' } @existing;
	print "rm -rf @oldies\n" 
    }
    $dbh->disconnect();
    exit(0);
}
else {
    $dbh->do("UNLOCK TABLES");
}

my $hc_dur = time - $hc_started;
printf "Unlocked tables.\n" unless $opt{quiet};

#
# --- HOT-COPY COMPLETE ---
###########################

$dbh->disconnect;

if ( @failed ) {
    # hotcopy failed - cleanup
    # delete any @targets 
    # rename _old copy back to original

    my @targets = ();
    foreach my $rdb ( @db_desc ) {
        push @targets, $rdb->{target} if ( -d  $rdb->{target} );
    }
    print "Deleting @targets \n" if $opt{debug};

    print "Deleting @targets \n" if $opt{debug};
    rmtree([@targets]);
    if (@existing) {
	print "Restoring @existing from back-up\n" if $opt{debug};
        foreach my $dir ( @existing ) {
	    rename("${dir}_old", $dir )
	      or warn "Can't rename ${dir}_old to $dir: $!\n";
	}
    }

    die join( "\n", @failed );
}
else {
    # hotcopy worked
    # delete _old unless $opt{keepold}

    if ( @existing && !$opt{keepold} ) {
	my @oldies = map { $_ . '_old' } @existing;
	print "Deleting previous copy in @oldies\n" if $opt{debug};
	rmtree([@oldies]);
    }

    printf "$0 copied %d tables (%d files) in %d second%s (%d seconds overall).\n",
	    $num_tables, $num_files,
	    $hc_dur, ($hc_dur==1)?"":"s", time - $start_time
	unless $opt{quiet};
}

exit 0;


# ---

sub copy_files {
    my ($method, $files, $target, $raid_dirs) = @_;
    my @cmd;
    print "Copying ".@$files." files...\n" unless $opt{quiet};

    if ($^O =~ m/^(NetWare)$/)  # on NetWare call PERL copy (slower)
    {
      foreach my $file ( @$files )
      {
        copy($file, $target."/".basename($file));
      }
    }
    elsif ($method =~ /^s?cp\b/)  # cp or scp with optional flags
    {
	my $cp = $method;
	# add option to preserve mod time etc of copied files
	# not critical, but nice to have
	$cp.= " -p" if $^O =~ m/^(solaris|linux|freebsd|darwin)$/;

	# add recursive option for scp
	$cp.= " -r" if $^O =~ /m^(solaris|linux|freebsd|darwin)$/ && $method =~ /^scp\b/;

	my @non_raid = map { "'$_'" } grep { ! m:/\d{2}/[^/]+$: } @$files;

	# add files to copy and the destination directory
	safe_system( $cp, @non_raid, "'$target'" ) if (@non_raid);
	
	foreach my $rd ( @$raid_dirs ) {
	    my @raid = map { "'$_'" } grep { m:$rd/: } @$files;
	    safe_system( $cp, @raid, "'$target'/$rd" ) if ( @raid );
	}
    }
    else
    {
	die "Can't use unsupported method '$method'\n";
    }
}

#
# Copy only the header of the index file
#

sub copy_index
{
  my ($method, $files, $source, $target) = @_;
  
  print "Copying indices for ".@$files." files...\n" unless $opt{quiet};  
  foreach my $file (@$files)
  {
    my $from="$source/$file";
    my $to="$target/$file";
    my $buff;
    open(INPUT, "<$from") || die "Can't open file $from: $!\n";
    binmode(INPUT, ":raw");
    my $length=read INPUT, $buff, 2048;
    die "Can't read index header from $from\n" if ($length < 1024);
    close INPUT;
    
    if ( $opt{dryrun} )
    {
      print "$opt{method}-header $from $to\n";
    }
    elsif ($opt{method} eq 'cp')
    {
      open(OUTPUT,">$to")   || die "Can\'t create file $to: $!\n";
      if (syswrite(OUTPUT,$buff) != length($buff))
      {
	die "Error when writing data to $to: $!\n";
      }
      close OUTPUT	   || die "Error on close of $to: $!\n";
    }
    elsif ($opt{method} =~ /^scp\b/)
    {
      my ($fh, $tmp)= tempfile('mysqlhotcopy-XXXXXX', DIR => $opt_tmpdir) or
	die "Can\'t create/open file in $opt_tmpdir\n";
      if (syswrite($fh,$buff) != length($buff))
      {
	die "Error when writing data to $tmp: $!\n";
      }
      close $fh || die "Error on close of $tmp: $!\n";
      safe_system("$opt{method} $tmp $to");
      unlink $tmp;
    }
    else
    {
      die "Can't use unsupported method '$opt{method}'\n";
    }
  }
}


sub safe_system {
  my @sources= @_;
  my $method= shift @sources;
  my $target= pop @sources;
  ## @sources = list of source file names

  ## We have to deal with very long command lines, otherwise they may generate 
  ## "Argument list too long".
  ## With 10000 tables the command line can be around 1MB, much more than 128kB
  ## which is the common limit on Linux (can be read from
  ## /usr/src/linux/include/linux/binfmts.h
  ## see http://www.linuxjournal.com/article.php?sid=6060).
 
  my $chunk_limit= 100 * 1024; # 100 kB
  my @chunk= (); 
  my $chunk_length= 0;
  foreach (@sources) {
      push @chunk, $_;
      $chunk_length+= length($_);
      if ($chunk_length > $chunk_limit) {
          safe_simple_system($method, @chunk, $target);
          @chunk=();
          $chunk_length= 0;
      }
  }
  if ($chunk_length > 0) { # do not forget last small chunk
      safe_simple_system($method, @chunk, $target); 
  }
}

sub safe_simple_system {
    my @cmd= @_;

    if ( $opt{dryrun} ) {
        print "@cmd\n";
    }
    else {
        ## for some reason system fails but backticks works ok for scp...
        print "Executing '@cmd'\n" if $opt{debug};
        my $cp_status = system "@cmd > /dev/null";
        if ($cp_status != 0) {
            warn "Executing command failed ($cp_status). Trying backtick execution...\n";
            ## try something else
            `@cmd` || die "Error: @cmd failed ($?) while copying files.\n";
        }
    }
}

sub retire_directory {
    my ( @dir ) = @_;

    foreach my $dir ( @dir ) {
	my $tgt_oldpath = $dir . '_old';
	if ( $opt{dryrun} ) {
	    print "rmtree $tgt_oldpath\n" if ( -d $tgt_oldpath );
	    print "rename $dir, $tgt_oldpath\n";
	    next;
	}

	if ( -d $tgt_oldpath ) {
	    print "Deleting previous 'old' hotcopy directory ('$tgt_oldpath')\n" unless $opt{quiet};
	    rmtree([$tgt_oldpath],0,1);
	}
	rename($dir, $tgt_oldpath)
	  or die "Can't rename $dir=>$tgt_oldpath: $!\n";
	print "Existing hotcopy directory renamed to '$tgt_oldpath'\n" unless $opt{quiet};
    }
}

sub record_log_pos {
    my ( $dbh, $table_name ) = @_;

    eval {
	my ($file,$position) = get_row( $dbh, "show master status" );
	die "master status is undefined" if !defined $file || !defined $position;
	
	my $row_hash = get_row_hash( $dbh, "show slave status" );
	my ($master_host, $log_file, $log_pos ); 
	if ( $dbh->{mysql_serverinfo} =~ /^3\.23/ ) {
	    ($master_host, $log_file, $log_pos ) 
	      = @{$row_hash}{ qw / Master_Host Log_File Pos / };
	} else {
	    ($master_host, $log_file, $log_pos ) 
	      = @{$row_hash}{ qw / Master_Host Master_Log_File Read_Master_Log_Pos / };
	}
	my $hostname = hostname();