mysql增量备份脚本

达了个蛋 · 发表于 2023-2-25 17:43:22

2、增量备份

2.1、添加备份脚本

[root@localhost]# vim /mnt/data/backup/mysql/mysql_m_bak_diff.sh

复制代码

#!/bin/bash
#mysql 增量备份
time=`date +%Y%m%d`
now=`date +%F' '%T`
etime=`date -d '-7 day' +%Y%m%d`
#备份文件路径
mkdir -p /mnt/data/backup/mysql/diff_back/$time
mkdir -p /mnt/data/backup/mysql/diff_back/$time/log
backupdir="/mnt/data/backup/mysql/diff_back/$time"
log_dir="/mnt/data/backup/mysql/diff_back/$time/log"
#bin-log日志文件路径
BinFile="/mnt/data/mysql/logs/"
user=root
passwd=123456echo "$now 重新生成binlog日志" >> $log_dir/$time.log
/mnt/data/mysql/bin/mysqladmin -P3308 -u$user -p$passwd --socket=/mnt/data/mysql/mysql.sock flush-logs
if [ $? -eq 0 ];then
echo "$now binlog日志重新生成成功" >> $log_dir/$time.log
echo "$now Begin backup mysql database" >> $log_dir/$time.log
#查找binlog日志
new_blog=`/mnt/data/mysql/bin/mysql -P3308 -u$user -p$passwd --socket=/mnt/data/mysql/mysql.sock -e "show master status\G" |grep 'File'|awk '{print $2}'`
num=`/mnt/data/mysql/bin/mysql -P3308 -u$user -p$passwd --socket=/mnt/data/mysql/mysql.sock -e "show master status\G" |grep 'File'|awk '{print $2}'|awk -F '.' '{print $2}'`
next_num=`expr $num - 1`
old_blog=`find $BinFile -name "mysql-bin.*$next_num"`
#备份文件
\cp $old_blog $backupdir/
old_blog_n=`find $backupdir -name "mysql-bin.*$next_num"`
mv $old_blog_n $backupdir/master_bak_$time.sql
if [ $? -eq 0 ];then
echo "$now mysql copy successfully " >> $log_dir/$time.log
echo "$now 开始执行压缩......" >> $log_dir/$time.log
tar -zcvf $backupdir/master_bak_$time.tar.gz -C $backupdir master_bak_$time.sql
if [ $? -eq 0 ];then
echo "$now 文件压缩成功......" >> $log_dir/$time.log
rm -rf $backupdir/master_bak_$time.sql
size=`du -sh $backupdir/master_bak_$time.tar.gz |awk '{print $1}'`
echo "$now backup mysql database successfully completed" >> $log_dir/$time.log
echo "$now deploy-主数据库数据备份成功——增量备份,文件大小为:$size\n 二进制文件为: $old_blog_n" > /tmp/tmp.log
rm -rf /tmp/tmp.log
else
echo "$now 文件压缩失败......" >> $log_dir/$time.log
echo "$now deploy-主数据库数据备份成功__增量备份,但文件压缩失败,\n 二进制文件为: $old_blog_n " > /tmp/tmp.log
rm -rf /tmp/tmp.log
fi
else
echo "$now backup mysql database faild" >> $log_dir/$time.log
echo "$now deploy-主数据库数据备份失败——增量备份,请检查mysql是否正常" > /tmp/tmp.log
rm -rf /tmp/tmp.log
fi
else
echo "$now binlog日志重新生成失败" >> $log_dir/$time.log
echo "$now deploy-主数据库数据备份失败__差量备份,刷新binlog日志失败" > /tmp/tmp.log
rm -rf /tmp/tmp.log
fi

复制代码

2.2、添加执行权限

[root@localhost ~]# chmod +x /mnt/data/backup/mysql/mysql_m_bak_diff.sh

复制代码

2.3、添加计划任务

[root@localhost ~]# crontab -e #添加内容，每天2点执行 0 2 * * * sh /mnt/data/backup/mysql/mysql_m_bak_diff.sh #wq保存退出

复制代码

2.4、手动备份

[root@localhost ~]# sh /mnt/data/backup/mysql/mysql_m_bak_diff.sh mysqladmin: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. mysql: [Warning] Using a password on the command line interface can be insecure. master_bak_20230223.sql

复制代码

2.5、查看备份文件

[root@localhost ~]# ll -h /mnt/data/backup/mysql/diff_back/20230223/ 总用量 120K drwxr-xr-x. 2 root root 26 2月 23 14:22 log -rw-r--r--. 1 root root 119K 2月 23 14:40 master_bak_20230223.tar.gz [root@ops deploy]# cat /mnt/data/backup/mysql/diff_back/20230223/log/20230223.log 2023-02-23 14:40:07 重新生成binlog日志 2023-02-23 14:40:07 binlog日志重新生成成功 2023-02-23 14:40:07 Begin backup mysql database 2023-02-23 14:40:07 mysql copy successfully 2023-02-23 14:40:07 开始执行压缩...... 2023-02-23 14:40:07 文件压缩成功...... 2023-02-23 14:40:07 backup mysql database successfully completed

复制代码

2.6、增量数据恢复

[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime="2022-10-31 17:53:00" --stop-datetime="2022-11-01 16:18:00" --database=dbname mysql-bin.000073 | mysql -uroot -p123456 #指定时间段，指定库进行恢复

复制代码

来源:https://www.cnblogs.com/xgsh/p/17153883.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】我们会及时删除侵权内容，谢谢合作！