MYSQL备份脚本

时间：2010-04-27 来源：lh0365

脚本修改：添加了PERL，增加锁表动作。

发一个刚刚写完的脚本。想法源于ORACLE的RMAN备份策略

脚本目的：MYSQL的自动备份

备份方式：全备和增量备份

备份方法：每周日进行一次全备；每周一、二、四、五进行当天的增量备份；每周三备份从上次全备以来的增量备份（备份三天）；每周六备份从上次全备以来的增量备份（备份六天）

全备模式：基于LVM快照模式

增量备份：备份每天新的二进制的日志文件

目前存在一个问题：脚本首次执行备份的时间，正在想方案解决。

希望和大家共同研究学习！

BASH部分

#!/bin/bash
g_Date_Ago_1_Path=`date -d "1 day ago" +%Y%m%d`
g_Date_Ago_3_Path=`date -d "3 days ago" +%Y%m%d`
g_Date_Ago_6_Path=`date -d "6 days ago" +%Y%m%d`
g_Date_Path=`date +%Y%m%d`
g_Db_Log_Index=mysql-bin.index
g_Back_Path=/backup
g_Group_Name=mysql_var
g_Lv_Name=mysql_var_1
g_Snap_Name=snap1
g_Device_Path=/dev
g_Data_Path=/data/snapshot
g_Db_Path=/data/var
g_Db_User=slave
g_Db_Pass=123456
g_Big_Size=11G
g_Today=`date +%a`
g_Db_Host=192.168.198.103

######################################################mkdir move function###########################################################
function Mk_dir {
if [ ! -e ${g_Back_Path}/${g_Date_Path} ]
then
mkdir -p ${g_Back_Path}/${g_Date_Path}
fi
}

function Find_Mov_1_log {
Last_Name=`awk -F'/' '{print $2}' ${g_Back_Path}/${g_Date_Ago_1_Path}/${g_Db_Log_Index}|tail -1`
Line_Num=`cat -n ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index} |sed -n "/$Last_Name/{n;p}"|awk '{print $1}'`
New_File_Log=`cat -n ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index} |awk -valn="$Line_Num" '{if ( $1 >= aln) print $2}'`
cd ${g_Db_Path}
for New_log in ${New_File_Log[*]}
do
cp -p ${New_log} ${g_Back_Path}/${g_Date_Path}/
echo "copy bin_log:${New_log}" >>${g_Back_Path}/${g_Date_Path}/backup.log
done
}

function Find_Mov_3_log {
Last_Name=`awk -F'/' '{print $2}' ${g_Back_Path}/${g_Date_Ago_3_Path}/${g_Db_Log_Index}|tail -1`
Line_Num=`cat -n ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index} |sed -n "/$Last_Name/{n;p}"|awk '{print $1}'`
New_File_Log=`cat -n ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index} |awk -valn="$Line_Num" '{if ( $1 >= aln) print $2}'`
cd ${g_Db_Path}
for New_log in ${New_File_Log[*]}
do
cp -p $New_log ${g_Back_Path}/${g_Date_Path}/
echo "copy bin_log:${New_log}" >>${g_Back_Path}/${g_Date_Path}/backup.log
done
}

function Find_Mov_6_log {
Last_Name=`awk -F'/' '{print $2}' ${g_Back_Path}/${g_Date_Ago_6_Path}/${g_Db_Log_Index}|tail -1`
Line_Num=`cat -n ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index} |sed -n "/$Last_Name/{n;p}"|awk '{print $1}'`
New_File_Log=`cat -n ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index} |awk -valn="$Line_Num" '{if ( $1 >= aln) print $2}'`
cd ${g_Db_Path}
for New_log in ${New_File_Log[*]}
do
cp -p $New_log ${g_Back_Path}/${g_Date_Path}/
echo "copy bin_log:${New_log}" >>${g_Back_Path}/${g_Date_Path}/backup.log
done
}

#######################################################Sun Full_Backup##########################################################
function Full_backup {
Mk_dir
cp ${g_Db_Path}/${g_Db_Log_Index} ${g_Back_Path}/${g_Date_Path}
perl create_lock.pl ${g_Db_Host} ${g_Db_User} ${g_Db_Pass} ${g_Device_Path} ${g_Group_Name} ${g_Lv_Name} ${g_Snap_Name} ${g_Big_Size}
#lvcreate -s -n${g_Snap_Name} -L${g_Big_Size} ${g_Device_Path}/${g_Group_Name}/${g_Lv_Name}
mount ${g_Device_Path}/${g_Group_Name}/${g_Snap_Name} ${g_Data_Path}
cd ${g_Back_Path}/${g_Date_Path}
tar czf mysql_${g_Date_Path}.tgz ${g_Data_Path}
#mysqldump --all-database --delete-master-logs -u${g_Db_User} -p${g_Db_Pass} --lock-all-tables >${g_Back_Path}/${g_Date_Path}/mysqldump_${g_Date_Path}.sql
umount ${g_Data_Path}
lvremove -f ${g_Device_Path}/${g_Group_Name}/${g_Snap_Name}

tar tvf mysql_${g_Date_Path}.tgz >/dev/null
if [ $? -ne 0 ]
then
echo "tar error">> ${g_Back_Path}/${g_Date_Path}/error.log
fi

mount |grep ${g_Snap_Name}
if [ $? -eq 0 ]
then
fuser -m -k -v ${g_Data_Path}
umount ${g_Data_Path}
fi

lvdisplay |grep ${g_Snap_Name}
if [ $? -eq 0 ]
then
lvremove -f ${g_Device_Path}/${g_Group_Name}/${g_Snap_Name}
fi
}
####################################################Mon Tue Thu Fri incre_backup##########################################
function Incre_backup_1 {
Mk_dir

cp ${g_Db_Path}/${g_Db_Log_Index} ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index}
mysql -u${g_Db_User} -p${g_Db_Pass} -e "flush logs"

Find_Mov_1_log
}
######################################################Wed incre_backup###################################################
function Incre_backup_3 {
Mk_dir

cp ${g_Db_Path}/${g_Db_Log_Index} ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index}
mysql -u${g_Db_User} -p${g_Db_Pass} -e "flush logs"

Find_Mov_3_log
}
########################################################Sat incre_backup##################################################
function Incre_backup_6 {
Mk_dir

cp ${g_Db_Path}/${g_Db_Log_Index} ${g_Back_Path}/${g_Date_Path}/${g_Db_Log_Index}
mysql -u${g_Db_User} -p${g_Db_Pass} -e "flush logs"

Find_Mov_6_log
}
##########################################################if Today#######################################################
if [ ${g_Today} = Mon ] || [ ${g_Today} = Tue ] || [ ${g_Today} = Thu ] || [ ${g_Today} = Fri ]
then
Incre_backup_1
fi

if [ ${g_Today} = Wed ]
then
Incre_backup_3
fi

if [ ${g_Today} = Sat ]
then
Incre_backup_6
fi

if [ ${g_Today} = Sun ]
then
Full_backup
fi

perl部分 #!/usr/bin/perl
use DBI;
my $g_Db_Host=${ARGV[0]};
my $g_Db_User=${ARGV[1]};
my $g_Db_Pass=${ARGV[2]};
my $g_Device_Path=${ARGV[3]};
my $g_Group_Name=${ARGV[4]};
my $g_Lv_Name=${ARGV[5]};
my $g_Snap_Name=${ARGV[6]};
my $g_Big_Size=${ARGV[7]};
my $dbh = DBI->connect( "DBI:mysql:database=mysql;host=$g_Db_Host",
                        $g_Db_User,
                        $g_Db_Pass,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      );
my $sql_use = qq{flush tables with read lock};
my $sql_count = qq{flush logs};
print "$sql_use\n";
print "$sql_count\n";
my $re=$dbh->do($sql_use);
if (!$re) {
        return 0;
};
my $ree=$dbh->do($sql_count);
if (!$ree) {
        return 0;
};
system "lvcreate -s -n${g_Snap_Name} -L${g_Big_Size} ${g_Device_Path}/${g_Group_Name}/${g_Lv_Name}";
$dbh->commit();
$dbh->disconnect();