#!/bin/bash
#Mysql data backup script --use mysqldump
#Auther:dongy
#Date:2013.12.16
#Version:1.2.5
#backup info
dbbackup_dir="/srv/repl_back/dbbackup"
logs_dir="/srv/repl_back/dbbackup_logs"
repl_db_dir="/srv/repl-db"
temp_dir="$logs_dir/tmp"
#time info
mins_time=$(date +%Y%m%d_%H%M)
day_time=$(date +%Y%m%d)
#log info
dump_logfile="$logs_dir/dbbackup_$day_time.log"
lost_dbs_logfile="$logs_dir/lost_dbs.log"
repl_dbs_status_logfile="$logs_dir/repl_dbs_status_${day_time}.log"
repl_dbs_error_logfile="$logs_dir/repl_dbs_error_${day_time}.log"
slave_error_sendmail_file="$logs_dir/slave_status.${day_time}.log"
slave_lost_sendmail_file="$logs_dir/slave_lost.log"
dump_result_sendmail_file="$logs_dir/dump_result_send.${day_time}.log"
#login info
remote_ip="xxx.xxx.xxx.xxx"
remote_user="xxxx"
remote_password="xxxxx"
remote_port="xxxx"
dump_user="xxxx"
dump_password="xxxx"
localip=$(curl ifconfig.me)
#Send mail info
senddate=$(date +%F_%H:%M)
connect_mail="dongy@xxxxx.com"
mail_login_name="server_alert@xxxxx.com"
mail_login_password="xxxxxxxxxx"
mail_server="smtp.qq.com"
slave_status_title="Slave_Status_Check_Result_server:$localip"
slave_backup_title="Slave_Backup_Oprate_Result_server:$localip"
#mysql connect client
myclient="/var/www/dream/mysql/bin/mysql"
#check argus
if [[ $# != 1 ]];then
echo "Usage:$0 slave_backup|slave_status"
exit 1
fi
if [[ $1 = "slave_status" ]] || [[ $1 = "slave_backup" ]];then
echo "Argus Right , Load.........."
sleep 5
else
echo "The argus was wrong,please retry"
exit 1
fi
#check backup dirs
#if [ -d $dbbackup_dir ] -a [ -d $daily_backup_dir ] -a [ -d $repl_db_dir ] -a [ -d $temp_dir ];then
#echo "dirs is exist"
#else
mkdir -p $dbbackup_dir $daily_backup_di $repl_db_dir $temp_dir
#fi
#check logs dirs
#if [] -a [] -a [] -a [] -a[]
if [[ ! -d $logs_dir ]];then
mkdir -p $logs_dir
fi
#check logs files
#if [ -f $dump_logfile ] -o [ -f $lost_dbs_logfile ] -o [ -f $repl_dbs_status_logfile ] -o [ -f $repl_dbs_error_logfile ] -o [ $slave_error_sendmail_file ] -o [ $backup_sednmail_file ] -o [ $slave_lost_sendmail_file ];then
touch $dump_logfile $lost_dbs_logfile $repl_dbs_status_logfile $repl_dbs_error_logfile $slave_error_sendmail_file $backup_sednmail_file $slave_lost_sendmail_file
# Database statements
get_gh_produ_db=$($myclient -h $remote_ip -u $remote_user -p$remote_password -P $remote_port -N -e "use azurebreeze;select distinct backupdb_name from manage_db where masterdb_ip like '%xxxipxxx%';"|awk -F ',' '{print $1}'|awk '{print $1}')
gh_produ_db_list=$(echo $get_gh_produ_db)
#database Involve count var
lost_count=0
lost_dbs=""
slave_err_dbs=""
slave_err_count=0
slave_not_create_dbs=""
slave_not_create_count=0
dump_err_dbs=""
dump_err_count=0
dump_not_create_dbs=""
dump_not_create_count=0
#functions
repl_db_status_check ()
{
echo " " >> $repl_dbs_status_logfile
echo " " >> $repl_dbs_status_logfile
echo $(date +%Y-%m-%d_%H:%M:%S) >> $repl_dbs_status_logfile
echo "<<_-_-_-_-_-_-_-_-_-_-_-_-_start check_-_-_-_-_-_-_-_-_-_-_-_->>" >> $repl_dbs_status_logfile
slave_IO_status=$($repl_db_dir/$db_full_name/bin/mysql -p -u$dump_user -p$dump_password -e "show slave status\G;"|grep "Slave_IO_Running"|awk '{print $2}')
slave_SQL_status=$($repl_db_dir/$db_full_name/bin/mysql -p -u$dump_user -p$dump_password -e "show slave status\G;"|grep "Slave_SQL_Running"|awk '{print $2}')
if [[ $slave_IO_status == "Yes" && $slave_SQL_status == "Yes" ]];then
echo "$dbname slave status is OK" >> $repl_dbs_status_logfile
elif [[ -z "$slave_IO_status" ]] || [[ -z "$slave_SQL_status" ]];then
slave_not_create_count=$(expr $slave_not_create_count + 1)
slave_not_create_dbs="$slave_not_create_dbs \t $dbname"
#echo "$dbname was not create slave" >> $repl_dbs_error_logfile
else
echo " " >> $repl_dbs_error_logfile
echo "$(date +%Y-%m-%d_%H:%M:%S)" >> $repl_dbs_error_logfile
echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>" $repl_dbs_error_logfile
$repl_db_dir/$db_full_name/bin/mysql -p -u$dump_user -p$dump_password -e "show slave status\G;" >> $repl_dbs_error_logfile
echo "<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
echo " " >> $repl_dbs_error_logfile
slave_err_count=$(expr $slave_err_count + 1)
slave_err_dbs="$slave_err_dbs $dbname"
fi
echo "<>" >> $repl_dbs_status_logfile
}
repl_db_backup ()
{
echo " " >> $dump_logfile
echo $(date +%Y-%m-%d_%H:%M:%S) >> $dump_logfile
echo "<<_-_-_-_-_-_-_-_-_-_-_-_-_start backup_-_-_-_-_-_-_-_-_-_-_-_->>" >> $dump_logfile
echo "$dbname" >> $dump_logfile
check_backupdb=$($repl_db_dir/$db_full_name/bin/mysql -u$dump_user -p$dump_password -N -e "show databases;"|grep "$dbname")
if [[ -n $check_backupdb ]];then
temp_backup_dbs=$($repl_db_dir/$db_full_name/bin/mysql -u$dump_user -p$dump_password -N -e "show databases;"|grep -v "mysql"|grep -v "test"|grep -v "information_schema")
need_backup_dbs=$(echo $temp_backup_dbs)
$repl_db_dir/$db_full_name/bin/mysql -u$dump_user -p$dump_password -e "slave stop;"
$repl_db_dir/$db_full_name/bin/mysqldump -u$dump_user -p$dump_password --opt -B $need_backup_dbs >> $everyday_backup_dir/$dbname.$mins_time.sql
if [[ $? != 0 ]];then
#dump_process="error"
echo "$dbname.$mins_time.sql.gz Dump Find Error!" >> $dump_logfile
dump_err_count=$(expr $dump_err_count + 1 )
dump_err_dbs="$dump_err_dbs $dbname"
else
echo "$dbname.$mins_time.sql.gz Operat Over" >> $dump_logfile
gzip $everyday_backup_dir/$dbname.$mins_time.sql
fi
$repl_db_dir/$db_full_name/bin/mysql -p -u$dump_user -p$dump_password -e "slave start;"
else
dump_not_create_dbs="$dump_not_create_dbs $dbname"
dump_not_create_count=$(expr $dump_not_create_count + 1 )
fi
echo "<>" >> $dump_logfile
echo " " >> $dump_logfile
}
mail_send ()
{
/usr/local/bin/sendEmail -f ${mail_login_name} -t ${connect_mail} -s ${mail_server} -u $2 -o message-file="$1" -xu $mail_login_name -xp ${mail_login_password}
}
case $1 in
"slave_status")
for dbname in $gh_produ_db_list
do
db_full_name=$(ls $repl_db_dir |grep "$dbname-")
test -z $db_full_name && continue
repl_db_status_check
done
echo "Date: $senddate Serverip: $localip " >> $slave_error_sendmail_file
echo -e " Slave Error Count: $slave_err_count" >> $slave_error_sendmail_file
echo -e " Slave Err DBS: $slave_err_dbs" >> $slave_error_sendmail_file
echo -e " Slave Not Create Count: $slave_not_create_count" >> $slave_error_sendmail_file
echo -e " Slave Not Create DBS: $slave_not_create_dbs" >> $slave_error_sendmail_file
cat $repl_dbs_error_logfile >> $slave_error_sendmail_file
mail_send $slave_error_sendmail_file $slave_status_title
mv $repl_dbs_error_logfile $temp_dir
mv $slave_error_sendmail_file $temp_dir
rm $temp_dir/*
;;
"slave_backup")
Dump_Start_Time=$(date +%Y%m%d:%H:%M)
for dbname in $gh_produ_db_list
do
daily_backup_dir="$dbbackup_dir/$dbname"
everyday_backup_dir="$daily_backup_dir/$day_time"
if [[ ! -d $everyday_backup_dir ]];then
mkdir -p $everyday_backup_dir
fi
db_full_name=$(ls $repl_db_dir |grep "$dbname-")
test -z "$db_full_name" && lost_count=$(expr $lost_count + 1 ) && lost_dbs="${lost_dbs} ${dbname}" && continue
repl_db_backup
done
Dump_End_Time=$(date +%Y%m%d:%H:%M)
echo "Date: $senddate Serverip: $localip " >> $dump_result_sendmail_file
echo "Dump_Start_Time : $Dump_Start_Time Dump_End_Time : $Dump_End_Time" >> $dump_result_sendmail_file
echo -e " Slave Database Dir No Exist Num Is : $lost_count" >> $dump_result_sendmail_file
echo -e " The Following List Database Doesn't Exist : $lost_dbs" >> $dump_result_sendmail_file
echo -e " Slave Database Backup Find Error Num Is : $dump_err_count" >> $dump_result_sendmail_file
echo -e " Slave Database Backup Error DB Is : $dump_err_dbs" >> $dump_result_sendmail_file
echo -e " Database Dir Exist But Not Create Num Is : $dump_not_create_count" >> $dump_result_sendmail_file
echo -e " Database Dir Exist But Not Create DBs IS : $dump_not_create_dbs" >> $dump_result_sendmail_file
cat $dump_logfile >> $dump_result_sendmail_file
mail_send $dump_result_sendmail_file $slave_backup_title
mv $dump_logfile $temp_dir
mv $dump_result_sendmail_file $temp_dir
rm $temp_dir/*
rsync -avP --port 52873 /srv/repl_back/dbbackup/ abc@xxx.xxx.xxx.xxx::databases-backup --password-file=/srv/repl_back/server.pass
rm -rf /srv/repl_back/dbbackup/*
;;
esac