msql-分库分表备份

#!/bin/sh

################

# author: will

# createdate: 20170314

# function: mysql db backup

#

##################

TIME=`date -d '1 day ago' +%Y%m%d`

PASSWORD=""

USERNAME="root"

###mysql 路径

MYSQL="/usr/local/webserver/mysql/bin/mysql -u${USERNAME} -p${PASSWORD}"

MYDUMP="/usr/local/webserver/mysql/bin/mysqldump  -u${USERNAME} -p${PASSWORD}"

###备份路径

BACKPATH=/data/db_backup/${TIME}

[ ! -d ${BACKPATH} ] && mkdir -p /data/db_backup/${TIME}

#---------------------------------------

###备份所有库

DBLIST=`${MYSQL} -e "show databases;"|sed 1d|egrep -v "_schema|mysql" `

###备份指定库

#DBLIST="cacti opola"

#----------------------------------------

for dbname in ${DBLIST}

 do

   TLIST=`${MYSQL} -e "show tables from ${dbname}"|sed 1d`

###备份库

#     ${MYDUMP} ${dbname}  --single-transaction --routines --events --master-data=2 |gzip > /data/db_backup/${TIME}/${dbname}/${dbname}_${TIME}sql.gz 

###备份表

for tname in ${TLIST}

       do

          mkdir -p /data/db_backup/${TIME}/${dbname}

          ${MYDUMP} ${dbname} ${tname}  --single-transaction --routines --events --master-data=2 |gzip > /data/db_backup/${TIME}/${dbname}/${dbname}_${tname}${TIME}sql.gz

       done

 

 done