#!/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 {} \;
mysql备份脚本
最新推荐文章于 2024-04-12 17:26:09 发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)