備份策略：
    每週一(或第一次)全備，其他增備
    使用xbstream方式進行lbzip2壓縮

改進:支援遠端備份

使用
檢視幫助資訊：perl bbq_innobackupex.pl -I 或 perl bbq_innobackupex.pl --help
            引數預設值:使用者名稱、密碼為:ubak, 埠:3306, my.cnf路徑為/etc/my.cnf
    例如1:備份輸出路徑為/data/bak，其他使用預設值
        perl bbq_innobackupex.pl -O=/data/bak

    備份檔格式：例如25號全備，26號在25基礎上增備，27在26號增備
        # 25號
        base-20140125-110900_extra
        base-20140125-110900.xb.bz2
        # 26號
        inc-20140126-112537_extra
        inc-20140126-112537_base-20140125-110900.xb.bz2
        # 27號
        inc-20140127-112654_extra
        inc-20140127-112654_inc-20140126-112537.xb.bz2

參考:
http://blog.chinaunix.net/uid-26364035-id-3845609.html

原始碼如下

點選(此處)摺疊或開啟

#!/usr/bin/perl -w

use strict;
use Getopt::Long qw(:config no_ignore_case); # use GetOption function

# print to screen
my $LOG_OUT = *STDOUT;

# 定義周幾全備。預設週一
my $FULLBAK_WEEKDAY = '1';

# bak file fix
my $FIX_SP = '_'; # spliter char
my $BAKDIRFIX_BASE = 'base-';
my $BAKDIRFIX_INC = 'inc-';
my $BAKDIRFIX_EXTRA = '_extra';

# mysql config
my $mysql_host = qq(127.0.0.1); # only backup local
my $mysql_cnf = qq(/etc/my.cnf);
my $mysql_user = qq{ubak};
my $mysql_pwd = qq{ubak};
my $mysql_port = qq{3306};
my $output_path = `pwd`;
chomp($output_path);

# log file
my $BAK_LOGFILE = qq{$output_path/back.log};
if (!(-e $BAK_LOGFILE)){
`touch $BAK_LOGFILE`;
}

# ----------------------------------------------------
# handle Ctrl+C
sub catch_zap {
my $signame = shift;
print "\nExit Now...\n\n";
exit;
}
$SIG{INT} = \&catch_zap;

# ----------------------------------------------------------------------------------------
# Main()
# ----------------------------------------------------------------------------------------
sub main{
# get input params
&get_option();

&do_innobackupex($output_path);
}

&main();

# ----------------------------------------------------------------------------------------
# Func : get input params
# %2c-an-Introduction
# ----------------------------------------------------------------------------------------
sub get_option{
GetOptions('I|help'=> \&help_print,
'u|user=s' => \$mysql_user,
'p|password=s' => \$mysql_pwd,
'P|port=i' => \$mysql_port,
'F|defaults-file=s' => \$mysql_cnf,
'O|outputdir=s' => \$output_path
) or help_print();

&pprint("cnf=$mysql_cnf\n"."user=$mysql_user, pwd=$mysql_pwd ,port=$mysql_port\n"."output=$output_path");
}

# ----------------------------------------------------------------------------------------
# Func : use innobackupex
# monday full,other incre
# note:my.cnf must set datadir
# [xtrabackup]
# datadir = innodb_data_home_idr
# ----------------------------------------------------------------------------------------
sub do_innobackupex{
#
my $m_bak_sh = &get_innobak_sh();

#
if ($m_bak_sh ne ''){
# exe shell
my $start_dt = `date`;
chomp($start_dt);
&pprint("start:$start_dt");

&pprint("shell:$m_bak_sh");
my $res = `$m_bak_sh`;

my $end_dt = `date`;
chomp($end_dt);
&pprint("end: $start_dt ----- $end_dt");
}
else{
&pprint("Error:canot get bak shell");
}
}

# ----------------------------------------------------------------------------------------
# Func : get innobackupex shell str
# -------------------
# full back
# innobackupex --user=xx --password=xx --defaults-file=xx \
# --no-timestamp --sleep=5 --slave-info --throttle=N \
# --stream=xbstream --extra-lsndir=xx backdir 2>>log \
# | lbzip2 -kv -n 10 > $backdir.xb.bz2 2>>log
#
# increment back
# innobackupex --host=xx --user=xx --password=xx --defaults-file=xx \
# --no-timestamp --sleep=5 --slave-info --throttle=N \
# --stream=xbstream --extra-lsndir=xx \
# --incremental-base=fullback --increment incbackdir 2>>log \
# | lbzip2 -kv -n 10 > $incbackdir.xb.bz2 2>>log
#
# -------------------
# Restore backup
# 1>uncompress
# 1) 解壓lbzip2壓縮檔案
# param:-d uncompress ; -n compress thread num; -k keep src file; -v display compress information
# lbzip2 -dkv -n 10 'filepath/fname.xb.bz2'
# 2) 解壓xbstream壓縮檔案
# param : -x, --extract ; -C output dir
# xbstream -x < 'filepath/fname.xb' -C 'outputdir'
# 2> apply
# note:my.cnf must set datadir path
# innobackupex --apply-log --redo-only bak_base_dir
# innobackupex --apply-log --redo-only bak_base_dir --incremental-dir=bak_incre1_dir
# innobackupex --apply-log --redo-only bak_base_dir --incremental-dir=bak_incre2_dir
# innobackupex --defaults-file=/etc/my.cnf --copy-back bak_base_dir
# ----------------------------------------------------------------------------------------
sub get_innobak_sh{
#
my ($m_output_path) = $output_path;
my $m_today_str = `date '+%Y%m%d-%H%M%S'`;
chomp($m_today_str);

# -------- param
my $inno_shell = qq{innobackupex --host=$mysql_host --user=$mysql_user --password=$mysql_pwd};
$inno_shell .= qq{ --defaults-file=$mysql_cnf};
$inno_shell .= qq{ --no-timestamp}; # not use timestamp filename
$inno_shell .= qq{ --sleep=5}; # copy 1M data sleep N micSeconds
$inno_shell .= qq{ --slave-info}; # record master binlog info
$inno_shell .= qq{ --throttle=200}; # limit backup used io

# -------- bak file
# day of week(0..6),0 is sunday
my $m_bakfile = '';
my $m_bakfile_extra = '';
my $m_weekday = `date '+%w'`;
# monday do full backup
if ($m_weekday == $FULLBAK_WEEKDAY) {
$m_bakfile = qq{$m_output_path/$BAKDIRFIX_BASE$m_today_str};
$m_bakfile_extra = qq{$m_bakfile$BAKDIRFIX_EXTRA};
# other do increment backup
} else{
# check back base(yesterday)
my $m_base_datestr = `date --date="-1 day" '+%Y%m%d'`;
chomp($m_base_datestr);
my $m_base_num = `ls $m_output_path | grep -i "$m_base_datestr\.\*$BAKDIRFIX_EXTRA" | wc -l`;
#my $str = qq{ls $m_output_path | grep -i "$m_base_datestr\.\*$BAKDIRFIX_EXTRA" | wc -l};
#my $m_base_num = `$str`;
chomp($m_base_num);
# first day, do all backup
if ($m_base_num == 0) {
&pprint(" do base back");
$m_bakfile = qq{$m_output_path/$BAKDIRFIX_BASE$m_today_str};
$m_bakfile_extra = qq{$m_bakfile$BAKDIRFIX_EXTRA};
}
# do increment backup
elsif ($m_base_num == 1){
# base file name
my $m_base_fname = `ls $m_output_path | grep -i "$m_base_datestr\.\*$BAKDIRFIX_EXTRA" `;
chomp($m_base_fname);
my $m_base_file = qq{$m_output_path/$m_base_fname};
$inno_shell .= qq{ --incremental-basedir=$m_base_fname --incremental };

# bakfile: sub $BAKDIRFIX_EXTRA
my $m_base_fix = `echo $m_base_fname | awk -F$BAKDIRFIX_EXTRA '{print \$1}'`;
chomp($m_base_fix);
$m_bakfile = qq{$m_output_path/$BAKDIRFIX_INC$m_today_str$FIX_SP$m_base_fix};
$m_bakfile_extra = qq{$m_output_path/$BAKDIRFIX_INC$m_today_str$BAKDIRFIX_EXTRA};
}
# many backup
else{
&pprint("Error:$m_output_path,yesterday $m_base_datestr have $m_base_num backup file!");
exit 1;
}
}

# xbstream
mkdir $m_bakfile_extra;
$inno_shell .= qq{ --stream=xbstream --extra-lsndir="$m_bakfile_extra"};

$inno_shell .= qq{ $m_bakfile};

# -------- log
$inno_shell .= qq{ 2>>"$BAK_LOGFILE"};

# -------- compress
# 直接備份後，透過管道直接壓縮，這樣把原約3次的IO，減少為一次
# xbstream
# lbzip2:
# -n compress thread num
# -k keep src file
# -v display compress information
$inno_shell .= qq{ | lbzip2 -kv -n 10 > $m_bakfile.xb.bz2 2>>$BAK_LOGFILE};

# -------- remote
#

return $inno_shell;
}

# ----------------------------------------------------------------------------------------
# Func : print help information
# ----------------------------------------------------------------------------------------
sub help_print{
print < =========================================================================================
Info :
Created By babaoqi
params:
'I|help', print help information
'u|user=s', Mysql user. defautl:ubak
'p|password=s', Mysql password. defautl:ubak
'P|port=i', Mysql port. defautl:3306
'F|defaults-file=s', my.cnf path.default:/etc/my.cnf
'O|output=s' backup file output path. default:cur path
sample:
perl bbq_innobackupex.pl -O=/data/bak
=========================================================================================
EOF
exit ;
}

# ----------------------------------------------------------------------------------------
# Func : print log
# ----------------------------------------------------------------------------------------
sub pprint{
my $pre = " ########################### ";
print "$pre $pre \n";
print "$pre @_ \n";
print "$pre $pre \n";
}

innobackupex備份指令碼

相關文章