mysqldump+binlog备份脚本

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

网站的建设创新互联公司专注网站定制,经验丰富,不做模板,主营网站定制开发.小程序定制开发,H5页面制作!给你焕然一新的设计体验!已为报废汽车回收等企业提供专业服务。

## 设置 /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 \;

fi

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