#!/bin/bash

# 此脚本为数据库自动备份脚本 可以配合# crontab -e定时任务
# Basic configuration: datestamp e.g. YYYYMMDD
# 创建文件的时间

DATE=$(date +"%Y%m%d")

# Location of your backups (create the directory first!)
# 备份目录

BACKUP_DIR="/data/backup/mysql"

# MySQL login details
# 数据库用户名密码

MYSQL_USER="Monitor1"
MYSQL_PASSWORD='Monitor2020'

# MySQL executable locations (no need to change this)
# 数据库命令及工具命令位置 此位置为编译安装

MYSQL=/usr/local/mysql/bin/mysql
MYSQLDUMP=/usr/local/mysql/bin/mysqldump

# MySQL databases you wish to skip
# 除了哪些数据库不备份 剩余都会备份

SKIPDATABASES="Database|information_schema|performance_schema|mysql|test"

# Number of days to keep the directories (older than X days will be removed)
# 删除以前数据的天数

RETENTION=60

# ---- DO NOT CHANGE BELOW THIS LINE ------------------------------------------
#
# Create a new directory into backup directory location for this date

mkdir -p $BACKUP_DIR/$DATE

# Retrieve a list of all databases

databases=`$MYSQL -u$MYSQL_USER -p$MYSQL_PASSWORD -e "SHOW DATABASES;" | grep -Ev "($SKIPDATABASES)"`

# Dumb the databases in seperate names and gzip the .sql file

for db in $databases; do
echo $db
$MYSQLDUMP --force --opt --user=$MYSQL_USER -p$MYSQL_PASSWORD --skip-lock-tables --events --databases $db | gzip > "$BACKUP_DIR/$DATE/$db.sql.gz"
done

# Remove files older than X days

find $BACKUP_DIR/* -mtime +$RETENTION -delete
