百木园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
本站部分图文来源于网络，如有侵权请联系删除。