mysql 脚本批量_mysql批量备份脚本（自用）

#!/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