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.shmysqladmin: [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/总用量 120Kdrwxr-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.log2023-02-23 14:40:07 重新生成binlog日志2023-02-23 14:40:07 binlog日志重新生成成功2023-02-23 14:40:07 Begin backup mysql database2023-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 #指定时间段,指定库进行恢复
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
2022-02-25 zabbix调用api实现主机批量添加监控项和触发器
2022-02-25 zabbix监控mysql主从状态