mysql数据备份策略-shell脚本优化代码

一.备份的主机上需要自己手动安装xtrabackup工具

1.安装xtrabackup-yum源

#下载xtrabackup的yum源
wget http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
  rpm -ivh percona-release-0.1-4.noarch.rpm

 2.打开yum源

#打开xtrabackup的yum源 
vim percona-release.repo

 

3.下载

#2.下载xtrabackup 工具
yum -y install percona-xtrabackup-24.x86_64

二.代码

1.vim mysql_bauk.sh

#!/usr/bin/bash
<<!
数据库备份脚本方式: 物理备份
工具:xtrabackup过程
需求1:不允许有任何的安装在脚本中，
需求2:在脚本中，不允许有任何的交式代码容，使用日志记录
需求3:在脚本中，不允许有任何输出
需求4:每周为一个循环周期，1全备、2增量、3增量、4差异、5增量、6增量、7差异
需求5:自动检测 执行报错，邮件告警
!

# 备份目录

backup_dir=/opt/mysql_back

# 记录日志，记录备份是否成功

backup_log=/var/log/mysql_back.log

# 记录备份的状态和备份的类型

back_status_log=${backup_dir}/back_status.log

# 数据库的账号

db_user=root
db_password="Qian@123"

cmd="innobackupex --user=${db_user} --password=${db_password}"

# 全备指令

# innobackupex --user=root --password='123' /xtrabackup/full

# 其他指令

# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2019-08-20_15-04-29/

# f full i intr d diff

rule=(d f i i d i i)
ipaddr=$(ip -4 a show dev ens33 | awk '/inet/{print $2}')
send_email(){
    to_user=1161733918@qq.com
    mail -s "数据备份状态邮件，请及时查收" $to_user <<EOF
$1
EOF
}

backup_full(){
    tar cvzf /opt/mysql_backup_`date "+%F_%H-%M-%S"`.tar.gz $backup_dir && \
    rm -rf $backup_dir || \
    (
        send_email "[`date +'%F %T'`] 在做数据打包时出现异常，请及时检查，当前主机 ${ipaddr}"
        echo "[`date +'%F %T'`] 备份数据打包 失败" >> $backup_log
    )
    if [ ! -d $backup_dir/full ];then
        mkdir $backup_dir/full
    fi
    $cmd $backup_dir/full && \
    (
    echo "[full] $backup_dir/full/`ls $backup_dir/full`" >> $back_status_log
    echo "[`date +'%F %T'`] full备份 成功" >> $backup_log
    ) || \
    (
        send_email "[`date +'%F %T'`] 在进行full备份时，出现无法解决的异常，请及时检查 当前主机 ${ipaddr}"
        echo "[`date +'%F %T'`] full备份 失败" >> $backup_log
    )
}
backup_other(){
    if [ ! -d $backup_dir/$1 ];then
        mkdir $backup_dir/$1
    fi
    $cmd --incremental $backup_dir/$1 --incremental-basedir=$2 && \
    (
    echo "[$1] $backup_dir/$1/`ls $backup_dir/$1 | tail -1`" >> $back_status_log
    echo "[`date +'%F %T'`] $1备份 成功" >> $backup_log
    ) || \
    (
        send_email "[`date +'%F %T'`] 在进行${1}备份时遇到无法解决的异常，请及时检查 当前主机 ${ipaddr}"
        echo "[`date +'%F %T'`] $1备份 失败" >> $backup_log
    )
}

case ${rule[$(date "+%w")]} in
f)
    backup_full
    ;;
d)
    path=`fgrep "[full]" $back_status_log | awk 'END{print $2}'`
    backup_other diff "$path";;
i)
    path=`tail -1 $back_status_log | awk '{print $2}'`
    backup_other intr $path;;
esac

2.配置mailx的插件

#下载
yum -y install mailx
#添加以下内容
vim /etc/mail.rc
set bsdcompat
set from=邮箱@qq.com
set smtp=smtp.qq.com
set smtp-auth-user=邮箱@qq.com
set smtp-auth-password=邮箱的授权码
set smtp-auth=login
set ssl-verify=ignore