mysqldump+binlog备份脚本

发布时间：2023-05-08 13:31:16 所属栏目：MySql教程来源：

导读：## 注意各种目录创建及相关参数设置

## 设置 /root/dba/binlog_control.txt，记录binlog文件时间，获取时间命令：stat -c %y mysql_mmm_01-bin.000013

## 设置 /root/dba/fullbak_file.txt，记录全备目录，第

## 注意各种目录创建及相关参数设置

## 设置 /root/dba/binlog_control.txt，记录binlog文件时间，获取时间命令：stat -c %y mysql_mmm_01-bin.000013

## 设置 /root/dba/fullbak_file.txt，记录全备目录，第一次执行时，需设置一个目录来存放备份前产生的binlog，之后每次全备会自动更新该文件

---------------------------full_backup：MysqL_full_backup.sh----------------------------------------

#!/bin/bash

#MysqL param

user="root"

passwd="123456"

socket="/data/MysqL/run/MysqL.sock"

port=3306

bk_options="--single-transaction --flush-logs -u$user -p$passwd --master-data=2 --all-databases --socket=$socket --port=$port"

#log file

dtime=`date +%Y%m%d%H%M%s`

log_base="/root/dba/logs"

log_file="$log_base/full_backup_${dtime}.log"

#control file

contr_file="/root/dba/binlog_control.txt"

fullbak_file="/root/dba/fullbak_file.txt"

#full backup dir

bk_dir_suffix=`date +%Y%m%d`

full_dir="/backup/MysqL_bak/MysqL_$bk_dir_suffix"

[ -d ${full_dir} ] || mkdir -p ${full_dir}

echo "mkdir full backup dir : $full_dir" >> $log_file

#full backup file

full_bk_file="MysqL_full_$bk_dir_suffix.sql"

#MysqLdump

cd $full_dir

MysqLdump $bk_options > $full_bk_file

echo "full backup file : $full_bk_file" >> $log_file

#backup flush logs--binlog to prevIoUs full backup

/root/dba/MysqL_binlog_backup_new.sh

#update fullbak_file control file

echo $full_dir > $fullbak_file

---------------------------binlog_backup：MysqL_binlog_backup_new.sh-------------------------------------------------------

#!/bin/bash

#MysqL

user="root"

passwd="123456"

socket="/data/MysqL/run/MysqL.sock"

port=3306

#log file

dtime=`date +%Y%m%d%H%M%s`

log_base="/root/dba/logs"

log_file="$log_base/binlog_backup_${dtime}.log"

#control file

contr_file="/root/dba/binlog_control.txt"

fullbak_file="/root/dba/fullbak_file.txt"

#binlog path

binlog_path="/data/MysqL/binlog/"

#binlog file format

binlog_format="MysqL_mmm_01-bin.*[0-9]*"

#binlog backup dir

dest_dir=`cat $fullbak_file`

#current binlog

curr_binlog=`MysqL -u$user -p$passwd --socket=$socket --port=$port -e 'show master status\G'|grep "File"|awk '{print $2}'`

echo "Current binlog : $curr_binlog">>$log_file

#current binglog file time

currbinlog_time=`stat -c %y $binlog_path$curr_binlog`

echo "Current binlog time : $currbinlog_time">>$log_file

#lastest backup binlog file time

prevIoUs_time=`cat $contr_file`

echo "PrevIoUs time : $prevIoUs_time">>$log_file

# ! -name "*$curr_binlog*" exclude current binglog

bk_count=`find -L $binlog_path -name "$binlog_format" ! -name "*$curr_binlog*" -newermt "$prevIoUs_time" ! -newermt "$currbinlog_time"|wc -l`

echo "backup files count : $bk_count">>$log_file

if [ $bk_count -gt 0 ];then

find -L $binlog_path -name "$binlog_format" ! -name "*$curr_binlog*" -newermt "$prevIoUs_time" ! -newermt "$currbinlog_time"|xargs ls -t>>$log_file

#find the newest bakup file

last_file=`find -L $binlog_path -name "$binlog_format" ! -name "*$curr_binlog*" -newermt "$prevIoUs_time" ! -newermt "$currbinlog_time"|xargs ls -t|head -n 1`

#newest backup file time

last_file_time=`stat -c %y $last_file`

echo $last_file_time > $contr_file

#cp files

find -L $binlog_path -name "$binlog_format" ! -name "*$curr_binlog*" -newermt "$prevIoUs_time" ! -newermt "$currbinlog_time" -exec cp {} $dest_dir \;

find $log_base -mtime +15 -exec rm -f {} \;

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!