xtrabackup增量备份局限性

xtrabackup增量备份局限性,目前发现:
1、增删表无法增量备份
2、增删索引无法增量备份
3、增删字段无法增量备份

可能全备后的DDL都无法完成增量备份(目前只验证以上三种情况),针对xtrabackup存在这些问题,我希望用xtrabackup比较好的完成增量备份,想到两个方案:

1、在全备后,检查information_schema中COLUMNS、TABLES、STATISTICS行数变化,如果有变化,就要全备一次。
2、在全备后,解析binlog,如果有DDL操作,就要全备一次

第一方案:比较简单、但不严谨,另外的DDL可能增量备份也无法备份
第二方案:比较严谨,全备和binlog产生时间点要配合好

写了全量备份脚本:

#!/usr/bin/perl
#########################################
#author:luoxuan                         #
#purpose:backup DATABASE WITH xtrabackup#
#date:20110629                          #
#########################################
 
USE warnings;
USE strict;
USE Getopt::Std;
USE vars qw($opt_d);
getopts('d:');
my $xtrabackup = "/usr/bin/xtrabackup";
#the backup path
my $back_dir="/backup";
my $cate=lc($opt_d);
 
###########################################
# print usage
###########################################
sub print_usage {
	my $text = <<EOF;
 
 NAME:
    back_db.pl
 
 SYNTAX:
    eg:
    back_db.pl -d FULL|incr
 
 FUNCTION:
    backup mysql db WITH xtrabackup
 
 PARAMETER:
    FULL:FULL backup DATABASE;
    incr:INCREMENT backup databas;
 
EOF
	print STDERR $text;
	exit 0;
}
 
#
my $len_db = rindex $cate."\$","\$";
IF($len_db==0){
	&print_usage;
}
 
#path EXISTS
unless(-e $back_dir){
	system("mkdir $back_dir");
}
 
#backup DATABASE ON slave AND GET port
my $is_slave=readpipe("$mysql -uroot -e \"show variables like 'read_only'\"|grep read_only|awk '{print \$2}'");
chomp($is_slave);
IF($is_slave eq "OFF"){
	print "This is master,i hope you can backup db on slave!thank you!\n";
	exit 0;
}
 
my $port=readpipe("$mysql -uroot -e \"show variables like 'port'\"|grep port|awk '{print \$2}'");
chomp($port);
 
#mysqldump ALL DATABASES OR single DATABASE
 
IF($db_name eq "all"){
	$option_db="--all-databases";
}
ELSE{
#check db exist
	my @userdb=readpipe("$mysql -uroot -e \"show databases\"|grep -v 'test\\|Database\\|information_schema\\|mysql'");
	foreach my $my_db(@userdb){
		chomp($my_db);
		$my_db.="|";
		$put_db.=$my_db; 
	}
	my @backup_db=split(/,/,$db_name);
	foreach my $single_db(@backup_db){
		$single_db=~s/^\s*|\s*$//g;
		IF($put_db=~$single_db){
			chomp($single_db);
			$single_db.=" ";
			$option_db.=$single_db;
		}
		ELSE{
			print "The db which you want to backup doesn\'t exists!!!\n";
			&print_usage;
		}
	}
}
 
sub dump_db{
	@tmpbackup=@_;
	@rbackup=@_;
	IF(shift @tmpbackup eq "all"){    
		my $issucc = system("$mysqldump -uroot -P$port --single-transaction -q -e @rbackup>$back_file");
		IF ($issucc==0){
			print "the backup is successful!\n";
		}
	}
	ELSE{
		my $issucc = system("$mysqldump -uroot -P$port --single-transaction -q -e --databases @rbackup>$back_file");
		IF ($issucc==0){
			print "the backup is successful!\n";
		}
	}   
}
 
eval{&dump_db($option_db);}

2 Responses to “xtrabackup增量备份局限性”

  1. admin说道:

    接下来,如果有需求(更多的机器切换),可以修改代码,支持更多的切换(循环方式),上面写得复杂了。

  2. lyflyd说道:

    楼主,xtrabackup增量备份局限性,使用innobackupex 都可以解决

Post a Comment