mysql物理及逻辑备份shell脚本

备份简要说明

• 物理备份采用xtrbackup （只实现全备逻辑）
• 逻辑备份采用mysqldump（分库分表）
• 脚本代码根据实际应用场景可选备份逻辑，并结合系统计划任务自动备份

#!/bin/bash
###mysql backup###
TS=$(date +%Y-%m-%d_%H-%M-%S)
Data_dir=/data3/data/mysql/
MySQL_bak_dir=/data1/mysql_bak/
Password="mysqlroot@wx2016"
cmd="mysql -uroot -p"
log_time="date +%Y%m%d_%H:%M:%S"
lock_dir=/var/lock/subsys/
Usage() {

        echo "USAGE:$0 {physical_backup|logic_backup}"
}

write_log() {

        content=$1
        echo "`${log_time}`  $content" >> /tmp/mysql_bak.log
}

function mysql_physical_backup() {

        write_log "start innobackup"
        /usr/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=mysqlroot@wx2016 ${MySQL_bak_dir}
        sleep 5
        writ_log "start apply log"
        /usr/bin/innobackupex --defaults-file=/etc/my.cnf --socket=/tmp/mysql.sock --user=root --password=mysqlroot@wx2016 --apply-log ${MySQL_bak_dir}$TS
        write_log "innobackup and apply log finished"
}


function mysql_logic_backup() {

        db=`$cmd${Password} -e "show databases"|egrep -v "Database|information_schema|performance_schema|ts_db|aaa|mysql"`
        for database in $db
                do
                        write_log "mysqldump start ${database} backup"
                        tb=`$cmd${Password} -e "use ${database};show tables"|grep -v "Tables_in_${database}"`
                        for table in $tb
                        do
                                write_log "backup ${database} ${table}"
                                [ -d ${MySQL_bak_dir}mysqldump/$TS/${database}/ ] || mkdir -p ${MySQL_bak_dir}mysqldump/$TS/${database}
                                /usr/bin/mysqldump -uroot -p${Password} ${database} ${table} >${MySQL_bak_dir}mysqldump/$TS/${database}/${table}

                        done
                        write_log "mysqldump ${database} backup finished.."
                done

}

main() {

while [ $# -ge 1 ]
do
        mysql_bak_option="${1}"
        case "${mysql_bak_option}" in
                physical_backup)
                mysql_physical_backup;
                shift;;
                logic_backup)
                mysql_logic_backup;
                shift;;
                *)
                Usage;
                exit 1
        esac
#mysql_logic_backup
done
}

(
        flock -n 200
        if [ $? -ne 0 ];then
                echo "script is running..."
                exit 2
        fi
        main "$@"
        flock -u 200

)200>${lock_dir}mysql_bak.lock