mysql数据库备份

备份脚本：

#!/bin/bash

#备份目录
BACKUP_ROOT=/data/mysqlbak
BACKUP_FILEDIR=$BACKUP_ROOT/data

#当前日期
DATE=$(date +%Y%m%d)

######备份######

#查询所有数据库
#-uroot -p123456表示使用root账号执行命令，且root账号的密码为:123456
DATABASES=$(/usr/local/mysql5.7/bin/mysql -uroot -p密码 -e "show databases" | grep -Ev "Database|sys|information_schema|performance_schema|mysql")
#循环数据库进行备份
for db in $DATABASES
do
echo
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz BEGIN----------
/usr/local/mysql5.7/bin/mysqldump -uroot -p密码 --default-character-set=utf8 -q --lock-all-tables --flush-logs -E -R --triggers -B ${db} | gzip > $BACKUP_FILEDIR/${db}_$DATE.sql.gz
echo ----------$BACKUP_FILEDIR/${db}_$DATE.sql.gz COMPLETE----------
echo
done

echo "done"

数据库恢复

use db;
source D:/data/db_20220826.sql

清除7天前备份

#!/bin/bash
echo ----------CLEAN BEGIN----------
find /data/mysqlbak/data -mtime +7 -name "*.gz" -exec rm -rf {} \;
echo ----------CLEAN COMPLETE----------

定时任务执行

crontab -e

0 2 * * * /data/mysqlbak/scripts/backup.sh > /data/mysqlbak/logs/backup.log 2>&1
0 1 * * * /data/mysqlbak/scripts/backup_clean.sh > /data/mysqlbak/logs/backup_full_clean.log 2>&1