基于自动化运维平台实现mysql物理备份

自动化运维管理平台：

• 支撑八大运维场景自动化（如下图所示)，覆盖常见运维工作。
• 灵活的可视化任务编排及调度，操作、资源、工具、人员的任意编排调度，构建多方位自动化能力。
• 内置大量运维操作原子场景，支持常见数据库、中间件、云平台、网络设备等运维操作自动化能力。

SQL> create user monmydb@localhost identified with mysql_native_password by XXXXX;
SQL> grant process on *.* to monmydb@localhost;
SQL> grant super on *.* to monmydb@localhost;
SQL> grant replication client on *.* to monmydb@localhost;
SQL> grant select on *.* to monmydb@localhost;
SQL>flush privileges;
SQL> show grants for monmydb@localhost;

percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz
cd /mysql/dacp/mysql/mysqlbak/scripts
tar -zxvf percona-xtrabackup-2.4.7-Linux-x86_64.tar.gz

#!/bin/bash
Innobackupex_Path=/mysql/dacp/mysql/mysqlbak/scripts/percona-xtrabackup-2.4.7-Linux-x86_64/bin/xtrabackup
Mysql_Client=/mysql/dacp/mysql/soft/bin/mysql
Bak_Time=`date +%Y%m%d_%H%M%S`

Backup_Dir=/mysql/dacp/mysql/mysqlbak/data #备份主目录
#Backup_Dir=/opt/backup/data/`data -I` #按日期生成备份主目录
Fullbackup_Path=$Backup_Dir/full # 全库备份的目录
Incrbackup_Path=$Backup_Dir/incr # 增量备份的目录
Log_Path=$Backup_Dir/logs #日志文件目录
Keep_Fullbackup=2 #保留的全备份数量,此处要加1; 如要保留2个，此处要写3
Mysql_Conf=/etc/my.cnf #mysql配置文件
export MYSQL_PWD=xxxxxxx
SOCK=/mysql/dacp/mysql/data/mysql.sock
Mysql_Opts="$Mysql_Client -s --host=localhost --user=root --password=$MYSQL_PWD -S$SOCK" #mysql的连接配置，按需修改

Error()
{
echo -e "e[1;31m$1e[0m" 1>&2
exit 1
}

Backup()
{

if [ $# = 2 ] ; then
$Innobackupex_Path --defaults-file=$Mysql_Conf -uroot -p$MYSQL_PWD  --backup --parallel=3 --target-dir $1/full_$Bak_Time>$2 2>&1

elif [ $# = 3 ];then
$Innobackupex_Path --defaults-file=$Mysql_Conf -uroot -p$MYSQL_PWD --backup --target-dir $1/incr_$Bak_Time  --incremental-basedir $2 >$3 2>&1
else
Error "Backup(): 参数不正确"
fi
}

Lastest_Dir()
{
if [ -d $1 ]; then
path=`ls -t $1 |head -n 1`
if [ $path ]; then
echo $path
else
Error "Lastest_Diri(): 目录为空,没有最新目录"
fi
else
Error "Latest_Dir(): 目录不存在或者不是目录"
fi
}

Do_Inc()
{
if [ "$(ls -A $Incrbackup_Path)" ] ; then

Backup $Incrbackup_Path $Incrbackup_Path/`Lastest_Dir $Incrbackup_Path` $Log_Path/incr_$Bak_Time.log
else

Backup $Incrbackup_Path  $Log_Path/incr_full_$Bak_Time.log
fi
}

Do_Full()
{
Backup $Fullbackup_Path $Log_Path/full_$Bak_Time.log
cd $Fullbackup_Path
ls -t |tail -n +$Keep_Fullbackup |xargs rm -rf

}

Check()
{

if [ ! -d $Fullbackup_Path ];then
mkdir -p $Fullbackup_Path
fi

if [ ! -d $Incrbackup_Path ];then
mkdir -p $Incrbackup_Path
fi

if [ ! -d $Log_Path ];then
mkdir -p $Log_Path
fi

if [ ! -f $Innobackupex_Path ];then
Error "未安装xtradbbackup或xtradbbackup路径不正确"
fi

if [ ! -f $Mysql_Client ];then
Error "未安装mysql客户端"
fi

if [ ! -f $Mysql_Conf ];then
Error "mysql配置文件路径不正确"
fi

if [ `ps -ef |grep mysqld |grep -v grep |wc -l` = 0 ];then
Error "MySQL没有运行"
fi

if  ! `echo exit |$Mysql_Opts >/dev/null 2>&1` ; then
Error "提供的数据库连接配置不正确!"
fi
}
case $1 in

full)
Check
Do_Full
;;

inc)
Check
Do_Inc
;;

*)
echo "full 全量备份"
echo "inc 增量备份"
;;
esac

#!/bin/bash

unamesr="`uname -sr`"
case "$unamesr" in
AIX*)
AWK=awk
. $HOME/.profile
;;
SunOS 5*)
AWK=nawk
. $HOME/.profile
;;
HP*)
AWK=awk
. $HOME/.profile
;;
Linux*)
AWK=awk
. $HOME/.bash_profile
;;
esac

directory=$directory
sh_directory=$sh_directory
log_directory=$log_directory
user=$user

sudo su - $user <rm -rf $directory/mysqlbak/data/full/full*
rm -f ${log_directory}/*.log
EOF

tmp1=`df -mP |grep ${directory}|awk {print $4}`
tmp2=`df -mP |grep ${directory}|awk {print $3}`

if [[ ${tmp1} -gt ${tmp2} ]];then

echo "可执行备份"

sudo su - $user <sh $sh_directory/xtradump.sh full
EOF

sudo su - $user <cat ${log_directory}/*.log |grep completed OK |wc -l > /tmp/backup.log
chmod 775 /tmp/backup.log
EOF

backup=`cat /tmp/backup.log`

if [[ ${backup} = 1 ]];then

output=0
echo "备份成功"
else
output=1
echo "备份失败，请检查"
fi
else
echo "目录空间不足，不可执行备份"
output=1
fi
exit 0

#!/bin/bash

data_directory="/mysql/dacp/mysql/mysqlbak/data/full"
sh_directory="/mysql/dacp/mysql/mysqlbak/scripts"

/usr/openv/netbackup/bin/bparchive -p MYSQL_DMP_ARC -s User_Archive -L $sh_directory/`date +%Y%m%d`.log -w $data_directory/full_*

find $sh_directory/ -name "*.log" -mtime +1 |xargs -i rm {}

#!/bin/bash
sh_directory="/mysql/dacp/mysql/mysqlbak/scripts"
ipa=`ip addr |grep inet|grep -v 127.0.0.1|grep -v inet6|awk {print $2}|tr -d "addr:"|grep 134.|head -n 1`
hostip=${ipa%/*}
cat $sh_directory/`date +%Y%m%d`.log|grep successfully |wc -l > /tmp/trscheck.log

check=`cat /tmp/trscheck.log`

if [[ ${check} = 1 ]];then
echo "$hostip `date +%F %T` 备份文件发送成功" >> $sh_directory/trscheck.log

else

cd $sh_directory
./mess.sh -b "`date +%F %T` $hostip ERROR: mysql备份文件发送失败，请检查 " -t 18974885939,15367933992
echo " `date +%F %T` $hostip mysql备份文件发送失败，请检查" >> $sh_directory/trscheck.log
fi