MySQL5.7分库常用备份脚本

一、MySQL5.7分库常用备份脚本

排除一些系统数据库，备份完成之后发送邮件，邮件发送配置方法参见：https://www.cnblogs.com/cyleon/p/10250092.html

# cat /data/script/mysql_bak.sh
#!/bin/bash
USER_NAME=root
USER_PASSWD=password
backup=/data/backupdata/mysql
date=`date +%Y-%m-%d`
[ ! -d $backup ] && mkdir -p $backup
mkdir -p ${backup}/${date}
date1=`date +%Y.%m.%d-%H:%M:%S`
echo "Start time: $date1 Path: ${backup}/${date}" > ${backup}/mysqlbak.log
iplist="192.168.56.5 192.168.56.6"
for i in $iplist;
do
  for dbname in `/bin/mysql -u $USER_NAME -p$USER_PASSWD -h $i -e "show databases;"| sed 1d | egrep -v "schema|test|mysql|sys|*_bak"`
  do
    /bin/mysqldump -u $USER_NAME -p$USER_PASSWD -h $i -P 3306 --skip-lock-tables --hex-blob --set-gtid-purged=OFF -R --triggers $dbname | gzip > ${backup}/${date}/${i}_${dbname}.$date.sql.gz
    date1=`date +%Y.%m.%d-%H:%M:%S`
    echo "$dbname   | size: `du -sh ${backup}/$date/${i}_${dbname}.$date.sql.gz | awk '{print $1}' ` | Completion time: $date1 " >> ${backup}/mysqlbak.log
  done
done
/bin/mutt  -s "mysql backup log" xxx@163.com < /data/backupdata/mysql/mysqlbak.log

# crontab -l
0 1 * * * sh /data/script/mysql_bak.sh >/dev/null 2>&1