#!/usr/bin/env bash
# 文件名: mysql_backup.sh
# 功  能: MySQL数据库备份脚本

set -o errexit
set -o nounset
set -o pipefail

### 定义变量开始
readonly DB_USER="root"
readonly DB_PASS="admin@2020"
readonly DB_HOST="127.0.0.1"
readonly DB_PORT="3306"

readonly CONN_INFO="--host=${DB_HOST} --port=${DB_PORT} --user=${DB_USER}"

### 参数说明
# --routines: 导出存储过程和自定义函数
# --triggers: 备份触发器
# --single-transaction: 先执行START TRANSACTION命令，保证备份的一致性
# --hex-blob: 使用16进制的形式导出
# --master-data=2: 注释 CHANGE MASTER语句
# --set-gtid-purged=OFF: 单纯备份为OFF,主从重做为ON
# --add-drop-database: 表示在CREATE DATABASES前先运行DROP DATABASE
# --compact: 输出结果简洁，不包含默认选项中的各种注释
# --databases: 自动加上create database
readonly DUMP_OPTS='--set-gtid-purged=OFF --routines --triggers --single-transaction --no-autocommit --hex-blob --master-data=2 --add-drop-database --compact --databases --max-allowed-packet=256M'

# 备份主目录
readonly BACKUP_DIR="/data/backup/mysql"

# 备份日志输出文件
readonly LOG_FILE="$BACKUP_DIR/backup.log"

# 备份数据保留周期（单位:天）
readonly KEEP_DAYS=7

# 时间格式
readonly TODAY="$(date +"%Y-%m-%d")"

### 定义变量结束

# 创建备份子目录
readonly MBD="$BACKUP_DIR/${TODAY}"

# 忽略备份的数据库
SKIP="information_schema
mysql
performance_schema
sys"

install -d $MBD

# 获取所有数据库名
DBS="$(export MYSQL_PWD=${DB_PASS} && mysql ${CONN_INFO} -Bse 'show databases')"

printf "%-4s %-32s %-20s %-20s\n" [+] 库名 大小 路径

for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
        for i in $SKIP
        do
                [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi

    if [ "$skipdb" == "-1" ]
    then
        # 获取数据库当前容量大小
        SIZE=$(export MYSQL_PWD=${DB_PASS} && mysql ${CONN_INFO} --silent --skip-column-names -e "SELECT CONCAT(ROUND(SUM(data_length / 1024 / 1024), 2), 'MB') FROM information_schema.tables WHERE table_schema=\"$db\";")
        FILE="$MBD/$db.sql"

        echo "SET AUTOCOMMIT=0;" > $FILE
        echo "SET UNIQUE_CHECKS=0;" >> $FILE
        echo "SET FOREIGN_KEY_CHECKS=0;" >> $FILE

        export MYSQL_PWD=${DB_PASS} && mysqldump ${CONN_INFO} ${DUMP_OPTS} ${db} >> $FILE

        echo "SET AUTOCOMMIT=1;" >> $FILE
        echo "SET UNIQUE_CHECKS=1;" >> $FILE
        echo "SET FOREIGN_KEY_CHECKS=1;" >> $FILE

        if [[ "$?" -eq "0" ]]
        then
            printf "%-4s %-30s %-18s %-20s\n" [-] ${db} ${SIZE} ${FILE}
            echo "$(basename $0) | $(date +%F" "%T) | ${db} | ${SIZE} | 备份成功!" >> ${LOG_FILE}
        else
            echo "$(basename $0) | $(date +%F" "%T) | ${db} ] 备份失败，退出!" >>${LOG_FILE}
            exit 67
        fi
    fi
done

# 压缩本次备份数据
cd $BACKUP_DIR
tar --use-compress-program=pigz -cf ${TODAY}.tar.gz ${TODAY} --remove-files || tar acvf ${TODAY}.tar.gz ${TODAY} --remove-files
if [[ "$?" -eq "0" ]]
then
    echo "$(basename $0) | $(date +%F" "%T) | [ ${TODAY}.tar.gz ] 备份压缩完成!" >>${LOG_FILE}
fi

# 删除过期备份数据
find ${BACKUP_DIR} -name $(date -d "-${KEEP_DAYS} days" +"%Y-%m-%d").tar.gz -exec rm -f {} \;