mysql备份脚本

#!/bin/bash

#数据库信息
DB_HOST=localhost
DB_USER=root
DB_PASS=fjks@jktj


#备份目录
BACKUP_DIR=/backup/full_backup/mysql


#当前日期
DATE=$(date -d  "1 days ago" +%Y%m%d)


#确认目录
[ -d $BACKUP_DIR  ] || mkdir -p  $BACKUP_DIR


#查询所有数据库
#DATABASES=$(mysql -uroot -p123456  -e "show databases;" | grep -Ev "Database|sys|information_schema")
#DATABASES=$(mysql -uroot -p123456 -e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('sys','mysql','information_schema','performance_schema');" | grep -v "SCHEMA_NAME","ken.io")
DATABASES=$(mysql -u${USER} -p"${DB_PASS}" -h${DB_HOST}  -e "show databases;"|grep -Ev  "Database|_schema|_bak")


#分别创建不同库的备份目录
for db_dir in $DATABASES
do
     mkdir -p ${BACKUP_DIR}/${db_dir}
done


#数据库轮询备份
for db in $DATABASES
do
    #innodb引擎备份命令，要用--master-data=2参数，需要开启log-bin和设置server-id
    #mysqldump -u$DB_USER -p$DB_PASS -h$DB_HOST  --single-transaction  --master-data=2   --events   --flush-logs  --routines  --triggers -B  ${db} | gzip > $BACKUP_DIR/${db}/${db}_$DATE.sql.gz
    sleep 1
    /www/wdlinux/mysql/bin/mysqldump -u$DB_USER -p$DB_PASS -h$DB_HOST  --single-transaction   --events    --flush-logs  --routines  --triggers -B  ${db} | gzip > $BACKUP_DIR/${db}/${db}_$DATE.sql.gz

    #多引擎混合备份命令，例如myisam,innodb混合，要用--master-data=2参数，需要开启log-bin和设置server-id
    #mysqldump -u$DB_USER -p$DB_PASS -h$DB_HOST  --master-data=2   --events  --ignore-table=mysql.events   --flush-logs  --routines  --triggers -B  ${db} | gzip > $BACKUP_DIR/${db}/${db}_$DATE.sql.gz
done

#清理60天以前的备份
find $BACKUP_DIR -type f  -mtime +60 -name "*.gz" -exec rm -f {} \;