编写shell脚本,利用mysqldump实现MYSQL数据库分库分表备份
#!/bin/bash
# define var
BAK_DIR=/backup/db
CMD_OPT="-uroot -pzmy@456PP"
EX_DB="information_schema|mysql|performance_schema|sys"
DBS=$(mysql ${CMD_OPT} -N -e 'show databases' | egrep -v ${EX_DB})
# main program
for db in $DBS
do
TABS=$(mysql ${CMD_OPT} -N -e "show tables from $db")
[ -d ${BAK_DIR}/$db ] || mkdir -p ${BAK_DIR}/$db
mysqldump ${CMD_OPT} -B $db | gzip > ${BAK_DIR}/$db/${db}_$(date +%F).sql.gz
#
for tab in $TABS
do
mysqldump ${CMD_OPT} $db $tab | gzip > ${BAK_DIR}/$db/${db}_${tab}_$(date +%F).sql.gz
done
done
以下是备份的结果