mysql_bak.sh

#!/bin/bash
############################################################
# $Name:         mysql_bak.sh
# $Version:      v1.0
# $Functions:
# $Author:       Jeson Li
# $organization: www.lzbbg.cn
# $Create Date:  2017-03-10
# $Mail:         libobin@tom.com
# $Description:  Use Linux mysql Sub database table backup to /backup dir
############################################################
source /etc/profile
backup='/backup'
mysql_bin2nfs='mysql_bin2nfs'
DBdir_paths="$(ls -d /mysql_data/*/)"
Local_IP="`ip addr show eth0|awk -F"[ \t/]*" '$2~"^inet$"{print $3}'|head -1`"
    for DBdir_path in $(echo ${DBdir_paths})
    do
        CMD="-uroot -pjeson123456 -S ${DBdir_path}mysql.sock"
        db_names=$(mysql ${CMD} -e "show databases;"|awk 'NR>1{print $1}')
        DBdir_name=$(echo ${DBdir_path}|awk -F "/" '{print $(NF-1)}')
        backup_dir_path="${backup}/${Local_IP}/${DBdir_name}.$(date +%F_%w)"
            mkdir -p ${backup_dir_path}
            cd ${backup_dir_path}
            mysql ${CMD} -e "flush table with read lock;"
            mysqladmin ${CMD} flush-logs
            for db_name in $(echo ${db_names})
            do
                [ "${db_name}" == "mysql" ] && events='--events' || events=''
                mysql ${CMD} -e 'show variables like "log_bin";'|grep "OFF" &&  master_data='' || master_data='--master-data=1'
                mysqldump ${CMD} -B -c -x ${master_data} ${events} ${db_name}|gzip >${db_name}.sql.gz 
                [ -s ${db_name}.sql.gz ] && md5sum ${db_name}.sql.gz >${db_name}.sql.gz.md5
            done
            mysql ${CMD} -e "unlock tables;"
            [ $(ls ${DBdir_path}${mysql_bin2nfs}/|wc -l) -gt 0 ] && \cp -a ${DBdir_path}${mysql_bin2nfs}/mysql_bin.?????? ${backup_dir_path}
    done