#!/bin/sh
#備份主機
remote_ip=10.2.142.161
Master_ip=10.2.142.148
VIP=103.2.132.136
#備份用戶
user='root'
#密碼
password='123456'
# 返回年月日
backup_date=`date +%F`
# 返回時分秒
backup_time=`date +%H-%M-%S`
# 返回今天是這周的第幾天
backup_week_day=`date +%u`
backup_ok=0
#備份目錄
socket=/data/mysql/mysql.sock
# 備份路徑
backup_dir=/data/xtrabackup
backup_dir_local=/data/xtrabackup_local
# 數據目錄
datadir=/data/mysql
# percona-xtrabackup 備份軟件路徑
xtrabackup_dir=/usr/bin
# 全備是在一周的第幾天
full_backup_week_day=6
#周期性全量增量開始日期# 全量備信息名稱前綴
full_backup_prefix=full
# 增量備信息名稱前綴
increment_prefix=incr
# mysql配置文件
mysql_conf_file=/etc/my.cnfcycle=$backup_date
cycle_record=$backup_dir/cycle_record.txtif [ ! -f $backup_dir/cycle_record.txt ];thenindex=$backup_date
elseif [ "$full_backup_week_day" -eq `date +%u` ]; thenindex=$backup_dateelseindex=`cat $backup_dir/cycle_record.txt`fi
fi
index_file=$backup_dir/backup_$index.index
index_file_local=$backup_dir_local/backup_$index.indexlog_dir=$backup_dir/log
if [ ! -d "$backup_dir" ];thenmkdir -p $backup_dir
fi
if [ ! -d "$backup_dir_local" ];thenmkdir -p $backup_dir_local
fi
if [ ! -d "$log_dir" ];thenmkdir -p $log_dir
fifunction append_index_to_file() {echo "{week_day:$backup_week_day, \dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \type:${1}, \date:${backup_date}}" >> $index_file
}
function append_index_to_file_local() {echo "{week_day:$backup_week_day, \dir:${1}_${backup_date}_${backup_time}_${backup_week_day}, \type:${1}, \date:${backup_date}}" >> $index_file_local
}
# 判斷是應該全備還是增量備份
# 0:full, 1:incr
function get_backup_type() {full_backup_week_day=$full_backup_week_daybackup_type=0if [ ! -f "$index_file" ]; thentouch "$index_file"fiif [ "$full_backup_week_day" -eq `date +%u` ]; thenbackup_type=0elsebackup_type=1fiif [ ! -n "`cat $index_file`" ]; thenbackup_type=0fireturn $backup_type
}
#推送遠程全量備份
function full_backup (){backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}if [ ! -d $backup_dir_local/$cycle/ ]; thenmkdir $backup_dir/$cycle/fiecho $cycle>$backup_dir/cycle_record.txtssh $user@$remote_ip  "if [ ! -d $backup_dir/$cycle/ ];then mkdir -p $backup_dir/$cycle; fi "innobackupex --defaults-file=$mysql_conf_file --no-timestamp --user=$user \--password=$password --host=${VIP} --port=3306 --extra-lsndir=$backup_dir/$cycle/$backup_folder  --compress \--stream=xbstream $backup_dir  | ssh $user@$remote_ip  "gzip ->$backup_dir/$cycle/$backup_folder.tar.gz" if [ $? -eq 0 ];thenappend_index_to_file  $full_backup_prefixlog_info 0 fullelselog_info 1 fullfi
}#推送遠程增量備份{week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr}
incremental (){backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}incr_record=`cat $backup_dir/cycle_record.txt`echo $backup_dir/$incr_record#cd $backup_dir/$incr_recordincr_base_folder=`sed -n '$p' $index_file | \awk -F '[, {}]*' '{print $3}' | \awk -F ':' '{print $2}'`echo $backup_dir/$incr_record/${incr_base_folder}innobackupex --defaults-file=$mysql_conf_file \--no-timestamp --user=$user --password=$password --host=${VIP} --port=3306  \--stream=xbstream --compress --extra-lsndir=$backup_dir/$incr_record/$backup_folder \--incremental backup_folder --incremental-basedir=$backup_dir/$incr_record/${incr_base_folder} \|ssh $user@$remote_ip  "gzip ->$backup_dir/$incr_record/$backup_folder.tar.gz" \if [ $? -eq 0 ];thenlog_info 0 incrappend_index_to_file  $increment_prefixelselog_info 1 incrfi
}function full_backup_local (){backup_folder=${full_backup_prefix}_${backup_date}_${backup_time}_${backup_week_day}if [ ! -d $backup_dir_local/$cycle/ ]; thenmkdir $backup_dir_local/$cycle/fiecho $cycle>$backup_dir_local/cycle_record.txtinnobackupex --defaults-file=$mysql_conf_file --no-timestamp --user=$user \--password=$password --host=${VIP} --port=3306 $backup_dir_local/$cycle/$backup_folderif [ $? -eq 0 ];thenecho "全量備份成功"append_index_to_file_local  $full_backup_prefixlog_info_local 0 fullelselog_info_local 1 fullfi
}#本地增量備份{week_day:1,dir:full/incr_2015-12-29_00-00-00_7,type:full/incr}
incremental_local (){backup_folder=${increment_prefix}_${backup_date}_${backup_time}_${backup_week_day}incr_record=`cat $backup_dir_local/cycle_record.txt`echo $backup_dir_local/$incr_record#cd $backup_dir/$incr_recordincr_base_folder=`sed -n '$p' $index_file_local | \awk -F '[, {}]*' '{print $3}' | \awk -F ':' '{print $2}'`echo  $incr_base_folderecho $backup_dir_local/$incr_record/${incr_base_folder}innobackupex --defaults-file=$mysql_conf_file \--no-timestamp --user=$user --password=$password --host=${VIP} --port=3306  \--incremental $backup_dir_local/$incr_record/$backup_folder --incremental-basedir=$backup_dir_local/$incr_record/$incr_base_folder   if [ $? -eq 0 ];thenlog_info_local 0 incrappend_index_to_file_local  $increment_prefixelselog_info_local 1 incrfi
}
function log_info (){
if [[ "$1" = "0" ]];thenif [ "$2" = "full" ];thenecho "全量備份成功" >$backup_dir/successelseecho "增量備份成功" >$backup_dir/failfi
elseif [ "$2" = "full" ];thenecho "全量備份失敗" >$backup_dir/successelseecho "增量備份失敗" >$backup_dir/failfi
fi
}
function log_info_local ()
{
echo "全量備份成功"
if [[ "$1" = "0" ]];thenif [ "$2" = "full" ];thenecho "全量備份成功" >$backup_dir/successelseecho "增量備份成功" >$backup_dir/failfi
elseif [ "$2" = "full" ];thenecho "全量備份失敗" >$backup_dir/successelseecho "增量備份失敗" >$backup_dir/failfi
fi
}
function run_auto() {get_backup_typebackup_type=$?echo $backup_typecase $backup_type in0)full_backup			   ;;1)incremental			     ;;*)echo "Please  use it this way. Usage:$0 {0|1}";;esac  
}
function run_manual() {case $1 infull)full_backup;;incremental)incremental;;*)echo "Please  use it this way. Usage:$0 {Full|incremental}";;esac 
}
function run_auto_local() {get_backup_typebackup_type=$?case backup_type in0)full_backup_local;;1)incremental_local;;*)echo "Please  use it this way. Usage:$0 {Full|incremental}";;esac  
}
function run_manual_local() {#echo $1case $1 infull)full_backup_local;;incremental)incremental_local;;*)echo "Please  use it this way. Usage:$0 {full|incremental}";;esac 
}
run_auto
#run_auto_local
#run_manual_local $1
#run_manual $1

What’s the problem with FTWRL anyway?
A lot has been written on what FLUSH TABLES WITH READ LOCK really does. Here’s yet another walk-through in a bit more detail than described elsewhere:It first invalidates the Query Cache.
It then waits for all in-flight updates to complete and at the same time it blocks all incoming updates. This is one problem for busy servers.
It then closes all open tables (the FLUSH part) and expels them from the table cache. This is also whenFTWRL has to wait for all SELECT queries to complete. And this is another, even bigger problem for busy servers, because that wait happens to occur with all updates blocked. What’s even worse, the server at this stage is essentially offline, because even incoming SELECT queries will get blocked.
Finally, it blocks COMMITs.
1.它首先使查詢緩存無效。
2.然后，等待所有更新完成，并在同一時間，它阻止所有的更新。這對于繁忙的服務器是一個問題。
3.然后，它關閉所有打開的表（沖洗表）并且從表中的高速緩存刷出。這也是當FTWRL必須等待所有的SELECT查詢完成。這是另一個更大的問題為繁忙的服務器，因為等待恰好發生這將堵塞所有的更新。更糟的是，服務器在這個階段基本上是離線狀態，因為即使進入的SELECT查詢將被封鎖。
4.最后，它會阻止事務提交。Percona已經提供了解決方案（2.1版本開始）：設置一個超時時間，避免無限期的等待。Xtrabackup提供了以下參數實現該功能：--lock-wait-timeout=SECONDS, ，一旦Flush table with read lock被阻塞超過預定時間，則XtraBackup出錯返回退出，該值默認為0，也就是說一旦阻塞，立即返回失敗。
--lock-wait-query-type=all|update，該參數允許用戶指定，哪類的SQL語句是需要Flush table with read lock等待的，同時用戶可以通過--lock-wait-threshold=SECONDS設置等待的時間，如果不在query-type指定的類型范圍內或者超過了wait-threshold指定的時間，XtraBackup均返回錯誤。如果指定update類型，則UPDATE/ALTER/REPLACE/INSERT 均會等待，ALL表示所有的SQL語句。kill 其他阻塞線程
Kill掉所有阻塞Flush table with read lock的線程：
--kill-long-queries-timeout=SECONDS參數允許用戶指定了超過該閾值時間的查詢會被Kill，同時也允許用戶指定KillSQL語句的類型。
--kill-long-query-type=all|select
默認值為ALL，如果選擇Select，只有Select語句會被Kill，如果Flush table with read lock是被Update語句阻塞，則XtraBackup不會處理。