mysqldump工具实现mysql数据库单机的周期性备份

mysqldump工具实现mysql数据库单机的周期性备份

#!/bin/bash
#PS=使用mysqldump工具实现mysql数据库单机的周期性备份;
set -u
color_red="echo -e \033[1;31m"
color_end="\033[0m"
OS_type=`cat /etc/redhat-release |awk -F"[[:space:]]+|[.]" '{print $4}'`
Database_user="root"
Database_passwd="redhat@2022"
Db_type=`rpm -qa |grep -Eo "mariadb-server|mysql-server"|head -n 1`
Database_type=`mysql -uroot -p${Database_passwd} -e "show engines;" |sed -rn 's/(.*)DEFAULT.*/\1/p'`
Backup_time=`date  "+%Y-%m-%d_%H:%M"`

#环境检查
function check_mysqldump() {
    case ${OS_type} in
        7)
            [[ ${Db_type} =~ mariadb-server ]] || { $color_red"Not installed mariadb"$color_end;exit 1; }
            systemctl status mariadb 2&>/dev/null || { $color_red"service  mariadb is unstart"$color_end;exit 1; }
            type mysqldump 2&>/dev/null || { ${color_red} "Please install the  `rpm -qf /usr/bin/mysqldump`" ${color_end};exit 1;}
            ;;
        8)
            [[ ${Db_type} =~ mysql-server ]] || { $color_red"Not installed mysql"$color_end;exit 1; }
            type mysqldump 2&>/dev/null || { ${color_red} "Please install the  `rpm -qf /usr/bin/mysqldump`" ${color_end};exit 1;}
            systemctl status mysqld 2&>/dev/null || { $color_red"service  mariadb is unstart"$color_end;exit 1; }
            ;;
        *)
           ${color_red} "Abnormal environment" ${color_end}
            exit 1
            ;;
    esac
}


#mysqldump全备
function all_database_backup(){
        [ ! -d /backup ] && { mkdir -p /backup/all;chown mysql.mysql /backup/all; }
        if [[ ${Database_type} =~ InnoDB ]];then
            mysqldump -u${Database_user} -p "${Database_passwd}" -A -F -E -R --triggers --single-transaction --master-data=2 --flush-privileges | gzip > /backup/all/${Backup_time}.sql.gz
            sleep 15
                [ -f ${Backup_time}.sql.gz ] || ${color_red}"Backup failure"${color_end}
        elif [[ ${Database_type} =~ MyISAM ]];then
            mysqldump -u${Database_user} -p -A -F -E -R -x --master-data=2 --flush-privileges --triggers --hex-blob > /backup/all/${Backup_time}.sql.gz
                [ -f ${Backup_time}.sql.gz ] || ${color_red}"Backup failure"${color_end}
        else 
             ${color_red}"Backup not supported"${color_end}
        fi
}

#mysqldump单库备份
function Single_database_backup() {
        [ ! -d /backup ] && { mkdir -p /backup/single;chown mysql.mysql /backup/single; }
        a=0
        for db in `mysqldump -${Database_user} -p "${Database_passwd}" -e "show databases;" |grep -Ev '^(Database|information_schema|performance_schema)$'`;do
                Database_list[$a]=$db
                let a++
        done
        read -p "需备份的库为:${Database_list[*]}(共${#Database_list[*]}个)" Database_Backup_name
        for db_name in `echo ${Database_Backup_name}|tr -s " " "\n"`;do
                mysqldump -u${Database_user} -p "${Database_passwd}" -F -E -R --single-transaction --masterdata=2 -q -B $db_name |gzip > /backup/single/${Backup_time}.sql.gz
        done
}   

#生成备份计划任务
function Generating_scheduled_Tasks(){
    read -p "全备计划任务-1,单库备份-2:" Number
    case   $Number in 
    1)
        mkdir -p /backup/backup_sh
        #生成脚本文件
        echo $PTAH > all_database_backup.sh
        tee /backup/backup_sh/all_database_backup.sh <<EOF
        Database_user="root"
        Database_passwd="redhat@2022"
        Backup_time=`date  "+%Y-%m-%d_%H:%M"`
        mysqldump -u${Database_user} -p "${Database_passwd}" -A -F -R --single-transaction --default-characterset=utf8 --master-data=2 | gzip > /backup/all/${Backup_time}.sql.gz
EOF
        [ -f /backup/backup_sh/all_database_backup.sh ] || exit 1
        #生成crontab文件：预计为每周六的凌晨2点15分执行all备份
        if [ ! -f /etc/cron.d/all_database_backup ];then
        tee /etc/cron.d/all_database_backup <<EOF
        15 2 * * 6  root sh /backup/backup_sh/all_database_backup.sh
EOF
        else
            $color_red"File already exists"$color_end
        fi
        ;;
    2)
        mkdir -p /backup/backup_sh
        #生成脚本文件
        echo $PTAH > single_database_backup.sh
        tee /backup/backup_sh/single_database_backup.sh <<EOF
        Database_user="root"
        Database_passwd="redhat@2022"
        Backup_time=`date  "+%Y-%m-%d_%H:%M"`
        for DB in `mysql -u${Database_user} -p"${Database_passwd}"  -e 'show databases' | grep -Ev '^(Database|information_schema|performance_schema)$'`;do
        mysqldump -u${Database_user} -p "${Database_passwd}" -F -E -R --single-transaction --master-data=2 --default-characterset=utf8  -q -B $DB | gzip >/backup/single/${Backup_time}.sql.gz
        done
EOF
        [ -f /backup/backup_sh/single_database_backup.sh ] || exit 1
        #生成crontab文件：预计为周一到周五的凌晨4点15分执行单库的备份
        if [ ! -f /etc/cron.d/all_database_backup ];then
        tee /etc/cron.d/single_database_backup <<EOF
        15 4 * * 1-5  root sh /backup/backup_sh/all_database_backup.sh
EOF
        else
            $color_red"File already exists"$color_end
        fi
        ;;
    *)
        $color_red"Please enter 1 or 2"$color_end
        ;;
    esac
}


#增量备份
function incremental_backup(){
    read -p "Enter the full backup path and filename" all_file
    [ ! -e all_Path ] || { $color_red"Please enter a path"$color_end;exit 2; }
    [ -f  ${all_file} ] || { $color_red"There is no file"$color_end;exit 2; } 
    #获取全备sql文件的二进制log名称
    all_log_file=`sed -rn "s/^--.*MASTER_LOG_FILE='(.*)', MASTER_LOG_POS=.*;$/\1/p" ${all_file}`
    #获取全备sql文件的二进制log记录的值
    all_log_number=`sed -rn "s/^--.*MASTER_LOG_FILE='.*', MASTER_LOG_POS=(.*);$/\1/p" ${all_file}`
    #获取mysql二进制log的路径
    cat /etc/my.cnf /etc/my.cnf.d/mysql-server.cnf /etc/my.cnf.d/server.cnf >/var/Temporary.file
    while read line;do
    if [[ `echo $line |grep -Ei 'log-bin'` ]];then
            log_path=`echo $line |sed -rn 's@.*=(/.*/)\b.*@\1@p'`
    fi
    done < /var/Temporary.file
    rm -f /var/Temporary.file
    #判断全备二进制log的文件编号:
    all_num=`echo ${all_log_file} |awk -F"[.]" '{print $2}'`
        if [ ${all_num:4:1} -ge 0 ];then
             tital=0
        else
            tital=1
        fi
       [ ! -e  ${log_path} ] || exit 2
    #判断bin_log序号，并备份
    j=1
    case  ${tital} in
        0)
            for i in `$(ls ${log_path})`;do
            a=`echo $i|awk -F"[.]" '{print $2}'`
            if [ ${a:5:1} -qe ${all_num:5:1} ];then
                    mysqlbinlog ${all_log_file} --start-position=${all_log_number} > /root/inc_1.sq
            elif [ ${a:5:1} -gt ${all_num:5:1} ];then
                let j++
                    mysqlbinlog $i > /root/inc_${j}.sql
            fi
            done
            ;;
        1)
            for i in `$(ls ${log_path})`;do
            a=`echo $i|awk -F"[.]" '{print $2}'`
            if [ ${a:4:2} -qe ${all_num:4:2} ];then
                mysqlbinlog ${all_log_file} --start-position=${all_log_number} > /root/inc_1.sq
            elif [ ${a:4:2} -gt ${all_num:4:2} ];then
                let j++
                mysqlbinlog $i > /root/inc_${j}.sql
            fi
            done
            ;;
    esac

}



PS3="请选择：(1-5):"
select MENU in  环境检查 mysql全备 mysql单库备份 计划任务生成 退出 ;do
case $REPLY in
1)
    check_mysqldump
    ;;
2)
    check_mysqldump
    all_database_backup
    ;;
3)
    check_mysqldump
    Single_database_backup
    ;;
4)
    check_mysqldump
    Generating_scheduled_Tasks
    ;;
5)
    exit 1
    ;;
esac
done