一、查看内存选择系统盘防止内存不足备份失败
[root@iZ7ns71hl9rkcwZ scripts]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 79G 6.2G 69G 9% /
devtmpfs 16G 0 16G 0% /dev
tmpfs 16G 0 16G 0% /dev/shm
tmpfs 16G 448K 16G 1% /run
tmpfs 16G 0 16G 0% /sys/fs/cgroup
tmpfs 3.1G 0 3.1G 0% /run/user/0
二、创建文件存储目录
mkdir -p /data/mysqlbak/data
mkdir -p /data/mysqlbak/scripts
mkdir -p /data/mysqlbak/logs
三、创建自动备份脚本执行文件
cd /data/mysqlbak/scripts
vi backup.sh
#!/bin/bash
#备份目录
BACKUP_ROOT=/data/mysqlbak
BACKUP_FILEDIR=$BACKUP_ROOT/data
#当前日期
DATE=$(date +%Y%m%d)
######备份######
#查询所有数据库
#-uroot -p123456表示使用root账号执行命令,且root账号的密码为:123456
DATABASES=$(mysql -uroot -p123456 -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----------
#-uroot -p123456表示使用root账号执行命令,且root账号的密码为:123456
mysqldump -uroot -p123456 --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"
四、赋予脚本执行权限
chmod 777 backup.sh
五、将备份操作加入到定时任务中(每天凌晨2点执行)
crontab -e
00 2 * * * /data/mysqlbak/scripts/backup.sh > data/mysqlbak/logs/backup.log 2>&1
六、创建自动删除备份脚本文件(防止备份文件过大占用内存,7天删除一次)
vi backup_clean.sh
#!/bin/bash
echo ----------CLEAN BEGIN----------
find /data/mysqlbak/data -mtime +7 -name "*.gz" -exec rm -rf {} \;
echo ----------CLEAN COMPLETE----------
七、赋予删除脚本执行权限
chmod 777 backup_clean.sh
八、将删除操作加入到定时任务中(每天凌晨1点执行)
00 1 * * * /data/mysqlbak/scripts/backup_clean.sh > /data/mysqlbak/logs/backup_full_clean.log 2>&1
九、查看定时任务
crontab -l
查看结果:
[root@iZ7ns71hl9rkcwZ scripts]# crontab -l
00 2 * * * /data/mysqlbak/scripts/backup.sh > data/mysqlbak/logs/backup.log 2>&1
00 1 * * * /data/mysqlbak/scripts/backup_clean.sh > /data/mysqlbak/logs/backup_full_clean.log 2>&1
十、验证脚本是否有效 出现以下结果表示没问题
[root@iZ7ns71hl9rkcwZ scripts]# ./backup.sh
----------/data/mysqlbak/data/base_resource_20201106.sql.gz BEGIN----------
----------/data/mysqlbak/data/base_resource_20201106.sql.gz COMPLETE----------
----------/data/mysqlbak/data/check_db_20201106.sql.gz BEGIN----------
----------/data/mysqlbak/data/check_db_20201106.sql.gz COMPLETE----------
----------/data/mysqlbak/data/equipment_db_20201106.sql.gz BEGIN----------
----------/data/mysqlbak/data/equipment_db_20201106.sql.gz COMPLETE----------
----------/data/mysqlbak/data/qualitycontrol_20201106.sql.gz BEGIN----------
----------/data/mysqlbak/data/qualitycontrol_20201106.sql.gz COMPLETE----------
done
十一、或者备份至另外一台服务器,避免数据库和脚本一起丢失
1、首先服务器需要安装export,yum安装:
yum install expect
2、修改上面的脚本文件
#!/bin/bash
#备份目录
BACKUP_ROOT=/data/mysqlbak
BACKUP_FILEDIR=$BACKUP_ROOT/data
#当前日期
DATE=$(date +%Y%m%d)
######备份######
#查询所有数据库
#-uroot -p123456表示使用root账号执行命令,且root账号的密码为:123456
DATABASES=$(mysql -uroot -p123456 -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----------
mysqldump -uroot -p123456 --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 ----------scp 226 begin----------
expect -c "
spawn scp -r /data/mysqlbak/data/${db}_$DATE.sql.gz root@xxx.xxx.xxx.226:/data/mysqlbak/data225/
expect {
\"*assword\" {set timeout 300; send \"此处是scp的密码\r\"; exp_continue;}
\"yes/no\" {send \"yes\r\";}
}
expect eof"
echo ----------scp 226 end----------
echo
done
echo "done"
或者源码安装,参考 https://www.cnblogs.com/operationhome/p/9154055.html