CopyDBoverServer.pl 33 KB
Newer Older
1
#!/usr/bin/env perl
2
# Copyright [1999-2016] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
3
#
4 5 6
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
7
#
8
#      http://www.apache.org/licenses/LICENSE-2.0
9
#
10 11 12 13 14 15
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

16 17

use strict;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
18 19
use warnings;

20
use DBI;
21
use Socket;
22
use English qw( -no_match_vars );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
23 24 25
use File::Copy;
use File::Spec::Functions
  qw( rel2abs curdir canonpath updir catdir catfile );
26
use File::Temp;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
27 28 29
use Getopt::Long;
use IO::File;
use Sys::Hostname;
Andy Yates's avatar
Andy Yates committed
30
use Tie::File;
31 32

$OUTPUT_AUTOFLUSH = 1;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
33 34 35 36

my $start_time = time();

sub short_usage {
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
37
  print <<SHORT_USAGE_END;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
38
Usage:
39
  $0 --pass=XXX \\
40
  \t[--user=XXX] \\
41
  \t[--noflush] [--nocheck] [--notargetflush]\\
42 43 44 45 46
  \t[--noopt] [--noinnodb] [--skip_views] [--force] \\
  \t[ --only_tables=XXX,YYY | --skip_tables=XXX,YYY ] \\
  \t[ input_file |
  \t  --source=db\@host[:port] \\
  \t  --target=db\@host[:port] ]
47 48

  $0 --help
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
49 50 51

  Use --help to get a much longer help text.

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
52
SHORT_USAGE_END
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
53 54 55
}

sub long_usage {
56
  short_usage();
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
57

58
  print <<LONG_USAGE_END;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
59 60 61 62 63 64 65 66 67
Description:

  Safetly copy MySQL databases between different servers and run
  myisamchk on the indexes when done.

  The script A) transfers the database files to a local staging
  directory, B) checks the files for errors using myisamchk, and C)
  moves them into place in the database server directory.

68

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
69
Command line switches:
70

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
71
  --pass=XXX        (Required)
72 73 74 75 76 77
                    The password for the MySQL user to
                    connect to the database server.

  --user=XXX        (Optional)
                    MySQL user to connect to the database server.
                    Default will be 'ensadmin'.
78

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
79 80 81 82 83
  --noflush         (Optional)
                    Skips table flushing completely.  Use very
                    sparsingly as copying un-flushed databases
                    potentially means missing data not yet flushed to
                    disk.  Use only after due consideration.
84

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
85 86 87 88 89 90
  --nocheck         (Optional)
                    Skip running myisamchk on the copied table files.
                    Use this only if you are *absolutely* sure that the
                    table files are ok and that you really do not have
                    time to wait for the check to run.  Use only after
                    due consideration.
91

92 93 94 95 96 97
  --noopt           (Optional)
                    Skip the optimization step.  The database tables are
                    optimized (as with "OPTIMIZE TABLE") to flush the
                    index data onto disk.  This may be a time consuming
                    operation for very large tables.  The --noopt flag
                    disables the optimization.
98 99 100 101 102

                    DO NOT RUN OPTIMIZE WHEN YOU ARE ON THE STAGING
                    MACHINES. THESE ARE OPTIMIZED BY THE PRODUCTION
                    TEAM DURING THE RELEASE CYCLE. ATTEMPTING TO DO
                    THIS WILL CAUSE THE SCRIPT TO DIE.
103

104 105
  --notargetflush   (Optional)
                    Skips table flushing on the target machine.
106

107
  --noinnodb        (Optional)
108 109
                    Skip the copy of any InnoDB table encountered.
                    Default is to include and fail when InnoDB seen.
110

Andy Yates's avatar
Andy Yates committed
111
  --skip_views      (Optional)
112 113
                    Exclude 'view' tables in any copy.  Default is to
                    process them.
114

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
115 116 117 118 119
  --force           (Optional)
                    Ordinarily, the script refuses to overwrite an
                    already existing staging directory.  This switch
                    will force it to re-use the directory if it exists.
                    This is useful for continuing an aborted copy.
120

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
121 122
                    NOTE: This switch will not ever force overwriting of
                    a server database directory.
123

124 125 126
                    NOTE: Using --force will bypass the table
                    optimization step.

127 128 129 130 131 132 133 134 135
  --only_tables=XXX,YYY
                    (Optional)
                    Only copy the tables specified in the
                    comma-delimited list.

  --skip_tables=XXX,YYY
                    (Optional)
                    Copy all tables except the ones specified in the
                    comma-delimited list.
136

137
  --tmpdir=TMP      (Optional)
138 139 140 141
                    Allows for a non-standard staging location to be
                    used during the copy process.  Normally it will
                    create a directory called 'tmp' in the directory
                    above the target data directory.
142

143 144 145
  --routines        (Optional)
                    Also copies functions and procedures

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
146 147
  --help            (Optional)
                    Displays this text.
148

149 150 151 152 153 154 155 156 157 158 159
The script takes either an input file of a certain format (see below),
or two additional switches:

  --source          (Optional, but required if there is no input file)
                    The source database to copy on the form
                    "db\@host:port" where the ":port" part is optional.

  --target          (Optional, but required if --source is used)
                    The target database to copy to on the same form as
                    with the --source switch.  The target database must
                    not already exist.
160

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
161
Input file format:
162

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
163 164
  The input file should be a tab/space-separated file with six fields
  per line:
165

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
166 167 168
    1. Source server
    2. Source server port
    3. Source database name
169

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
170 171 172
    4. Target server
    5. Target server port
    6. Target database name
173
    7. Target location (optional)
174

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
175
  For example:
176

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
177
  genebuild1 3306 at6_gor2_wga ens-staging1 3306 gorilla_gorilla_core_57_1
178

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
179
  (with each field being separated by a tab or space).
180

181 182 183 184 185 186 187 188
  This corresponds to the following command line options:

      --source=at6_gor2_wga\@genebuild1 \\
      --target=gorilla_gorilla_core_57_1\@ens-staging1

  (note that the port 3306 is the default port, and thus does not need
  to be mentioned)

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
189 190
  Blank lines, lines containing only whitespaces, and lines starting
  with '#', are silently ignored.
191

192 193
  Column 7 is used only when you need to copy the database to a location
  which is not the MySQL server's data directory.  The same rules
194
  applies; the mysqlens user at Sanger or other user at EBI must have write access to this directory
195 196 197
  and to the one above to create any temporary staging directory
  structures.  There is currently no way to specify this on the
  command-line.
198 199


Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
200
Script restrictions:
201

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
202
  1. You must run the script on the destination server.
203

204
  2. The script must be run as the 'mysqlens' Unix user at Sanger. Talk to a
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
205 206
     recent release coordinator for access.

207
  3. The script will only copy MYISAM tables.  Databases with InnoDB
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
208 209 210 211
     tables will have to be copied manually using mysqldump.  InnoDB
     tables will make the script throw an error in the table checking
     stage.

212

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
213
LONG_USAGE_END
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
214 215
} ## end sub long_usage

216
my ( $opt_password, $opt_user, $opt_only_tables, $opt_skip_tables, $opt_help );
217

218 219 220 221
my $opt_flush    = 1;    # Flush by default.
my $opt_check    = 1;    # Check tables by default.
my $opt_optimize = 1;    # Optimize the tables by default.
my $opt_force = 0; # Do not reuse existing staging directory by default.
222 223
my $opt_skip_views = 0;    # Process views by default
my $opt_innodb     = 1;    # Don't skip InnoDB by default
224
my $opt_flushtarget = 1;
Andy Yates's avatar
Andy Yates committed
225
my $opt_tmpdir;
226
my $opt_routines = 0;
227
my ( $opt_source, $opt_target );
228

229
if ( !GetOptions( 'pass=s'        => \$opt_password,
230
                  'user=s'        => \$opt_user,
231
                  'flush!'        => \$opt_flush,
232
                  'flushtarget!'  => \$opt_flushtarget,
233 234 235
                  'check!'        => \$opt_check,
                  'opt!'          => \$opt_optimize,
                  'force!'        => \$opt_force,
236 237 238
                  'only_tables=s' => \$opt_only_tables,
                  'skip_tables=s' => \$opt_skip_tables,
                  'innodb!'       => \$opt_innodb,
Andy Yates's avatar
Andy Yates committed
239
                  'skip_views!'   => \$opt_skip_views,
Andy Yates's avatar
Andy Yates committed
240
                  'tmpdir=s'      => \$opt_tmpdir,
241 242
                  'help!'         => \$opt_help,
                  'source=s'      => \$opt_source,
243
                  'target=s'      => \$opt_target,
244
                  'routines'      => \$opt_routines,
245 246
     ) ||
     ( !defined($opt_password) && !defined($opt_help) ) )
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
247 248 249 250 251 252 253
{
  short_usage();
  exit 1;
}

if ( defined($opt_help) ) {
  long_usage();
254 255 256
  exit 0;
}

257 258 259 260 261 262
if ( ( defined($opt_source) && !defined($opt_target) ) ||
     ( !defined($opt_source) && defined($opt_target) ) )
{
  die("You must use --source and --target together.\n");
}

263 264 265 266
if ( defined($opt_skip_tables) && defined($opt_only_tables) ) {
  die("Can't use both --only_tables and --skip_tables\n");
}

267 268 269 270 271
if ( $opt_force && $opt_optimize ) {
  print("Note: Using --force will bypass optimization.\n");
  $opt_optimize = 0;
}

272 273 274 275 276 277 278 279 280
my %only_tables;
if ( defined($opt_only_tables) ) {
  %only_tables = map( { $_ => 1 } split( /,/, $opt_only_tables ) );
}
my %skip_tables;
if ( defined($opt_skip_tables) ) {
  %skip_tables = map( { $_ => 1 } split( /,/, $opt_skip_tables ) );
}

281
if ( scalar( getpwuid($<) ) ne 'mysqlens' ) {
282 283 284 285 286 287
  warn("You are not running this script as the 'mysqlens' user.\n");
}

if (!defined($opt_user))
{
  $opt_user='ensadmin';
288 289
}

290
my $input_file;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
291

292 293 294 295 296 297 298
if ( !defined($opt_source) ) {
  $input_file = shift(@ARGV);

  if ( !defined($input_file) ) {
    short_usage();
    exit 1;
  }
299 300
}

301
my @executables =('myisamchk','rsync');
302

303
# Make sure we can find all executables.
304 305
foreach my $key (@executables) {
  my $output = `which $key`;
306
  my $rc     = $? >> 8;
307 308 309 310 311 312 313 314 315 316 317 318 319 320
  
  if($rc != 0) {
    chomp $output;
    die(
      sprintf(
      "Can not find '%s' and 'which %s' "
      . "yields anything useful. Check your \$PATH",
      $key, $key
      ) );
  }
  else {
    if ( !$opt_check && $key eq 'myisamchk' ) {
      print( "Can not find 'myisamchk' " .
      "but --nocheck was specified so skipping\n" ),;
321
    }
322 323
  } 
} ## end foreach my $key ( @executables...)
324

325
my $run_hostaddr = hostname_to_ip(hostname());
326
my $working_dir = rel2abs( curdir() );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
327 328 329

##====================================================================##
##  Read the configuration file line by line and try to validate all  ##
330 331
##  parts of each line.  Store the validated information in the @todo ##
##  list (a list of hashes) for later processing.                     ##
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
332 333
##====================================================================##

334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359
my $do_unlink_tmp_file = 0;

if ( !defined($input_file) ) {
  # The user uses --source and --target rather than an input file.
  # Create our own temporary input file with the info from these options
  # and then parse that.  #FIXME: this is a hack.
  my $out = File::Temp->new( UNLINK => 0 ) or
    die("Can not create temporary file.\n");

  my ( $source_db, $source_server, $source_port ) =
    ( $opt_source =~ /^([^@]+)@([^:]+):?(\d+)?$/ );
  my ( $target_db, $target_server, $target_port ) =
    ( $opt_target =~ /^([^@]+)@([^:]+):?(\d+)?$/ );

  $out->printf( "%s %d %s %s %d %s\n",
                $source_server,
                defined($source_port) ? $source_port : 3306,
                $source_db,
                $target_server,
                defined($target_port) ? $target_port : 3306,
                $target_db );

  $input_file = $out->filename();

  # This temporary file will be unlinked further down.
  $do_unlink_tmp_file = 1;
360
} ## end if ( !defined($input_file...))
361

362 363
my $in = IO::File->new( '<' . $input_file ) or
  die( sprintf( "Can not open '%s' for reading", $input_file ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
364

365
my @todo;    # List of verified databases etc. to copy.
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
366 367 368 369 370

my $lineno = 0;
while ( my $line = $in->getline() ) {
  ++$lineno;

371 372
  $line =~ s/^\s+//;    # Strip leading whitespace.
  $line =~ s/\s+$//;    # Strip trailing whitespace.
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
373 374 375 376

  if ( $line =~ /^\#/ )   { next }    # Comment line.
  if ( $line =~ /^\s*$/ ) { next }    # Empty line.

377
  my $failed = 0;                     # Haven't failed so far...
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
378

379
  my ( $source_server, $source_port, $source_db,
380 381
       $target_server, $target_port, $target_db,
       $target_location
Leo Gordon's avatar
Leo Gordon committed
382
  ) = split( /\s+/, $line );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
383

384 385
  my $source_hostaddr = hostname_to_ip($source_server);
  my $target_hostaddr = hostname_to_ip($target_server);
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
386 387

  # Verify source server and port.
388 389
  if ( !defined($source_hostaddr) || $source_hostaddr eq '' ) {
    warn( sprintf( "line %d: Source server '%s' is not valid.\n",
390 391
                   $lineno, $source_server
          ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
392
    $failed = 1;
393
  }
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
394 395

  if ( !defined($source_port) || $source_port =~ /\D/ ) {
396
    warn( sprintf( "line %d: Source port '%s' is not a number.\n",
397 398
                   $lineno, $source_port || ''
          ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
399
    $failed = 1;
400 401
  }

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
402
  # Verify target server and port.
403 404
  if ( !defined($target_hostaddr) || $target_hostaddr eq '' ) {
    warn( sprintf( "line %d: Target server '%s' is not valid.\n",
405 406
                   $lineno, $target_server
          ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
407
    $failed = 1;
408
  }
409

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
410
  if ( !defined($target_port) || $target_port =~ /\D/ ) {
411
    warn( sprintf( "line %d: Target port '%s' is not a number.\n",
412 413
                   $lineno, $target_port || ''
          ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
414 415 416 417
    $failed = 1;
  }

  # Make sure we running on the target server.
418
  if ( !$failed && $run_hostaddr ne $target_hostaddr ) {
419
    warn( sprintf(
420 421 422 423 424
            "line %d: " .
              "This script needs to be run on the destination server " .
              "'%s' ('%s').\n",
            $lineno, $target_server, $target_hostaddr
          ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
425 426
    $failed = 1;
  }
427

428
  if(! $failed && $source_server =~ /^ens-staging\d?$/ && $opt_optimize) {
429 430 431 432 433
    my $tmpl = q{line %d: Source server '%s' is an ens-staging machine. Do not optimize on this server. Rerun with -noopt};
    warn(sprintf($tmpl, $lineno, $source_server));
    $failed = 1;
  }

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
434
  if ( !$failed ) {
435 436
    push( @todo, {
            'source_server'   => $source_server,
437
            'source_hostaddr' => $source_hostaddr,
438 439 440
            'source_port'     => $source_port,
            'source_db'       => $source_db,
            'target_server'   => $target_server,
441
            'target_hostaddr' => $target_hostaddr,
442
            'target_port'     => $target_port,
443
            'target_db'       => $target_db,
444 445
            'target_location' => $target_location,
          } );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
446 447
  }
} ## end while ( my $line = $in->getline...)
448

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
449
$in->close();
450

451 452 453 454 455
if ($do_unlink_tmp_file) {
  # Unlink the temporary input file.
  unlink($input_file);
}

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
456 457
##====================================================================##
##  Take the copy specifications from the @todo list and for each     ##
458
##  specification copy the database to a staging area using rsync,    ##
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
459 460 461
##  check it with myisamchk, and move it in place in the database     ##
##  directory.                                                        ##
##====================================================================##
462

463
TODO:
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
464 465
foreach my $spec (@todo) {
  my $source_server   = $spec->{'source_server'};
466
  my $source_hostaddr = $spec->{'source_hostaddr'};
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
467 468 469
  my $source_port     = $spec->{'source_port'};
  my $source_db       = $spec->{'source_db'};
  my $target_server   = $spec->{'target_server'};
470
  my $target_hostaddr = $spec->{'target_hostaddr'};
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
471 472
  my $target_port     = $spec->{'target_port'};
  my $target_db       = $spec->{'target_db'};
473
  my $target_location = $spec->{'target_location'};
474

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
475 476
  my $label = sprintf( "{ %s -> %s }==", $source_db, $target_db );
  print( '=' x ( 80 - length($label) ), $label, "\n" );
477

478 479
  print( "CONNECTING TO SOURCE AND TARGET DATABASES " .
         "TO GET 'datadir'\n" );
480 481

  my $source_dsn = sprintf( "DBI:mysql:database=%s;host=%s;port=%d",
482
                            $source_db, $source_hostaddr,
483
                            $source_port );
484

485
  my $source_dbh = DBI->connect( $source_dsn,
486
                                 $opt_user,
487 488
                                 $opt_password, {
                                   'PrintError' => 1,
489 490
                                   'AutoCommit' => 0
                                 } );
491 492

  if ( !defined($source_dbh) ) {
493
    warn( sprintf(
494 495 496
               "Failed to connect to the source database '%s@%s:%d'.\n",
               $source_db, $source_server, $source_port
          ) );
497 498

    $spec->{'status'} =
499 500
      sprintf( "FAILED: can not connect to source database '%s@%s:%d'.",
               $source_db, $source_server, $source_port );
501
    next TODO;
502 503 504
  }

  my $target_dsn = sprintf( "DBI:mysql:host=%s;port=%d",
505
                            $target_hostaddr, $target_port );
506

507
  my $target_dbh = DBI->connect( $target_dsn,
508
                                 $opt_user,
509 510
                                 $opt_password, {
                                   'PrintError' => 1,
511 512
                                   'AutoCommit' => 0
                                 } );
513 514

  if ( !defined($target_dbh) ) {
515
    warn( sprintf( "Failed to connect to the target server '%s:%d'.\n",
516 517
                   $target_server, $target_port
          ) );
518 519

    $spec->{'status'} =
520
      sprintf( "FAILED: can not connect to target server '%s:%d'.",
521
               $target_server, $target_port );
522 523

    $source_dbh->disconnect();
524
    next TODO;
525 526 527 528 529 530 531 532 533 534 535
  }

  # Get source and target server data directories.
  my $source_dir =
    $source_dbh->selectall_arrayref("SHOW VARIABLES LIKE 'datadir'")
    ->[0][1];
  my $target_dir =
    $target_dbh->selectall_arrayref("SHOW VARIABLES LIKE 'datadir'")
    ->[0][1];

  $target_dbh->disconnect();
536

537 538 539 540
  if ( !defined($source_dir) ) {
    warn(
      sprintf(
        "Failed to find data directory for source server at '%s:%d'.\n",
541 542
        $source_server, $source_port
      ) );
543 544

    $spec->{'status'} = sprintf(
545 546
        "FAILED: can not find data directory on source server '%s:%d'.",
        $source_server, $source_port );
547 548

    $source_dbh->disconnect();
549
    next TODO;
550 551
  }

552
  if ( defined($target_location) ) { $target_dir = $target_location }
553

554 555 556 557
  if ( !defined($target_dir) ) {
    warn(
      sprintf(
        "Failed to find data directory for target server at '%s:%d'.\n",
558 559
        $target_server, $target_port
      ) );
560 561

    $spec->{'status'} = sprintf(
562 563
        "FAILED: can not find data directory on target server '%s:%d'.",
        $target_server, $target_port );
564 565

    $source_dbh->disconnect();
566
    next TODO;
567
  }
568

569
  my $tmp_dir;
570
  if ( defined($opt_tmpdir) ) { $tmp_dir = $opt_tmpdir }
571 572 573
  else {
    $tmp_dir = canonpath( catdir( $target_dir, updir(), 'tmp' ) );
  }
574 575 576

  printf( "SOURCE 'datadir' = '%s'\n", $source_dir );
  printf( "TARGET 'datadir' = '%s'\n", $target_dir );
577
  printf( "TMPDIR = %s\n",             $tmp_dir );
578

579
  my $staging_dir = catdir( $tmp_dir, sprintf( "tmp.%s", $target_db ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
580 581 582 583 584 585 586 587 588 589
  my $destination_dir = catdir( $target_dir, $target_db );

  $spec->{'status'} = 'SUCCESS';    # Assume success until failure.

  # Try to make sure the temporary directory and the final destination
  # directory actually exists, and that the staging directory within the
  # temporary directory does *not* exist.  Allow the staging directory
  # to be reused when the --force switch is used.

  if ( !-d $tmp_dir ) {
590
    die(sprintf( "Can not find the temporary directory '%s'", $tmp_dir )
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
591
    );
592
  }
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
593 594

  if ( -d $destination_dir ) {
595 596
    warn( sprintf( "Destination directory '%s' already exists.\n",
                   $destination_dir ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
597 598 599

    $spec->{'status'} =
      sprintf( "FAILED: database destination directory '%s' exists.",
600
               $destination_dir );
601 602

    $source_dbh->disconnect();
603
    next TODO;
604 605
  }

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
606
  if ( !$opt_force && -d $staging_dir ) {
607 608
    warn( sprintf( "Staging directory '%s' already exists.\n",
                   $staging_dir ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
609 610 611

    $spec->{'status'} =
      sprintf( "FAILED: staging directory '%s' exists.", $staging_dir );
612 613

    $source_dbh->disconnect();
614
    next TODO;
615 616
  }

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
617 618
  if ( !mkdir($staging_dir) ) {
    if ( !$opt_force || !-d $staging_dir ) {
619 620
      warn( sprintf( "Failed to create staging directory '%s'.\n",
                     $staging_dir ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
621 622 623

      $spec->{'status'} =
        sprintf( "FAILED: can not create staging directory '%s'.",
624
                 $staging_dir );
625 626

      $source_dbh->disconnect();
627
      next TODO;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
628
    }
629 630
  }

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
631
  my @tables;
Andy Yates's avatar
Andy Yates committed
632
  my @views;
633 634 635 636 637 638 639 640 641

  my $table_sth = $source_dbh->prepare('SHOW TABLE STATUS');

  $table_sth->execute();

  my %row;
  # Fancy magic from DBI manual.
  $table_sth->bind_columns( \( @row{ @{ $table_sth->{'NAME_lc'} } } ) );

642
TABLE:
643 644 645 646 647 648
  while ( $table_sth->fetch() ) {
    my $table  = $row{'name'};
    my $engine = $row{'engine'};

    if ( defined($opt_only_tables) && !exists( $only_tables{$table} ) )
    {
649 650 651 652 653 654
      next TABLE;
    }
    elsif ( defined($opt_skip_tables) &&
            exists( $skip_tables{$table} ) )
    {
      next TABLE;
655 656 657 658 659
    }

    if ( defined($engine) ) {
      if ( $engine eq 'InnoDB' ) {
        if ( !$opt_innodb ) {
660
          warn( sprintf( "SKIPPING InnoDB table '%s.%s'\n",
661 662 663 664 665 666 667 668 669 670 671
                         $source_db, $table
                ) );
          next TABLE;
        }
        else {
          $spec->{'status'} =
            sprintf( "FAILED: can not copy InnoDB tables.  " .
                       "Please convert '%s.%s' to MyISAM " .
                       "or run with --noinnodb.",
                     $source_db, $table );
          next TODO;
672 673
        }
      }
674 675 676
    }
    else {
      if ($opt_skip_views) {
Andy Yates's avatar
Andy Yates committed
677 678 679
        warn( sprintf( "SKIPPING view '%s'\n", $table ) );
      }
      else {
680
        push( @views, $table );
Andy Yates's avatar
Andy Yates committed
681
      }
Andy Yates's avatar
Andy Yates committed
682
      next TABLE;
683 684 685 686
    }

    push( @tables, $table );
  } ## end while ( $table_sth->fetch...)
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
687

688 689 690
  # Lock tables with a read lock.
  print("LOCKING TABLES...\n");
  $source_dbh->do(
691
         sprintf( "LOCK TABLES %s READ", join( ' READ, ', @tables ) ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
692

693 694
  if ($opt_flush) {
    # Flush tables.
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
695 696

    print("FLUSHING TABLES...\n");
697
    $source_dbh->do(
698
                  sprintf( "FLUSH TABLES %s", join( ', ', @tables ) ) );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
699 700
  }

701 702 703 704 705 706 707 708 709 710 711 712 713 714
  ##------------------------------------------------------------------##
  ## OPTIMIZE                                                         ##
  ##------------------------------------------------------------------##

  if ($opt_optimize) {
    print( '-' x 35, ' OPTIMIZE ', '-' x 35, "\n" );

    foreach my $table (@tables) {
      printf( "Optimizing table '%s'...", $table );
      $source_dbh->do( sprintf( "OPTIMIZE TABLE %s", $table ) );
      print("\tok\n");
    }
  }

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
715 716 717 718 719 720 721 722 723 724 725
  ##------------------------------------------------------------------##
  ## COPY                                                             ##
  ##------------------------------------------------------------------##

  print( '-' x 37, ' COPY ', '-' x 37, "\n" );

  # Set up database copying.  We're using rsync for this because it's
  # using SSH for network transfers, because it may be used for local
  # copy too, and because it has good inclusion/exclusion filter
  # options.

726
  my @copy_cmd;
727 728 729
  
  @copy_cmd = ('rsync');

730
  push(@copy_cmd, '--whole-file', '--archive', '--progress' );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
731 732 733

  if ($opt_force) {
    push( @copy_cmd, '--delete', '--delete-excluded' );
734 735
  }

736 737 738
  # Set files permission to 755 (rwxr-xr-x)
  push (@copy_cmd, '--chmod=Du=rwx,go=rx,Fu=rwx,go=rx');  

739 740 741
  # Add TCP with arcfour encryption, TCP does go pretty fast (~110 MB/s) and is a better choice in LAN situation.
  push(@copy_cmd, '-e', q{ssh -c arcfour} );

742 743
  if ( defined($opt_only_tables) ) {
    push( @copy_cmd, '--include=db.opt' );
744

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
745
    push( @copy_cmd,
746
          map { sprintf( '--include=%s.*', $_ ) } keys(%only_tables) );
747 748 749 750 751 752

    # Partitioned tables:
    push( @copy_cmd,
          map { sprintf( '--include=%s#P#*.*', $_ ) }
            keys(%only_tables) );

Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
753
    push( @copy_cmd, "--exclude=*" );
754 755
  }
  elsif ( defined($opt_skip_tables) ) {
756
    push( @copy_cmd, '--include=db.opt' );
757

758
    push( @copy_cmd,
759
          map { sprintf( '--exclude=%s.*', $_ ) } keys(%skip_tables) );
760 761 762 763 764 765

    # Partitioned tables:
    push( @copy_cmd,
          map { sprintf( '--exclude=%s#P#*.*', $_ ) }
            keys(%skip_tables) );

766
    push( @copy_cmd, "--include=*" );
767
  }
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
768

769
  if ( $source_hostaddr eq $target_hostaddr ) {
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
770 771
    # Local copy.
    push( @copy_cmd,
772
          sprintf( "%s/", catdir( $source_dir, $source_db ) ) );
773 774
  }
  else {
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
775
    # Copy from remote server.
776 777
    push( @copy_cmd,
          sprintf( "%s:%s/",
778
                   $source_hostaddr, catdir( $source_dir, $source_db ) )
779
    );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
780 781 782 783 784 785 786
  }

  push( @copy_cmd, sprintf( "%s/", $staging_dir ) );

  # Perform the copy and make sure it succeeds.

  printf( "COPYING '%s:%d/%s' TO STAGING DIRECTORY '%s'\n",
787
          $source_server, $source_port, $source_db, $staging_dir );
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
788 789 790 791

  # For debugging:
  # print( join( ' ', @copy_cmd ), "\n" );

792
  my $copy_failed = 0;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
793
  if ( system(@copy_cmd) != 0 ) {
794 795 796 797
    warn( sprintf( "Failed to copy database.\n" .
                     "Please clean up '%s' (if needed).",
                   $staging_dir
          ) );
798
    $copy_failed = 1;
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
799 800
  }

801 802 803
  # Unlock tables.
  print("UNLOCKING TABLES...\n");
  $source_dbh->do('UNLOCK TABLES');
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
804

805 806
  $source_dbh->disconnect();

807
  if ($copy_failed) {
Andreas Kusalananda Kähäri's avatar
Andreas Kusalananda Kähäri committed
808 809
    $spec->{'status'} =
      sprintf( "FAILED: copy failed (cleanup of '%s' may be needed).",
810
               $staging_dir );
811
    next TODO;
812
  }
813

Andy Yates's avatar
Andy Yates committed
814 815 816 817
  ##------------------------------------------------------------------##
  ## VIEW REPAIR                                                      ##
  ##------------------------------------------------------------------##
  print( '-' x 36, ' VIEW REPAIR ', '-' x 37, "\n" );
818

819
  if ($opt_skip_views) {
Andy Yates's avatar
Andy Yates committed
820 821 822 823
    print 'SKIPPING VIEWS...', "\n";
  }
  else {
    print 'PROCESSING VIEWS...', "\n";
824 825 826 827

    if ( $source_db eq $target_db ) {
      print( "Source and target names (${source_db}) are the same; " .
             "views do not need repairing\n" );
Andy Yates's avatar
Andy Yates committed
828 829 830
    }
    else {
      my $ok = 1;
831

832
    VIEW:
Andy Yates's avatar
Andy Yates committed
833 834
      foreach my $current_view (@views) {
        print "Processing $current_view\n";
835 836 837 838 839

        my $view_frm_loc =
          catfile( $staging_dir, "${current_view}.frm" );

        if ( tie my @view_frm, 'Tie::File', $view_frm_loc ) {
Andy Yates's avatar
Andy Yates committed
840 841 842 843 844 845
          for (@view_frm) {
            s/`$source_db`/`$target_db`/g;
          }
          untie @view_frm;
        }
        else {