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` eti ...
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 #指定時間段，指定庫進行恢復
