背景:作为运维,备份及其重要,在进行删改操作,无论是应用系统还是数据库,还是系统文件,备份是第一步,警惕一顿操作猛如虎的行为
备份:我可以根据库备份,也可以根据库中的表来备份。所以写了2个脚本,备份的粒度不一样,就可以多方面去实现我们想要的恢复效果
一、分库备份
USER=root
PASSWD=123456
HOST=localhost
DATE=$(date +%F-%H-%M-%S)
BACK_DIR=/data/daba_bak
MAIL_ACC=450776260@qq.com
#获取库
DATA_LIST=$(mysql -u$USER -p$PASSWD -s -e "show databases;" |egrep -v "(Database|information_schema|performance_schema|testdata)" 2>/dev/null)
#判断是否存在备份目录
if [ ! -d $BACK_DIR ];then
mkdir -p $BACK_DIR
fi
for db in $DATA_LIST
do
#备份文件,备份失败则邮件预警
FILE_NAME="$BACK_DIR/${db}_${DATE}.sql.gz"
if ! mysqldump -h$HOST -u$USER -p$PASSWD -B $db |gzip > $FILE_NAME 2>/dev/null ;then
echo "mysql dump $db faile,pls check!"|mail -s "mysqldump error" $MAIL_ACC
else
echo "$db mysqldump ok!!"
fi
done
二、分表备份
USER=root
PASSWD=lxmlxm
HOST=localhost
DATE=$(date +%F-%H:%M:%S)
BAK_DIR=/data/
MAIL_ACC=1721393949@qq.com
DB_NAME=$(mysql -u$USER -h$HOST -p$PASSWD -s -e "show databases;" |egrep -v "Database|information_schema|performance_schema" 2>/dev/null)
if [ ! -d $BAK_DIR ] ;then
mkdir -p $BAK_DIR
fi
for db in $DB_NAME
do
TALBE_LIST=$(mysql -u$USER -h$HOST -p$PASSWD -s -e "use $db;show tables;")
for table in $TALBE_LIST
do
if [ ! -d $BAK_DIR/$db ] ;then
mkdir -p $BAK_DIR/$db/
fi
TABLE_FILE_NAME=${BAK_DIR}/${db}/${DATE}_${table}.sql.gz
if ! mysql -u$USER -h$HOST -p$PASSWD $db $table | gzip > $TABLE_FILE_NAME 1>/dev/null ;then
echo "$db-$table mysqldump faile" |mail -s "mysql table dump faile" $MAIL_ACC
else
echo "$db----$table dump successful!"
fi
done
done