mysqlhotcopy

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

#!/usr/bin/perl -w

use strict;
use Getopt::Long;
use Data::Dumper;
use File::Basename;
use File::Path;
use DBI;
use Sys::Hostname;
use File::Copy;
use File::Temp qw(tempfile);

=head1 NAME

mysqlhotcopy - fast on-line hot-backup utility for local MySQL databases and tables

=head1 SYNOPSIS

  mysqlhotcopy db_name

  mysqlhotcopy --suffix=_copy db_name_1 ... db_name_n

  mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory

  mysqlhotcopy db_name./regex/

  mysqlhotcopy db_name./^\(foo\|bar\)/

  mysqlhotcopy db_name./~regex/

  mysqlhotcopy db_name_1./regex_1/ db_name_1./regex_2/ ... db_name_n./regex_n/ /path/to/new_directory

  mysqlhotcopy --method='scp -Bq -i /usr/home/foo/.ssh/identity' --user=root --password=secretpassword \
         db_1./^nice_table/ [e-mail]:~/path/to/new_directory

WARNING: THIS PROGRAM IS STILL IN BETA. Comments/patches welcome.

=cut

# Documentation continued at end of file

my $VERSION = "1.22";

my $opt_tmpdir = $ENV{TMPDIR} || "/tmp";

my $OPTIONS = <<"_OPTIONS";

$0 Ver $VERSION

Usage: $0 db_name[./table_regex/] [new_db_name | directory]

  -?, --help           display this helpscreen and exit
  -u, --user=#         user for database login if not current user
  -p, --password=#     password to use when connecting to server (if not set
                       in my.cnf, which is recommended)
  -h, --host=#         Hostname for local server when connecting over TCP/IP
  -P, --port=#         port to use when connecting to local server with TCP/IP
  -S, --socket=#       socket to use when connecting to local server

  --allowold           don\'t abort if target dir already exists (rename it _old)
  --addtodest          don\'t rename target dir if it exists, just add files to it
  --keepold            don\'t delete previous (now renamed) target when done
  --noindices          don\'t include full index files in copy
  --method=#           method for copy (only "cp" currently supported)

  -q, --quiet          be silent except for errors
  --debug              enable debug
  -n, --dryrun         report actions without doing them

  --regexp=#           copy all databases with names matching regexp
  --suffix=#           suffix for names of copied databases
  --checkpoint=#       insert checkpoint entry into specified db.table
  --flushlog           flush logs once all tables are locked 
  --resetmaster        reset the binlog once all tables are locked
  --resetslave         reset the master.info once all tables are locked
  --tmpdir=#	       temporary directory (instead of $opt_tmpdir)
  --record_log_pos=#   record slave and master status in specified db.table
  --chroot=#           base directory of chroot jail in which mysqld operates

  Try \'perldoc $0\' for more complete documentation
_OPTIONS

sub usage {
    die @_, $OPTIONS;
}

# Do not initialize user or password options; that way, any user/password
# options specified in option files will be used.  If no values are specified
# all, the defaults will be used (login name, no password).

my %opt = (
    noindices	=> 0,
    allowold	=> 0,	# for safety
    keepold	=> 0,
    method	=> "cp",
    flushlog    => 0,
);
Getopt::Long::Configure(qw(no_ignore_case)); # disambuguate -p and -P
GetOptions( \%opt,
    "help",
    "host|h=s",
    "user|u=s",
    "password|p=s",
    "port|P=s",
    "socket|S=s",
    "allowold!",
    "keepold!",
    "addtodest!",
    "noindices!",
    "method=s",
    "debug",
    "quiet|q",
    "mv!",
    "regexp=s",
    "suffix=s",
    "checkpoint=s",
    "record_log_pos=s",
    "flushlog",
    "resetmaster",
    "resetslave",
    "tmpdir|t=s",
    "dryrun|n",
    "chroot=s",
) or usage("Invalid option");

# @db_desc
# ==========
# a list of hash-refs containing:
#
#   'src'     - name of the db to copy
#   't_regex' - regex describing tables in src
#   'target'  - destination directory of the copy
#   'tables'  - array-ref to list of tables in the db
#   'files'   - array-ref to list of files to be copied
#               (RAID files look like 'nn/name.MYD')
#   'index'   - array-ref to list of indexes to be copied
#

my @db_desc = ();
my $tgt_name = undef;

usage("") if ($opt{help});

if ( $opt{regexp} || $opt{suffix} || @ARGV > 2 ) {
    $tgt_name   = pop @ARGV unless ( exists $opt{suffix} );
    @db_desc = map { s{^([^\.]+)\./(.+)/$}{$1}; { 'src' => $_, 't_regex' => ( $2 ? $2 : '.*' ) } } @ARGV;
}
else {
    usage("Database name to hotcopy not specified") unless ( @ARGV );

    $ARGV[0] =~ s{^([^\.]+)\./(.+)/$}{$1};
    @db_desc = ( { 'src' => $ARGV[0], 't_regex' => ( $2 ? $2 : '.*' ) } );

    if ( @ARGV == 2 ) {
	$tgt_name   = $ARGV[1];
    }
    else {
	$opt{suffix} = "_copy";
    }
}

my %mysqld_vars;
my $start_time = time;
$opt_tmpdir= $opt{tmpdir} if $opt{tmpdir};
$0 = $1 if $0 =~ m:/([^/]+)$:;
$opt{quiet} = 0 if $opt{debug};
$opt{allowold} = 1 if $opt{keepold};

# --- connect to the database ---
my $dsn;
$dsn  = ";host=" . (defined($opt{host}) ? $opt{host} : "localhost");
$dsn .= ";port=$opt{port}" if $opt{port};
$dsn .= ";mysql_socket=$opt{socket}" if $opt{socket};

# use mysql_read_default_group=mysqlhotcopy so that [client] and
# [mysqlhotcopy] groups will be read from standard options files.

my $dbh = DBI->connect("dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy",
                        $opt{user}, $opt{password},
{
    RaiseError => 1,
    PrintError => 0,
    AutoCommit => 1,
});

# --- check that checkpoint table exists if specified ---
if ( $opt{checkpoint} ) {
    $opt{checkpoint} = quote_names( $opt{checkpoint} );
    eval { $dbh->do( qq{ select time_stamp, src, dest, msg 
			 from $opt{checkpoint} where 1 != 1} );
       };

    die "Error accessing Checkpoint table ($opt{checkpoint}): $@"
      if ( $@ );
}

# --- check that log_pos table exists if specified ---
if ( $opt{record_log_pos} ) {
    $opt{record_log_pos} = quote_names( $opt{record_log_pos} );

    eval { $dbh->do( qq{ select host, time_stamp, log_file, log_pos, master_host, master_log_file, master_log_pos
			 from $opt{record_log_pos} where 1 != 1} );
       };

    die "Error accessing log_pos table ($opt{record_log_pos}): $@"
      if ( $@ );
}

# --- get variables from database ---
my $sth_vars = $dbh->prepare("show variables like 'datadir'");
$sth_vars->execute;
while ( my ($var,$value) = $sth_vars->fetchrow_array ) {
    $mysqld_vars{ $var } = $value;
}
my $datadir = $mysqld_vars{'datadir'}
    || die "datadir not in mysqld variables";
    $datadir= $opt{chroot}.$datadir if ($opt{chroot});
$datadir =~ s:/$::;


# --- get target path ---
my ($tgt_dirname, $to_other_database);
$to_other_database=0;
if (defined($tgt_name) && $tgt_name =~ m:^\w+$: && @db_desc <= 1)
{
    $tgt_dirname = "$datadir/$tgt_name";
    $to_other_database=1;
}
elsif (defined($tgt_name) && ($tgt_name =~ m:/: || $tgt_name eq '.')) {
    $tgt_dirname = $tgt_name;
}
elsif ( $opt{suffix} ) {
    print "Using copy suffix '$opt{suffix}'\n" unless $opt{quiet};
}
elsif ( ($^O =~ m/^(NetWare)$/) && defined($tgt_name) && ($tgt_name =~ m:\\: || $tgt_name eq '.'))  
{
	$tgt_dirname = $tgt_name;
}
else
{
  $tgt_name="" if (!defined($tgt_name));
  die "Target '$tgt_name' doesn't look like a database name or directory path.\n";
}

# --- resolve database names from regexp ---
if ( defined $opt{regexp} ) {
    my $t_regex = '.*';
    if ( $opt{regexp} =~ s{^/(.+)/\./(.+)/$}{$1} ) {
        $t_regex = $2;
    }

    my $sth_dbs = $dbh->prepare("show databases");
    $sth_dbs->execute;
    while ( my ($db_name) = $sth_dbs->fetchrow_array ) {
	next if $db_name =~ m/^information_schema$/i;
	push @db_desc, { 'src' => $db_name, 't_regex' => $t_regex } if ( $db_name =~ m/$opt{regexp}/o );
    }
}

# --- get list of tables to hotcopy ---

my $hc_locks = "";
my $hc_tables = "";
my $num_tables = 0;
my $num_files = 0;

foreach my $rdb ( @db_desc ) {
    my $db = $rdb->{src};
    my @dbh_tables = get_list_of_tables( $db );

    ## generate regex for tables/files
    my $t_regex;
    my $negated;
    if ($rdb->{t_regex}) {
        $t_regex = $rdb->{t_regex};        ## assign temporary regex
        $negated = $t_regex =~ s/^~//;     ## note and remove negation operator

        $t_regex = qr/$t_regex/;           ## make regex string from
                                           ## user regex

        ## filter (out) tables specified in t_regex
        print "Filtering tables with '$t_regex'\n" if $opt{debug};
        @dbh_tables = ( $negated 
                        ? grep { $_ !~ $t_regex } @dbh_tables
                        : grep { $_ =~ $t_regex } @dbh_tables );
    }

    ## get list of files to copy
    my $db_dir = "$datadir/$db";
    opendir(DBDIR, $db_dir ) 
      or die "Cannot open dir '$db_dir': $!";

    my %db_files;
    my @raid_dir = ();

    while ( defined( my $name = readdir DBDIR ) ) {
	if ( $name =~ /^\d\d$/ && -d "$db_dir/$name" ) {
	    push @raid_dir, $name;
	}
	else {
	    $db_files{$name} = $1 if ( $name =~ /(.+)\.\w+$/ );
        }
    }
    closedir( DBDIR );

    scan_raid_dir( \%db_files, $db_dir, @raid_dir );

    unless( keys %db_files ) {
	warn "'$db' is an empty database\n";
    }

    ## filter (out) files specified in t_regex
    my @db_files;
    if ($rdb->{t_regex}) {
        @db_files = ($negated
                     ? grep { $db_files{$_} !~ $t_regex } keys %db_files
                     : grep { $db_files{$_} =~ $t_regex } keys %db_files );
    }
    else {
        @db_files = keys %db_files;
    }

    @db_files = sort @db_files;

    my @index_files=();

    ## remove indices unless we're told to keep them
    if ($opt{noindices}) {
        @index_files= grep { /\.(ISM|MYI)$/ } @db_files;
	@db_files = grep { not /\.(ISM|MYI)$/ } @db_files;
    }

    $rdb->{files}  = [ @db_files ];
    $rdb->{index}  = [ @index_files ];
    my @hc_tables = map { quote_names("$db.$_") } @dbh_tables;
    $rdb->{tables} = [ @hc_tables ];

    $rdb->{raid_dirs} = [ get_raid_dirs( $rdb->{files} ) ];

    $hc_locks .= ", "  if ( length $hc_locks && @hc_tables );
    $hc_locks .= join ", ", map { "$_ READ" } @hc_tables;
    $hc_tables .= ", "  if ( length $hc_tables && @hc_tables );
    $hc_tables .= join ", ", @hc_tables;

    $num_tables += scalar @hc_tables;
    $num_files  += scalar @{$rdb->{files}};
}

# --- resolve targets for copies ---

if (defined($tgt_name) && length $tgt_name ) {
    # explicit destination directory specified

    # GNU `cp -r` error message
    die "copying multiple databases, but last argument ($tgt_dirname) is not a directory\n"
      if ( @db_desc > 1 && !(-e $tgt_dirname && -d $tgt_dirname ) );

    if ($to_other_database)
    {
      foreach my $rdb ( @db_desc ) {
	$rdb->{target} = "$tgt_dirname";
      }
    }
    elsif ($opt{method} =~ /^scp\b/) 
    {   # we have to trust scp to hit the target
	foreach my $rdb ( @db_desc ) {
	    $rdb->{target} = "$tgt_dirname/$rdb->{src}";
	}
    }
    else
    {
      die "Last argument ($tgt_dirname) is not a directory\n"
	if (!(-e $tgt_dirname && -d $tgt_dirname ) );
      foreach my $rdb ( @db_desc ) {
	$rdb->{target} = "$tgt_dirname/$rdb->{src}";
      }
    }
  }
else {
  die "Error: expected \$opt{suffix} to exist" unless ( exists $opt{suffix} );