Mysql自动备份脚本

#!/bin/bash
PATH=/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin
export PATH
#检查是否安装bzip2
check_bz2=`rpm -qa | grep bzip2 | wc -l`
if [ $check_bz2 -eq 0 ];then
    yum -y install bzip2
else
    echo "GO RUN!"
fi
#数据库用户名
dbuser='dbuser'
#数据库密码
dbpasswd='dbpasswd'
#数据库名,可以定义多个数据库，中间以空格隔开，如：test test1 test2
dbname='dbname'
#备份时间
backtimes=`date +%Y年%m月%d日%H点%M分%S秒`
backtime=`date +%Y-%m-%d-%H-%M-%S`
#日志备份路径
mkdir -p /usr/local/devops/log/mysql_backuplog 
logpath='/usr/local/devops/log/mysql_backuplog'
#数据备份路径
mkdir -p /usr/local/devops/backup/mysqlbackup
datapath='/usr/local/devops/backup/mysqlbackup'
#正式备份数据库
for table in $dbname; do
source=`mysqldump  -u${dbuser} -p${dbpasswd} --single-transaction ${table}> ${datapath}/${backtime}.sql` 2>> ${logpath}/mysqllog.log;
#备份成功以下操作
if [ "$?" == 0 ];then
cd $datapath
#为节约硬盘空间，将数据库压缩
tar jcf ${table}${backtime}.tar.bz2 ${backtime}.sql > /dev/null
#删除原始文件，只留压缩后文件
rm -f ${datapath}/${backtime}.sql
echo "${backtimes}-数据库${dbname} 备份成功!!" >> ${logpath}/mysqllog.log
else
#备份失败则进行以下操作
echo "${backtimes}-数据库${dbname} 备份失败!!" >> ${logpath}/mysqllog.log
fi
done

想实现自动化备份可在crontab追加一条

0 0 1 * * sh /usr/local/devops/shell/mysql_backup.sh #每天凌晨1点执行一次

或者在脚本里加一段判断自动追加至crontab
注意：路径需自己创建并赋予脚本执行权限哦！

cr=`crontab -l | grep -v grep | wc -l`
if [ $cr -eq 0 ];then
    echo "0 0 1 * * sh /usr/local/devops/shell/mysql_backup.sh" > conf && crontab conf && rm -rf conf     
else
    crs=`crontab -l | grep -v grep | grep -w mysql_backup.sh | wc -l`
    if [ $crs -eq 0 ];then
        crontab -l > conf && echo "0 0 1 * * sh /usr/local/devops/shell/mysql_backup.sh" > /tmp/tmp.txt >> conf && crontab conf && rm -rf conf 
    else
        crontab -l > conf && crontab conf && rm -rf conf 
    fi    
fi