mysqldump+binlog備份腳本

## 注意 各種目錄創建及相關參數設置

## 設置 /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 {} \;