#!/usr/bin/env bash


# 参数配置
SERVHOST='10.1.1.64'
SERVPORT='3306'
MYSQL_USER="myuser"
MYSQL=/usr/bin/mysql
MYSQL_PASSWORD="helloPasswd"
MYSQLDUMP=/usr/bin/mysqldump

# 全库备份
BAK_PATH=/data/bak_db/online_db_all/`date '+%Y%m'`/`date '+%Y%m%d'`
mkdir -pv ${BAK_PATH}

# 备份完整数据库到一个文件
$MYSQLDUMP -h $SERVHOST -u $MYSQL_USER  -P$SERVPORT -p$MYSQL_PASSWORD --all-databases | zip ${BAK_PATH}/`date "+%Y%m%d-%H%M%S"`.all.sql.zip -

# 分文件备份数据库，方便还原单个数据库的数据
databases=`$MYSQL -h $SERVHOST -u $MYSQL_USER -P$SERVPORT -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema)"`
for db in $databases; do
  BACK_FILENAME=${BAK_PATH}/`date "+%Y%m%d-%H%M%S"`.$db.sql.zip
  echo "["`date`"] " "备份:" $db " 开始"
  $MYSQLDUMP --force --opt -h $SERVHOST -u $MYSQL_USER -P$SERVPORT -p$MYSQL_PASSWORD --databases $db | zip ${BACK_FILENAME} -
  echo "备份路径: " $BACK_FILENAME
  echo "["`date`"] " "备份:" $db " 结束"
  echo ""
done