背景:
使用perl脚本,通过交互界面实现DM8联机物理备份。
实现思路:
1、联机物理备份常用的备份类型包括库备份、表空间备份,备份方式包括全备和增备,功能之一便是考虑如何根据用户选择来确定哪种备份类型和备份方式。
2、功能之二便是备份路径的选择,为方便管理,需要提前设定合理的备份路径。
3、备份命令的执行可通过at调用shell脚本的形式来实现。
脚本内容:
#!/usr/bin/perl
#使用方式:
#1.将该脚本放置到dmdba所属目录下,并对文件进行格式转换:sed -i 's/\r$//' filename
#2.赋予执行权限;chmod 777 filename
#3.使用root权限调用脚本
use strict;
#执行清屏命令
system "clear";
use Time::Local;
#联机备份登录信息:
my %para_login = (
"user" => "数据库访问账号[default: SYSDBA]",
"passwd" => "数据库访问密码[default: SYSDBA]"
);
my @para_login_keys=(
"user",
"passwd"
);
#-----------------------------------------
#自定义系统时间格式:$now = 年月日时分
#-----------------------------------------
use POSIX qw(strftime);
my $now;
my $now_2;
my $now_3;
my $year;
my $mon;
my $hour;
my $sec;
my $min;
&now;
sub now{
$now=strftime("%Y%m%d%H%M", localtime(time));
$now_2=strftime("%Y-%m-%d %H:%M", localtime(time));
$now_3=strftime("%Y-%m-%d %H:%M:%S", localtime(time));
}
#-----------------------------------------
#确定本机DM8安装bin目录:$install_path_bin
#-----------------------------------------
my @path_locate = `locate disql`;
my $install_path_bin = '';
my $install_path = '';
foreach(@path_locate){
if(/.*\/bin\/disql\n/){
s/\/disql//g;
chomp($install_path_bin = $_); #bin目录
s/\/bin//g;
chomp($install_path = $_); #安装目录
}
}
#-----------------------------------------
#创建文件夹:用于存放AT和crontab定时调度的执行文件
#-----------------------------------------
my $install_path_bak_exc_file = "$install_path/bak_exc_file";
if(-e $install_path_bak_exc_file){
print "\n";
}else{
mkdir( $install_path_bak_exc_file ) or die "无法创建 $install_path_bak_exc_file 目录, $!";
}
#-----------------------------------------
#将DM8自带的动态库放到系统动态库目录,这样便可随处调用disql命令
#-----------------------------------------
my $libcrypto = "/usr/lib64/libcrypto.so";
if(-e $libcrypto){
print "\n";
}else{
copy("$install_path_bin/libcrypto.so","/usr/lin64") or die "copy $install_path_bin/libcrypto.so to /usr/lin64 failed";
}
print ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>备份方式选择及服务确认<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n";
#-----------------------------------------------------------------------
#通过用户输入的端口号来识别待备份库库名
#-----------------------------------------------------------------------
my $port;
my $PID_info;
my $DB_NAME;
my $PID;
my $user_name; #登陆用户名
my $user_passwd; #登录密码
print "\n请输入待备份数据库的端口号>>";
chomp($port = <STDIN>);
my $DB_info=`ps -L $PID | head -2`;
$DB_info =~ /\/([a-zA-Z0-9]+)\/dm.ini/;
$DB_NAME = $1;
print "\n\n\n>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>备份参数确认<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<\n";
#----------------------------------------------------
#联机物理备份所需参数值:"参数名"=>"值"
#----------------------------------------------------
my @para_online_keys=(
"backup_raise",
"backup_type",
"backup_dir",
"base_on_backup_dir"
);
#----------------------------------------------------
#接受、处理所需参数值:"参数名"=>"值"
#----------------------------------------------------
my %online;
my $backup_dir; #备份路径
my $backup_type; #备份类型
my $base_on_backup_dir; #基备份集路径
my $backup_raise; #备份级别
my $name;
my $backup_mode;
&deal_user_input_para_online;
&show_all_para_info(@para_online_keys);
foreach $key(@para_offline_keys){
if($key eq "backup_type"){
#备份类型
print "\n备份类型(backup_type)[0-全备,1-差量增备,2-累积增备;default:0]>>";
chomp($para_offline{$key} = <STDIN>);
if($para_offline{$key} == 0 || $para_offline{$key} eq ""){
$para_offline{$key} = "full";
}
elsif($para_offline{$key} ==1){
$para_offline{$key} = "INCREMENT";
}else{
$para_offline{$key} = "INCREMENT CUMULATIVE";
}
}
elsif($key eq "backup_dir"){
if($para_offline{'backup_type'} eq "full"){
print "\n备份家目录(backup_dir)[default: $install_path/bak/$DB_NAME\_offline]>>";
chomp($para_offline{$key} = <STDIN>);
if($para_offline{$key} eq ""){
$para_offline{$key} = "$install_path/bak/$DB_NAME\_offline/$DB_NAME\_full_";
}else{
$para_offline{$key} = "$para_offline{$key}/$DB_NAME\_full_";
}
}else{
print "备份家目录(backup_dir)[default: $install_path/bak/$DB_NAME\_offline]>>";
chomp($para_offline{$key} = <STDIN>);
if($para_offline{$key} eq ""){
$para_offline{$key} = "$install_path/bak/$DB_NAME\_offline/$DB_NAME\_inrc_";
}else{
$para_offline{$key} = "$para_offline{$key}/$DB_NAME\_inrc_";
}
}
}
elsif($key eq "base_on_backup_dir"){
if($para_offline{"backup_type"} ne "full"){
print "基备份集搜索目录(base_on_backup_dir)[default: $install_path/bak/$DB_NAME\_offline]>>";
chomp($para_offline{$key} = <STDIN>);
if($para_offline{$key} eq ""){
$para_offline{$key} = "$install_path/bak/$DB_NAME\_offline";
}
}else{
pop @para_offline_keys;
}
}
}
}
%dexp = map{$_, get_user_input_para_dexp($_)}@para_dexp_keys;
foreach $key(@para_dexp_keys){
if($key eq "backup_raise"){
if($dexp{$key} == 0 || $dexp{$key} eq ""){
$dexp{$key} = "schemas";
}
elsif($dexp{$key} == 1){
$dexp{$key} = "owner";
}
elsif($dexp{$key} == 2){
$dexp{$key} = "full";
}
elsif($dexp{$key} == 3){
$dexp{$key} = "tables";
}
}
elsif($dexp{$key} eq "" && $key eq "backup_dir"){
$dexp{$key} = "/dm/bak/$DB_NAME\_dexp/$dexp{\"backup_raise\"}";
}
elsif($dexp{$key} eq "" && $key eq "backup_name"){
$dexp{$key} = "$DB_NAME\_$dexp{\"backup_raise\"}\_";
}
elsif($dexp{$key} eq "" && $key eq "log_name"){
$dexp{$key} = "dexp.log";
}
}
$backup_type = "full"; #备份类型
#-----------------------------
#处理用户输入的参数值的接口--联机物理备份
#-----------------------------
sub deal_user_input_para_online{
foreach $key(@para_online_keys){
if($key eq "backup_raise"){
#备份级别
print "备份级别(backup_raise)[0-库,1-表空间;default: 0]>>";
chomp($online{$key}=<STDIN>);
if($online{$key} == 0 || $online{$key} eq ""){
$online{$key} = "database";
}
elsif($online{$key} ==1){
$online{$key} = "tablespace";
print "\n请输入想要备份的表空间名>>";
chomp($name=<STDIN>);
}
}
elsif($key eq "backup_type"){
print "备份类型(backup_type)[0-全备,1-差量增备,2-累计增备;default: 0]>>";
chomp($online{$key}=<STDIN>);
#备份类型
if($online{$key} == 0 || $online{$key} eq ""){
$online{$key} = "full";
}
elsif($online{$key} ==1){
$online{$key} = "INCREMENT";
}else{
$online{$key} = "INCREMENT CUMULATIVE";
}
}
elsif($key eq "backup_dir"){
print "备份家目录(backup_dir)[default: $install_path/bak/$DB_NAME\_online]>>";
chomp($online{$key}=<STDIN>);
if($online{$key} eq ""){
if($online{'backup_type'} eq "full"){
$online{'backup_dir'} = "$install_path/bak/$DB_NAME\_online/$online{\"backup_raise\"}/$DB_NAME\_full_";
}else{
$online{'backup_dir'} = "$install_path/bak/$DB_NAME\_online/$online{\"backup_raise\"}/$DB_NAME\_inrc_";
}
}
elsif($online{$key} ne ""){
if($online{'backup_type'} eq "full"){
$online{'backup_dir'} = "$online{'backup_dir'}/$online{\"backup_raise\"}/$DB_NAME\_full_";
}else{
$online{'backup_dir'} = "$online{'backup_dir'}/$online{\"backup_raise\"}/$DB_NAME\_inrc_";
}
}
}
elsif($key eq "base_on_backup_dir"){
print "基备份集搜索目录(base_on_backup_dir)[default: $install_path/bak/$DB_NAME\_online]>>";
chomp($online{$key}=<STDIN>);
if($online{$key} eq ""){
$online{$key} = "$install_path/bak/$DB_NAME\_online/$online{\"backup_raise\"}";
}
}
}
}
#-----------------------------
#重新给具体变量赋值
#-----------------------------
$backup_raise = "$online{'backup_raise'}"; #备份类型
$backup_type = "$online{'backup_type'}"; #备份类型
$backup_dir = "$online{'backup_dir'}"; #备份路径
$base_on_backup_dir = "$online{'base_on_backup_dir'}"; #备份路径
print $red."\n开始执行备份[y|n]>>".$end;
chomp(my $is_yes = <STDIN>);
if($is_yes eq "y"){
&exc_backup_once;
last;
}
#-----------------------------
#执行备份
#-----------------------------
sub exc_backup_once{
if($backup_type eq "full" ){
#库级和表空间级全备脚本:
open bak_sql, '>', "$install_path_bak_exc_file/AT_$now.sql";
print bak_sql "#!/usr/bin/bash
now=\`date +\"\%Y-\%m-\%-d \%H:\%M\"\`
echo -e \"单次联机物理备份执行时间:\$now\" >> $install_path/at_cron.log 2>&1
now=\`date +\"\%Y\%m\%d\%H\%M\"\`
$install_path_bin/disql $user_name/$user_passwd\@LOCALHOST:$port -e \"backup $backup_raise $name full backupset \'$backup_dir\$now\'\" >> $install_path/at_cron.log 2>&1
";
close bak_sql;
}else{
#库级和表空间级增备脚本:
open bak_sql, '>', "$install_path_bak_exc_file/AT_$now.sql";
print bak_sql "#!/usr/bin/bash
now=\`date +\"\%Y-\%m-\%-d \%H:\%M\"\`
echo -e \"单次联机物理备份执行时间:\$now\" >> $install_path/at_cron.log 2>&1
now=\`date +\"\%Y\%m\%d\%H\%M\"\`
$install_path_bin/disql $user_name/$user_passwd\@LOCALHOST:$port -e \"backup $backup_raise $name $backup_raise with backupdir \'$base_on_backup_dir\' backupset \'$backup_dir\$now\'\" >> $install_path/at_cron.log 2>&1
";
close bak_sql;
}
system("chmod 777 $install_path_bak_exc_file/AT_$now.sql");
system("at -f $install_path_bak_exc_file/AT_$now.sql now");
}