生产服务器数据库备份脚本

[root@dbslave01 dbscripts]# cat dbbackup
###################################################################
#######mysqldump###################################################
#!/bin/sh
#verison 0.1
#by ht
# mysql_backup.sh: backup mysql databases and keep newest 2 days backup.
# -----------------------------
db_user="root"
db_passwd=""
db_host="127.0.0.1"

# the directory for story your backup file.

db_backup_log="/bak/db_bak_log"
backup_dir_huaiyun="/bak/dbbackup/huaiyun"
backup_dir_jtt="/bak/dbbackup/jtt"
backup_dir_xfjtt="/bak/dbbackup/xfjtt"
backup_dir_qc="/bak/dbbackup/qc"
backup_dir_risk="/bak/dbbackup/risk"
backup_dir_rasgj="/bak/dbbackup/rasgj"
backup_dir_witech="/bak/dbbackup/witech"
backup_dir_mysql="/bak/dbbackup/mysql"

des_backup_dir_huaiyun=""
des_backup_dir_jtt=""
des_backup_dir_xfjtt=""
des_backup_dir_qc=""
des_backup_dir_risk=""
des_backup_dir_rasgj=""
des_backup_dir_witech=""
des_backup_dir_mysql=""

#definition of e-mail variables
error_host=$(ifconfig eth0 | grep inet | awk '{print $2}' | sed -e 's/addr\://')
email_in_address=""
email_out_address=""
email_user="hyx12ss"
email_pass="qwe123"
email_option="-o message-content-type=html -o message-charset=utf8"
email_platform="smtp.163.com"

# date format for backup file (dd-mm-yyyy)
#time="$(date +"%d-%m-%Y")"
time="$(date +"%F")"

 

# mysql, mysqldump and some other bin's path
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
MKDIR="$(which mkdir)"
RM="$(which rm)"
MV="$(which mv)"
GZIP="$(which gzip)"
SCP="$(which scp)"
SCP_OPTION="-P7555 -r"

# check the directory for store backup is writeable
test ! -w $backup_dir_huaiyun && echo "Error: $backup_dir_huaiyun is un-writeable." && exit 0
test ! -w $backup_dir_jtt && echo "Error: $backup_dir_jtt is un-writeable." && exit 0
test ! -w $backup_dir_xfjtt && echo "Error: $backup_dir_xfjtt is un-writeable." && exit 0
test ! -w $backup_dir_qc && echo "Error: $backup_dir_qc is un-writeable." && exit 0
test ! -w $backup_dir_risk && echo "Error: $backup_dir_risk is un-writeable." && exit 0
test ! -w $backup_dir_rasgj && echo "Error: $backup_dir_rasgj is un-writeable." && exit 0
test ! -w $backup_dir_witech && echo "Error: $backup_dir_witech is un-writeable." && exit 0
test ! -w $backup_dir_mysql && echo "Error: $backup_dir_mysql is un-writeable." && exit 0

# Test whether the directory already exists
test ! -d "$backup_dir_huaiyun" && echo "Error $backup_dir_huaiyun is Already exists"
test ! -d "$backup_dir_jtt" && echo "Error $backup_dir_jtt is Already exists"
test ! -d "$backup_dir_xfjtt" && echo "Error $backup_dir_xfjtt is Already exists"
test ! -d "$backup_dir_qc" && echo "Error $backup_dir_qc is Already exists"
test ! -d "$backup_dir_risk" && echo "Error $backup_dir_risk is Already exists"
test ! -d "$backup_dir_rasgj" && echo "Error $backup_dir_rasgi is Already exists"
test ! -d "$backup_dir_witech" && echo "Error $backup_dir_witech is Already exists"
test ! -d "$backup_dir_mysql" && echo "Error $backup_dir_mysql is Already exists"

# get all databases

sleep 5
for db in huaiyun huaiyun_bbs huaiyun_blog huaiyun_home
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_huaiyun/$time.$db.sql.gz"
       $SCP $SCP_OPTION  "$backup_dir_huaiyun/$time.$db.sql.gz"         $des_backup_dir_huaiyun
       if [[ $? == 0 ]];then
       echo "$time db huaiyun  succed"  >>  $db_backup_log/huaiyun_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host huaiyun_db bak faliure"
fi
done

sleep 5
for db in jiatingtong jtt_bbs jtt_home jtt_tiyanban
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_jtt/$time.$db.sql.gz"
       $SCP $SCP_OPTION  "$backup_dir_jtt/$time.$db.sql.gz"  $des_backup_dir_jtt
       if [[ $? == 0 ]];then
       echo " $time  db jtt  succed"  >>  $db_backup_log/jtt_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host jtt_db bak faliure" 
fi
done

sleep 5
for db in  xfjtt
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_xfjtt/$time.$db.sql.gz"
       $SCP $SCP_OPTION  "$backup_dir_xfjtt/$time.$db.sql.gz"  $des_backup_dir_xfjtt
       if [[ $? == 0 ]];then
       echo " $time  db xfjtt  succed"  >>  $db_backup_log/xfjtt_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host xfjtt_db bak faliure" 
fi
done

sleep 5
for db in  qctdaxue qingchun qingchun_bbs
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_qc/$time.$db.sql.gz"
       $SCP $SCP_OPTION "$backup_dir_qc/$time.$db.sql.gz"  $des_backup_dir_qc
       if [[ $? == 0 ]];then
       echo " $time db qc  succed"  >>  $db_backup_log/qc_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host qc_db bak faliure" 
fi
done

sleep 5
for db in  risk riskbjxc risk-wx
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_risk/$time.$db.sql.gz"
       $SCP $SCP_OPTION  "$backup_dir_risk/$time.$db.sql.gz"   $des_backup_dir_risk
       if [[ $? == 0 ]];then
       echo " $time db risk  succed"  >>  $db_backup_log/risk_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host risk_db bak faliure" 
fi
done

sleep 5
for db in  rasgj
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_rasgj/$time.$db.sql.gz"
       $SCP $SCP_OPTION  "$backup_dir_rasgj/$time.$db.sql.gz"   $des_backup_dir_rasgj
       if [[ $? == 0 ]];then
       echo " $time  db rasgi  succed"  >>  $db_backup_log/rasgi_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host rasgi_db bak faliure" 
fi
done

sleep 5
for db in  witech
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_witech/$time.$db.sql.gz"
       $SCP $SCP_OPTION   "$backup_dir_witech/$time.$db.sql.gz"  $des_backup_dir_witech
       if [[ $? == 0 ]];then
       echo " $time db witech  succed"  >>  $db_backup_log/witech_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host witech_db bak faliure" 
fi
done

sleep 5
for db in  mysql
do
       $MYSQLDUMP -u$db_user -h$db_host -p$db_passwd $db --add-drop-table | $GZIP  > "$backup_dir_mysql/$time.$db.sql.gz"
       $SCP $SCP_OPTION   "$backup_dir_mysql/$time.$db.sql.gz"   $des_backup_dir_mysql
       if [[ $? == 0 ]];then
       echo " $time db mysql  succed"  >>  $db_backup_log/mysql_db_bak_ok.log
       else
       $SENDEMAIL -f \
       $email_in_address -t \
       $email_out_address -s \
       $email_platform  -u "db bak faliure"
       $email_option -xu $email_user -xp $email_pass  -m "$error_host mysql_db bak faliure" 
fi
done

#delete the oldest backup 2 days ago
find $backup_dir_huaiyun  -name "*.gz" -mtime +2 |xargs rm -rf
find $backup_dir_jtt      -name "*.gz" -mtime +2 |xargs rm -rf
find $backup_dir_xfjtt    -name "*.gz" -mtime +2 |xargs rm -rf
find $backup_dir_qc       -name "*.gz" -mtime +2 |xargs rm -rf
find $backup_dir_risk     -name "*.gz" -mtime +2 |xargs rm -rf
find $backup_dir_rasgj    -name "*.gz" -mtime +2 |xargs rm -rf
find $backup_dir_witech   -name "*.gz" -mtime +2 |xargs rm -rf
find $backup_dir_mysql    -name "*.gz" -mtime +2 |xargs rm -rf

exit 0