3
# Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
5
# This program is free software; you can redistribute it and/or
6
# modify it under the terms of the GNU Library General Public
7
# License as published by the Free Software Foundation; version 2
10
# This program is distributed in the hope that it will be useful,
11
# but WITHOUT ANY WARRANTY; without even the implied warranty of
12
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
13
# Library General Public License for more details.
15
# You should have received a copy of the GNU Library General Public
16
# License along with this library; if not, write to the Free
17
# Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston,
28
use File::Temp qw(tempfile);
32
mysqlhotcopy - fast on-line hot-backup utility for local MySQL databases and tables
38
mysqlhotcopy --suffix=_copy db_name_1 ... db_name_n
40
mysqlhotcopy db_name_1 ... db_name_n /path/to/new_directory
42
mysqlhotcopy db_name./regex/
44
mysqlhotcopy db_name./^\(foo\|bar\)/
46
mysqlhotcopy db_name./~regex/
48
mysqlhotcopy db_name_1./regex_1/ db_name_1./regex_2/ ... db_name_n./regex_n/ /path/to/new_directory
50
mysqlhotcopy --method='scp -Bq -i /usr/home/foo/.ssh/identity' --user=root --password=secretpassword \
51
db_1./^nice_table/ user@some.system.dom:~/path/to/new_directory
53
WARNING: THIS PROGRAM IS STILL IN BETA. Comments/patches welcome.
57
# Documentation continued at end of file
61
my $opt_tmpdir = $ENV{TMPDIR} || "/tmp";
63
my $OPTIONS = <<"_OPTIONS";
67
Usage: $0 db_name[./table_regex/] [new_db_name | directory]
69
-?, --help display this help-screen and exit
70
-u, --user=# user for database login if not current user
71
-p, --password=# password to use when connecting to server (if not set
72
in my.cnf, which is recommended)
73
-h, --host=# hostname for local server when connecting over TCP/IP
74
-P, --port=# port to use when connecting to local server with TCP/IP
75
-S, --socket=# socket to use when connecting to local server
76
--old_server connect to old MySQL-server (before v5.5) which
77
doesn't have FLUSH TABLES WITH READ LOCK fully implemented.
79
--allowold don\'t abort if target dir already exists (rename it _old)
80
--addtodest don\'t rename target dir if it exists, just add files to it
81
--keepold don\'t delete previous (now renamed) target when done
82
--noindices don\'t include full index files in copy
83
--method=# method for copy (only "cp" currently supported)
85
-q, --quiet be silent except for errors
87
-n, --dryrun report actions without doing them
89
--regexp=# copy all databases with names matching regexp
90
--suffix=# suffix for names of copied databases
91
--checkpoint=# insert checkpoint entry into specified db.table
92
--flushlog flush logs once all tables are locked
93
--resetmaster reset the binlog once all tables are locked
94
--resetslave reset the master.info once all tables are locked
95
--tmpdir=# temporary directory (instead of $opt_tmpdir)
96
--record_log_pos=# record slave and master status in specified db.table
97
--chroot=# base directory of chroot jail in which mysqld operates
99
Try \'perldoc $0\' for more complete documentation
106
# Do not initialize user or password options; that way, any user/password
107
# options specified in option files will be used. If no values are specified
108
# at all, the defaults will be used (login name, no password).
112
allowold => 0, # for safety
117
Getopt::Long::Configure(qw(no_ignore_case)); # disambiguate -p and -P
144
) or usage("Invalid option");
148
# a list of hash-refs containing:
150
# 'src' - name of the db to copy
151
# 't_regex' - regex describing tables in src
152
# 'target' - destination directory of the copy
153
# 'tables' - array-ref to list of tables in the db
154
# 'files' - array-ref to list of files to be copied
155
# 'index' - array-ref to list of indexes to be copied
159
my $tgt_name = undef;
161
usage("") if ($opt{help});
163
if ( $opt{regexp} || $opt{suffix} || @ARGV > 2 ) {
164
$tgt_name = pop @ARGV unless ( exists $opt{suffix} );
165
@db_desc = map { s{^([^\.]+)\./(.+)/$}{$1}; { 'src' => $_, 't_regex' => ( $2 ? $2 : '.*' ) } } @ARGV;
168
usage("Database name to hotcopy not specified") unless ( @ARGV );
170
$ARGV[0] =~ s{^([^\.]+)\./(.+)/$}{$1};
171
@db_desc = ( { 'src' => $ARGV[0], 't_regex' => ( $2 ? $2 : '.*' ) } );
174
$tgt_name = $ARGV[1];
177
$opt{suffix} = "_copy";
182
my $start_time = time;
183
$opt_tmpdir= $opt{tmpdir} if $opt{tmpdir};
184
$0 = $1 if $0 =~ m:/([^/]+)$:;
185
$opt{quiet} = 0 if $opt{debug};
186
$opt{allowold} = 1 if $opt{keepold};
188
# --- connect to the database ---
190
$dsn = ";host=" . (defined($opt{host}) ? $opt{host} : "localhost");
191
$dsn .= ";port=$opt{port}" if $opt{port};
192
$dsn .= ";mysql_socket=$opt{socket}" if $opt{socket};
194
# use mysql_read_default_group=mysqlhotcopy so that [client] and
195
# [mysqlhotcopy] groups will be read from standard options files.
197
my $dbh = DBI->connect("dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy",
198
$opt{user}, $opt{password},
205
# --- check that checkpoint table exists if specified ---
206
if ( $opt{checkpoint} ) {
207
$opt{checkpoint} = quote_names( $opt{checkpoint} );
208
eval { $dbh->do( qq{ select time_stamp, src, dest, msg
209
from $opt{checkpoint} where 1 != 1} );
212
die "Error accessing Checkpoint table ($opt{checkpoint}): $@"
216
# --- check that log_pos table exists if specified ---
217
if ( $opt{record_log_pos} ) {
218
$opt{record_log_pos} = quote_names( $opt{record_log_pos} );
220
eval { $dbh->do( qq{ select host, time_stamp, log_file, log_pos, master_host, master_log_file, master_log_pos
221
from $opt{record_log_pos} where 1 != 1} );
224
die "Error accessing log_pos table ($opt{record_log_pos}): $@"
228
# --- get variables from database ---
229
my $sth_vars = $dbh->prepare("show variables like 'datadir'");
231
while ( my ($var,$value) = $sth_vars->fetchrow_array ) {
232
$mysqld_vars{ $var } = $value;
234
my $datadir = $mysqld_vars{'datadir'}
235
|| die "datadir not in mysqld variables";
236
$datadir= $opt{chroot}.$datadir if ($opt{chroot});
240
# --- get target path ---
241
my ($tgt_dirname, $to_other_database);
242
$to_other_database=0;
243
if (defined($tgt_name) && $tgt_name =~ m:^\w+$: && @db_desc <= 1)
245
$tgt_dirname = "$datadir/$tgt_name";
246
$to_other_database=1;
248
elsif (defined($tgt_name) && ($tgt_name =~ m:/: || $tgt_name eq '.')) {
249
$tgt_dirname = $tgt_name;
251
elsif ( $opt{suffix} ) {
252
print "Using copy suffix '$opt{suffix}'\n" unless $opt{quiet};
256
$tgt_name="" if (!defined($tgt_name));
257
die "Target '$tgt_name' doesn't look like a database name or directory path.\n";
260
# --- resolve database names from regexp ---
261
if ( defined $opt{regexp} ) {
263
if ( $opt{regexp} =~ s{^/(.+)/\./(.+)/$}{$1} ) {
267
my $sth_dbs = $dbh->prepare("show databases");
269
while ( my ($db_name) = $sth_dbs->fetchrow_array ) {
270
next if $db_name =~ m/^information_schema$/i;
271
push @db_desc, { 'src' => $db_name, 't_regex' => $t_regex } if ( $db_name =~ m/$opt{regexp}/o );
275
# --- get list of tables and views to hotcopy ---
279
my $hc_base_tables = "";
281
my $num_base_tables = 0;
286
foreach my $rdb ( @db_desc ) {
287
my $db = $rdb->{src};
288
my @dbh_base_tables = get_list_of_tables( $db );
289
my @dbh_views = get_list_of_views( $db );
291
## filter out certain system non-lockable tables.
292
## keep in sync with mysqldump.
293
if ($db =~ m/^mysql$/i)
295
@dbh_base_tables = grep
296
{ !/^(apply_status|schema|general_log|slow_log)$/ } @dbh_base_tables
299
## generate regex for tables/files
302
if ($rdb->{t_regex}) {
303
$t_regex = $rdb->{t_regex}; ## assign temporary regex
304
$negated = $t_regex =~ s/^~//; ## note and remove negation operator
306
$t_regex = qr/$t_regex/; ## make regex string from
309
## filter (out) tables specified in t_regex
310
print "Filtering tables with '$t_regex'\n" if $opt{debug};
311
@dbh_base_tables = ( $negated
312
? grep { $_ !~ $t_regex } @dbh_base_tables
313
: grep { $_ =~ $t_regex } @dbh_base_tables );
315
## filter (out) views specified in t_regex
316
print "Filtering tables with '$t_regex'\n" if $opt{debug};
317
@dbh_views = ( $negated
318
? grep { $_ !~ $t_regex } @dbh_views
319
: grep { $_ =~ $t_regex } @dbh_views );
322
## Now concatenate the base table and view arrays.
323
my @dbh_tables = (@dbh_base_tables, @dbh_views);
325
## get list of files to copy
326
my $db_dir = "$datadir/$db";
327
opendir(DBDIR, $db_dir )
328
or die "Cannot open dir '$db_dir': $!";
332
while ( defined( my $name = readdir DBDIR ) ) {
333
$db_files{$name} = $1 if ( $name =~ /(.+)\.\w+$/ );
337
unless( keys %db_files ) {
338
warn "'$db' is an empty database\n";
341
## filter (out) files specified in t_regex
343
if ($rdb->{t_regex}) {
344
@db_files = ($negated
345
? grep { $db_files{$_} !~ $t_regex } keys %db_files
346
: grep { $db_files{$_} =~ $t_regex } keys %db_files );
349
@db_files = keys %db_files;
352
@db_files = sort @db_files;
356
## remove indices unless we're told to keep them
357
if ($opt{noindices}) {
358
@index_files= grep { /\.(ISM|MYI)$/ } @db_files;
359
@db_files = grep { not /\.(ISM|MYI)$/ } @db_files;
362
$rdb->{files} = [ @db_files ];
363
$rdb->{index} = [ @index_files ];
364
my @hc_base_tables = map { quote_names("$db.$_") } @dbh_base_tables;
365
my @hc_views = map { quote_names("$db.$_") } @dbh_views;
367
my @hc_tables = (@hc_base_tables, @hc_views);
368
$rdb->{tables} = [ @hc_tables ];
370
$hc_locks .= ", " if ( length $hc_locks && @hc_tables );
371
$hc_locks .= join ", ", map { "$_ READ" } @hc_tables;
373
$hc_base_tables .= ", " if ( length $hc_base_tables && @hc_base_tables );
374
$hc_base_tables .= join ", ", @hc_base_tables;
375
$hc_views .= ", " if ( length $hc_views && @hc_views );
376
$hc_views .= join " READ, ", @hc_views;
378
@hc_tables = (@hc_base_tables, @hc_views);
380
$num_base_tables += scalar @hc_base_tables;
381
$num_views += scalar @hc_views;
382
$num_tables += $num_base_tables + $num_views;
383
$num_files += scalar @{$rdb->{files}};
386
# --- resolve targets for copies ---
388
if (defined($tgt_name) && length $tgt_name ) {
389
# explicit destination directory specified
391
# GNU `cp -r` error message
392
die "copying multiple databases, but last argument ($tgt_dirname) is not a directory\n"
393
if ( @db_desc > 1 && !(-e $tgt_dirname && -d $tgt_dirname ) );
395
if ($to_other_database)
397
foreach my $rdb ( @db_desc ) {
398
$rdb->{target} = "$tgt_dirname";
401
elsif ($opt{method} =~ /^scp\b/)
402
{ # we have to trust scp to hit the target
403
foreach my $rdb ( @db_desc ) {
404
$rdb->{target} = "$tgt_dirname/$rdb->{src}";
409
die "Last argument ($tgt_dirname) is not a directory\n"
410
if (!(-e $tgt_dirname && -d $tgt_dirname ) );
411
foreach my $rdb ( @db_desc ) {
412
$rdb->{target} = "$tgt_dirname/$rdb->{src}";
417
die "Error: expected \$opt{suffix} to exist" unless ( exists $opt{suffix} );
419
foreach my $rdb ( @db_desc ) {
420
$rdb->{target} = "$datadir/$rdb->{src}$opt{suffix}";
424
print Dumper( \@db_desc ) if ( $opt{debug} );
426
# --- bail out if all specified databases are empty ---
428
die "No tables to hot-copy" unless ( length $hc_locks );
430
# --- create target directories if we are using 'cp' ---
434
if ($opt{method} =~ /^cp\b/)
436
foreach my $rdb ( @db_desc ) {
437
push @existing, $rdb->{target} if ( -d $rdb->{target} );
440
if ( @existing && !($opt{allowold} || $opt{addtodest}) )
443
die "Can't hotcopy to '", join( "','", @existing ), "' because directory\nalready exist and the --allowold or --addtodest options were not given.\n"
447
retire_directory( @existing ) if @existing && !$opt{addtodest};
449
foreach my $rdb ( @db_desc ) {
450
my $tgt_dirpath = "$rdb->{target}";
451
# Remove trailing slashes (needed for Mac OS X)
452
substr($tgt_dirpath, 1) =~ s|/+$||;
453
if ( $opt{dryrun} ) {
454
print "mkdir $tgt_dirpath, 0750\n";
456
elsif ($opt{method} =~ /^scp\b/) {
457
## assume it's there?
461
mkdir($tgt_dirpath, 0750) or die "Can't create '$tgt_dirpath': $!\n"
462
unless -d $tgt_dirpath;
463
my @f_info= stat "$datadir/$rdb->{src}";
464
chown $f_info[4], $f_info[5], $tgt_dirpath;
468
##############################
469
# --- PERFORM THE HOT-COPY ---
471
# Note that we try to keep the time between the LOCK and the UNLOCK
472
# as short as possible, and only start when we know that we should
473
# be able to complete without error.
475
# read lock all the tables we'll be copying
476
# in order to get a consistent snapshot of the database
478
if ( $opt{checkpoint} || $opt{record_log_pos} ) {
479
# convert existing READ lock on checkpoint and/or log_pos table into WRITE lock
480
foreach my $table ( grep { defined } ( $opt{checkpoint}, $opt{record_log_pos} ) ) {
481
$hc_locks .= ", $table WRITE"
482
unless ( $hc_locks =~ s/$table\s+READ/$table WRITE/ );
486
my $hc_started = time; # count from time lock is granted
488
if ( $opt{dryrun} ) {
489
if ( $opt{old_server} ) {
490
print "LOCK TABLES $hc_locks\n";
491
print "FLUSH TABLES /*!32323 $hc_tables */\n";
494
# Lock base tables and views separately.
495
print "FLUSH TABLES $hc_base_tables WITH READ LOCK\n"
496
if ( $hc_base_tables );
497
print "LOCK TABLES $hc_views READ\n" if ( $hc_views );
500
print "FLUSH LOGS\n" if ( $opt{flushlog} );
501
print "RESET MASTER\n" if ( $opt{resetmaster} );
502
print "RESET SLAVE\n" if ( $opt{resetslave} );
506
if ( $opt{old_server} ) {
507
$dbh->do("LOCK TABLES $hc_locks");
508
printf "Locked $num_tables tables in %d seconds.\n", time-$start unless $opt{quiet};
509
$hc_started = time; # count from time lock is granted
511
# flush tables to make on-disk copy up to date
513
$dbh->do("FLUSH TABLES /*!32323 $hc_tables */");
514
printf "Flushed tables ($hc_tables) in %d seconds.\n", time-$start unless $opt{quiet};
517
# Lock base tables and views separately, as 'FLUSH TABLES <tbl_name>
518
# ... WITH READ LOCK' (introduced in 5.5) would fail for views.
519
# Also, flush tables to make on-disk copy up to date
520
$dbh->do("FLUSH TABLES $hc_base_tables WITH READ LOCK")
521
if ( $hc_base_tables );
522
printf "Flushed $num_base_tables tables with read lock ($hc_base_tables) in %d seconds.\n",
523
time-$start unless $opt{quiet};
526
$dbh->do("LOCK TABLES $hc_views READ") if ( $hc_views );
527
printf "Locked $num_views views ($hc_views) in %d seconds.\n",
528
time-$start unless $opt{quiet};
530
$hc_started = time; # count from time lock is granted
532
$dbh->do( "FLUSH LOGS" ) if ( $opt{flushlog} );
533
$dbh->do( "RESET MASTER" ) if ( $opt{resetmaster} );
534
$dbh->do( "RESET SLAVE" ) if ( $opt{resetslave} );
536
if ( $opt{record_log_pos} ) {
537
record_log_pos( $dbh, $opt{record_log_pos} );
538
$dbh->do("FLUSH TABLES /*!32323 $hc_tables */");
544
foreach my $rdb ( @db_desc )
546
my @files = map { "$datadir/$rdb->{src}/$_" } @{$rdb->{files}};
549
eval { copy_files($opt{method}, \@files, $rdb->{target}); };
550
push @failed, "$rdb->{src} -> $rdb->{target} failed: $@"
553
@files = @{$rdb->{index}};
556
copy_index($opt{method}, \@files,
557
"$datadir/$rdb->{src}", $rdb->{target} );
560
if ( $opt{checkpoint} ) {
561
my $msg = ( $@ ) ? "Failed: $@" : "Succeeded";
564
$dbh->do( qq{ insert into $opt{checkpoint} (src, dest, msg)
565
VALUES ( '$rdb->{src}', '$rdb->{target}', '$msg' )
570
warn "Failed to update checkpoint table: $@\n";
575
if ( $opt{dryrun} ) {
576
print "UNLOCK TABLES\n";
577
if ( @existing && !$opt{keepold} ) {
578
my @oldies = map { $_ . '_old' } @existing;
579
print "rm -rf @oldies\n"
585
$dbh->do("UNLOCK TABLES");
588
my $hc_dur = time - $hc_started;
589
printf "Unlocked tables.\n" unless $opt{quiet};
592
# --- HOT-COPY COMPLETE ---
593
###########################
598
# hotcopy failed - cleanup
599
# delete any @targets
600
# rename _old copy back to original
603
foreach my $rdb ( @db_desc ) {
604
push @targets, $rdb->{target} if ( -d $rdb->{target} );
606
print "Deleting @targets \n" if $opt{debug};
608
print "Deleting @targets \n" if $opt{debug};
611
print "Restoring @existing from back-up\n" if $opt{debug};
612
foreach my $dir ( @existing ) {
613
rename("${dir}_old", $dir )
614
or warn "Can't rename ${dir}_old to $dir: $!\n";
618
die join( "\n", @failed );
622
# delete _old unless $opt{keepold}
624
if ( @existing && !$opt{keepold} ) {
625
my @oldies = map { $_ . '_old' } @existing;
626
print "Deleting previous copy in @oldies\n" if $opt{debug};
630
printf "$0 copied %d tables (%d files) in %d second%s (%d seconds overall).\n",
631
$num_tables, $num_files,
632
$hc_dur, ($hc_dur==1)?"":"s", time - $start_time
642
my ($method, $files, $target) = @_;
644
print "Copying ".@$files." files...\n" unless $opt{quiet};
646
if ($method =~ /^s?cp\b/) # cp or scp with optional flags
649
# add option to preserve mod time etc of copied files
650
# not critical, but nice to have
651
$cp.= " -p" if $^O =~ m/^(solaris|linux|freebsd|darwin)$/;
653
# add recursive option for scp
654
$cp.= " -r" if $^O =~ /m^(solaris|linux|freebsd|darwin)$/ && $method =~ /^scp\b/;
656
# perform the actual copy
657
safe_system( $cp, (map { "'$_'" } @$files), "'$target'" );
661
die "Can't use unsupported method '$method'\n";
666
# Copy only the header of the index file
671
my ($method, $files, $source, $target) = @_;
673
print "Copying indices for ".@$files." files...\n" unless $opt{quiet};
674
foreach my $file (@$files)
676
my $from="$source/$file";
677
my $to="$target/$file";
679
open(INPUT, "<$from") || die "Can't open file $from: $!\n";
680
binmode(INPUT, ":raw");
681
my $length=read INPUT, $buff, 2048;
682
die "Can't read index header from $from\n" if ($length < 1024);
687
print "$opt{method}-header $from $to\n";
689
elsif ($opt{method} eq 'cp')
691
open(OUTPUT,">$to") || die "Can\'t create file $to: $!\n";
692
if (syswrite(OUTPUT,$buff) != length($buff))
694
die "Error when writing data to $to: $!\n";
696
close OUTPUT || die "Error on close of $to: $!\n";
698
elsif ($opt{method} =~ /^scp\b/)
700
my ($fh, $tmp)= tempfile('mysqlhotcopy-XXXXXX', DIR => $opt_tmpdir) or
701
die "Can\'t create/open file in $opt_tmpdir\n";
702
if (syswrite($fh,$buff) != length($buff))
704
die "Error when writing data to $tmp: $!\n";
706
close $fh || die "Error on close of $tmp: $!\n";
707
safe_system("$opt{method} $tmp $to");
712
die "Can't use unsupported method '$opt{method}'\n";
720
my $method= shift @sources;
721
my $target= pop @sources;
722
## @sources = list of source file names
724
## We have to deal with very long command lines, otherwise they may generate
725
## "Argument list too long".
726
## With 10000 tables the command line can be around 1MB, much more than 128kB
727
## which is the common limit on Linux (can be read from
728
## /usr/src/linux/include/linux/binfmts.h
729
## see http://www.linuxjournal.com/article.php?sid=6060).
731
my $chunk_limit= 100 * 1024; # 100 kB
736
$chunk_length+= length($_);
737
if ($chunk_length > $chunk_limit) {
738
safe_simple_system($method, @chunk, $target);
743
if ($chunk_length > 0) { # do not forget last small chunk
744
safe_simple_system($method, @chunk, $target);
748
sub safe_simple_system {
751
if ( $opt{dryrun} ) {
755
## for some reason system fails but backticks works ok for scp...
756
print "Executing '@cmd'\n" if $opt{debug};
757
my $cp_status = system "@cmd > /dev/null";
758
if ($cp_status != 0) {
759
warn "Executing command failed ($cp_status). Trying backtick execution...\n";
760
## try something else
761
`@cmd` || die "Error: @cmd failed ($?) while copying files.\n";
766
sub retire_directory {
769
foreach my $dir ( @dir ) {
770
my $tgt_oldpath = $dir . '_old';
771
if ( $opt{dryrun} ) {
772
print "rmtree $tgt_oldpath\n" if ( -d $tgt_oldpath );
773
print "rename $dir, $tgt_oldpath\n";
777
if ( -d $tgt_oldpath ) {
778
print "Deleting previous 'old' hotcopy directory ('$tgt_oldpath')\n" unless $opt{quiet};
779
rmtree([$tgt_oldpath],0,1);
781
rename($dir, $tgt_oldpath)
782
or die "Can't rename $dir=>$tgt_oldpath: $!\n";
783
print "Existing hotcopy directory renamed to '$tgt_oldpath'\n" unless $opt{quiet};
788
my ( $dbh, $table_name ) = @_;
791
my ($file,$position) = get_row( $dbh, "show master status" );
792
die "master status is undefined" if !defined $file || !defined $position;
794
my $row_hash = get_row_hash( $dbh, "show slave status" );
795
my ($master_host, $log_file, $log_pos );
796
if ( $dbh->{mysql_serverinfo} =~ /^3\.23/ ) {
797
($master_host, $log_file, $log_pos )
798
= @{$row_hash}{ qw / Master_Host Log_File Pos / };
800
($master_host, $log_file, $log_pos )
801
= @{$row_hash}{ qw / Master_Host Relay_Master_Log_File Exec_Master_Log_Pos / };
803
my $hostname = hostname();
805
$dbh->do( qq{ replace into $table_name
806
set host=?, log_file=?, log_pos=?,
807
master_host=?, master_log_file=?, master_log_pos=? },
809
$hostname, $file, $position,
810
$master_host, $log_file, $log_pos );
815
warn "Failed to store master position: $@\n";
820
my ( $dbh, $sql ) = @_;
822
my $sth = $dbh->prepare($sql);
824
return $sth->fetchrow_array();
828
my ( $dbh, $sql ) = @_;
830
my $sth = $dbh->prepare($sql);
832
return $sth->fetchrow_hashref();
835
sub get_list_of_tables {
840
$dbh->selectall_arrayref('SHOW FULL TABLES FROM ' .
841
$dbh->quote_identifier($db) .
842
' WHERE Table_type = \'BASE TABLE\'')
844
warn "Unable to retrieve list of tables in $db: $@" if $@;
846
return (map { $_->[0] } @$tables);
849
sub get_list_of_views {
854
$dbh->selectall_arrayref('SHOW FULL TABLES FROM ' .
855
$dbh->quote_identifier($db) .
856
' WHERE Table_type = \'VIEW\'')
858
warn "Unable to retrieve list of views in $db: $@" if $@;
860
return (map { $_->[0] } @$views);
865
# given a db.table name, add quotes
867
my ($db, $table, @cruft) = split( /\./, $name );
868
die "Invalid db.table name '$name'" if (@cruft || !defined $db || !defined $table );
870
# Earlier versions of DBD return table name non-quoted,
871
# such as DBD-2.1012 and the newer ones, such as DBD-2.9002
872
# returns it quoted. Let's have a support for both.
874
return "`$db`.`$table`";
881
mysqlhotcopy is designed to make stable copies of live MySQL databases.
883
Here "live" means that the database server is running and the database
884
may be in active use. And "stable" means that the copy will not have
885
any corruptions that could occur if the table files were simply copied
886
without first being locked and flushed from within the server.
892
=item --checkpoint checkpoint-table
894
As each database is copied, an entry is written to the specified
895
checkpoint-table. This has the happy side-effect of updating the
896
MySQL update-log (if it is switched on) giving a good indication of
897
where roll-forward should begin for backup+rollforward schemes.
899
The name of the checkpoint table should be supplied in database.table format.
900
The checkpoint-table must contain at least the following fields:
904
time_stamp timestamp not null
911
=item --record_log_pos log-pos-table
913
Just before the database files are copied, update the record in the
914
log-pos-table from the values returned from "show master status" and
915
"show slave status". The master status values are stored in the
916
log_file and log_pos columns, and establish the position in the binary
917
logs that any slaves of this host should adopt if initialised from
918
this dump. The slave status values are stored in master_host,
919
master_log_file, and master_log_pos, corresponding to the coordinates
920
of the next to the last event the slave has executed. The slave or its
921
siblings can connect to the master next time and request replication
922
starting from the recorded values.
924
The name of the log-pos table should be supplied in database.table format.
925
A sample log-pos table definition:
929
CREATE TABLE log_pos (
930
host varchar(60) NOT null,
931
time_stamp timestamp(14) NOT NULL,
932
log_file varchar(32) default NULL,
933
log_pos int(11) default NULL,
934
master_host varchar(60) NULL,
935
master_log_file varchar(32) NULL,
936
master_log_pos int NULL,
944
=item --suffix suffix
946
Each database is copied back into the originating datadir under
947
a new name. The new name is the original name with the suffix
950
If only a single db_name is supplied and the --suffix flag is not
951
supplied, then "--suffix=_copy" is assumed.
955
Move any existing version of the destination to a backup directory for
956
the duration of the copy. If the copy successfully completes, the backup
957
directory is deleted - unless the --keepold flag is set. If the copy fails,
958
the backup directory is restored.
960
The backup directory name is the original name with "_old" appended.
961
Any existing versions of the backup directory are deleted.
965
Behaves as for the --allowold, with the additional feature
966
of keeping the backup directory after the copy successfully completes.
970
Don't rename target directory if it already exists, just add the
971
copied files into it.
973
This is most useful when backing up a database with many large
974
tables and you don't want to have all the tables locked for the
977
In this situation, I<if> you are happy for groups of tables to be
978
backed up separately (and thus possibly not be logically consistent
979
with one another) then you can run mysqlhotcopy several times on
980
the same database each with different db_name./table_regex/.
981
All but the first should use the --addtodest option so the tables
982
all end up in the same directory.
986
Rotate the log files by executing "FLUSH LOGS" after all tables are
987
locked, and before they are copied.
991
Reset the bin-log by executing "RESET MASTER" after all tables are
992
locked, and before they are copied. Useful if you are recovering a
993
slave in a replication setup.
997
Reset the master.info by executing "RESET SLAVE" after all tables are
998
locked, and before they are copied. Useful if you are recovering a
999
server in a mutual replication setup.
1001
=item --regexp pattern
1003
Copy all databases with names matching the pattern.
1005
=item --regexp /pattern1/./pattern2/
1007
Copy all tables with names matching pattern2 from all databases with
1008
names matching pattern1. For example, to select all tables which
1009
names begin with 'bar' from all databases which names end with 'foo':
1011
mysqlhotcopy --indices --method=cp --regexp /foo$/./^bar/
1013
=item db_name./pattern/
1015
Copy only tables matching pattern. Shell metacharacters ( (, ), |, !,
1016
etc.) have to be escaped (e.g., \). For example, to select all tables
1017
in database db1 whose names begin with 'foo' or 'bar':
1019
mysqlhotcopy --indices --method=cp db1./^\(foo\|bar\)/
1021
=item db_name./~pattern/
1023
Copy only tables not matching pattern. For example, to copy tables
1024
that do not begin with foo nor bar:
1026
mysqlhotcopy --indices --method=cp db1./~^\(foo\|bar\)/
1030
Display help-screen and exit.
1034
User for database login if not current user.
1036
=item -p, --password=#
1038
Password to use when connecting to the server. Note that you are strongly
1039
encouraged *not* to use this option as every user would be able to see the
1040
password in the process list. Instead use the '[mysqlhotcopy]' section in
1041
one of the config files, normally /etc/my.cnf or your personal ~/.my.cnf.
1042
(See the chapter 'my.cnf Option Files' in the manual.)
1044
=item -h, -h, --host=#
1046
Hostname for local server when connecting over TCP/IP. By specifying this
1047
different from 'localhost' will trigger mysqlhotcopy to use TCP/IP connection.
1051
Port to use when connecting to MySQL server with TCP/IP. This is only used
1052
when using the --host option.
1054
=item -S, --socket=#
1056
UNIX domain socket to use when connecting to local server.
1060
Use old server (pre v5.5) commands.
1064
Don\'t include index files in copy. Only up to the first 2048 bytes
1065
are copied; You can restore the indexes with isamchk -r or myisamchk -r
1070
Method for copy (only "cp" currently supported). Alpha support for
1071
"scp" was added in November 2000. Your experience with the scp method
1072
will vary with your ability to understand how scp works. 'man scp'
1073
and 'man ssh' are your friends.
1075
The destination directory _must exist_ on the target machine using the
1076
scp method. --keepold and --allowold are meaningless with scp.
1077
Liberal use of the --debug option will help you figure out what\'s
1078
really going on when you do an scp.
1080
Note that using scp will lock your tables for a _long_ time unless
1081
your network connection is _fast_. If this is unacceptable to you,
1082
use the 'cp' method to copy the tables to some temporary area and then
1083
scp or rsync the files at your leisure.
1087
Be silent except for errors.
1091
Debug messages are displayed.
1095
Display commands without actually doing them.
1101
This software is free and comes without warranty of any kind. You
1102
should never trust backup software without studying the code yourself.
1103
Study the code inside this script and only rely on it if I<you> believe
1104
that it does the right thing for you.
1106
Patches adding bug fixes, documentation and new features are welcome.
1107
Please send these to internals@lists.mysql.com.
1111
Extend the individual table copy to allow multiple subsets of tables
1112
to be specified on the command line:
1114
mysqlhotcopy db newdb t1 t2 /^foo_/ : t3 /^bar_/ : +
1116
where ":" delimits the subsets, the /^foo_/ indicates all tables
1117
with names beginning with "foo_" and the "+" indicates all tables
1118
not copied by the previous subsets.
1120
'newdb' is either the name of the new database, or the full path name
1121
of the new database file. The database should not already exist.
1123
Add option to lock each table in turn for people who don\'t need
1124
cross-table integrity.
1126
Add option to FLUSH STATUS just before UNLOCK TABLES.
1128
Add support for other copy methods (e.g., tar to single file?).
1130
Add support for forthcoming MySQL ``RAID'' table subdirectory layouts.
1136
Martin Waite - Added checkpoint, flushlog, regexp and dryrun options.
1137
Fixed cleanup of targets when hotcopy fails.
1138
Added --record_log_pos.
1139
RAID tables are now copied (don't know if this works over scp).
1141
Ralph Corderoy - Added synonyms for commands.
1143
Scott Wiersdorf - Added table regex and scp support.
1145
Monty - Working --noindex (copy only first 2048 bytes of index file).
1146
Fixes for --method=scp.
1148
Ask Bjoern Hansen - Cleanup code to fix a few bugs and enable -w again.
1150
Emil S. Hansen - Added resetslave and resetmaster.
1152
Jeremy D. Zawodny - Removed deprecated DBI calls. Fixed bug which
1153
resulted in nothing being copied when a regexp was specified but no
1156
Martin Waite - Fix to handle database name that contains space.
1158
Paul DuBois - Remove end '/' from directory names.