mysql逻辑备份脚本参考

备份需求：

linux环境下指定库备份保留7天，每天备份3次

[root@db mysql_backup]# cat /etc/redhat-release 
CentOS release 6.9 (Final)

备份脚本如下：

[root@db ~]# more /data/bin/mysql_backup.sh
#!/bin/bash
#设置mysql备份目录
#folder= /data/backups/mysql_backup/
cd /data/backups/mysql_backup
#cd $folder
day=`date +%Y%m%d`
now2=`date +%H%M%S`

mkdir $day
cd $day

#数据库服务器，一般为localhost
host=localhost
#用户名
user=root
#密码
password=xxxxxx

#要备份的数据库
db_name=mydb
#如果有多个库，就参考设置不同的变量，比如db_name02=mydb02

#mysql命令行
#mysql_bin = /usr/local/mysql/bin

#数据要保留的天数
days=7

/usr/local/mysql/bin/mysqldump -h$host -u$user -p$password $db_name>backup_$db_name.sql

tar zcvf backup_$day$now2.sql.tar.gz backup_$db_name.sql
rm backup_$db_name.sql
cd /data/backups/mysql_backup
day=`date -d "$days days ago" +%Y%m%d`
rm -rf $day

设置定时任务

[root@db ~]# crontab -l
#例如每三小时备份数据
0 */3 * * * sh /data/bin/mysql_backup.sh

备份结果如下：

#备份结果检查：
[root@db ~]# cd /data/backups/mysql_backup
[root@db mysql_backup]# ls -lt|head
total 56
drwxr-xr-x 2 root root 4096 Nov 23 12:00 20231123
drwxr-xr-x 2 root root 4096 Nov 22 21:00 20231122
drwxr-xr-x 2 root root 4096 Nov 21 21:00 20231121
drwxr-xr-x 2 root root 4096 Nov 20 21:00 20231120
drwxr-xr-x 2 root root 4096 Nov 19 21:00 20231119
drwxr-xr-x 2 root root 4096 Nov 18 21:00 20231118
drwxr-xr-x 2 root root 4096 Nov 17 21:00 20231117

[root@roohl mysql_backup]# cd 20231123
[root@roohl 20231123]# ll
total 118272
-rw-r--r-- 1 root root 24219559 Nov 23 00:00 backup_20231123000001.sql.tar.gz
-rw-r--r-- 1 root root 24219803 Nov 23 03:00 backup_20231123030001.sql.tar.gz
-rw-r--r-- 1 root root 24219813 Nov 23 06:00 backup_20231123060001.sql.tar.gz
-rw-r--r-- 1 root root 24219674 Nov 23 09:00 backup_20231123090001.sql.tar.gz
-rw-r--r-- 1 root root 24218749 Nov 23 12:00 backup_20231123120001.sql.tar.gz
[root@db mysql_backup]# du -sm /data/backups/mysql_backup/*
185     /data/backups/mysql_backup/20231117
185     /data/backups/mysql_backup/20231118
185     /data/backups/mysql_backup/20231119
185     /data/backups/mysql_backup/20231120
185     /data/backups/mysql_backup/20231121
185     /data/backups/mysql_backup/20231122
116     /data/backups/mysql_backup/20231123
[root@db mysql_backup]# 

小结：

备份脚本和保留策略需根据磁盘空间及恢复要求来设定，灵活修改脚本，测试正常即可。
windows下备份任务参考：
https://blog.csdn.net/weixin_42464282/article/details/130990988