21运维原创，转载请注明出处。
曾经之前也有写过类似mysql全量和增量备份脚本，不过过于简单。昨天晚上给公司线上服务器写了个针对mysql5.7的，相对来讲更为优化的一个实例。
注：这个脚本仅仅适合小型数据库，不适合中大型数据库。毕竟是逻辑备份方式，数据库过大导入会比较慢，建议采用innobackupex方式备份，包括我自己服务器也是采用的innobackupex。
全量备份脚本：
#!/bin/bash
#bak mysql all
# v1.0
#2016.8.4
TIME=`date +%Y%m%d_%H%M%S`
LOGFILE="/home/mysqlbak/mysql_full_bak_time.log"
MYSQL_BAKDIR="/home/mysqlbak/full$TIME"
USER=root
PASSWORD=xxxxxx
[ ! -d $MYSQL_BAKDIR ] && mkdir -p $MYSQL_BAKDIR
[ ! -e $LOGFILE ] && touch $LOGFILE
DATABASE=`mysqlshow -u$USER -p$PASSWORD | grep -Ev "Data|info|per|sys" | grep -v + | awk '{print $2}'`
for bakmysqlname in $DATABASE
do
mysqldump -u$USER -p$PASSWORD $bakmysqlname --skip-lock-tables > $MYSQL_BAKDIR/$TIME_$bakmysqlname.sql
tar zcf $MYSQL_BAKDIR/$TIME_$bakmysqlname.tar.gz $MYSQL_BAKDIR/$TIME_$bakmysqlname.sql --remove-files &> /dev/null
done
ret=$?
FINISHTIME=`date +%Y%m%d_%H%M%S`
[ $ret -eq 0 ] && echo "Mysql fullbakup finished, time: $FINISHTIME">> $LOGFILE || echo "Mysql fullbakup failed,please check time:$FINISHTIME" >> $LOGFILE
增量备份脚本：
#!/bin/bash #bak incr for mysql #2016.8.4 TIME=`date +%Y%m%d_%H%M%S` DATADIR=/data/mysql LOGFILE="/home/mysqlbak/mysql_incr__bak_time.log" USER=root PASSWORD=xxxxxx MYSQL_BAKDIR=/home/mysqlbak/incr$TIME [ ! -d $MYSQL_BAKDIR ] && mkdir -p $MYSQL_BAKDIR [ ! -e $LOGFILE ] && touch $LOGFILE mysqladmin -u$USER -p$PASSWORD flush-logs TOTAL=`ls $DATADIR/mysql-bin.* |wc -l` TOTAL=`expr $TOTAL - 2` for filetemp in `ls $DATADIR/mysql-bin.*|head -n $TOTAL` do incrfilename=`basename $filetemp` cp $filetemp $MYSQL_BAKDIR/$incrfilename tar zcf $MYSQL_BAKDIR/$incrfilename.tar.gz $MYSQL_BAKDIR/$incrfilename --remove-files &> /dev/null done ret=$? FINISHTIME=`date +%Y%m%d_%H%M%S` [ $ret -eq 0 ] && echo " Mysql incr bak finished,time: $FINISHTIME">> $LOGFILE || echo " Mysql incr bak failed,please check.time: $FINISHTIME">> $LOGFILE