miércoles, 29 de mayo de 2013

Backup Mysql SERVER

This is my script to create MYSQL backup's


       
#!/bin/bash

### Backup server configuration ###
BCK_SERVER=www.myserver.com
BCK_SERVER_DIR=/home/backup/test

### THIS IS NECESARY ########################
### BACKUP Server need 
### ssh-copy-id -i .ssh/id_rsa.pub root@${BCK_SERVER}
###
### If you whant create rsa:
### ssh-keygen -t rsa
########################################

### SETUP MYSQL LOGIN ###
MUSER='mysql_user'
MPASS='mysql_pass'
MHOST="localhost"

### Set bins path ###
MYSQL=/usr/bin/mysql

### Default time format ###
TIME_FORMAT='%Y%m%d%H%M%S'
HUMAN_TIME_FORMAT='%Y/%m/%d %H:%M:%S%P'

### Temporal directory to backup ###
BCK_TGZ=/tmp/backup
BCK_TMP=/tmp/backup/mysql

### Email notification configure ###
EMAIL="myemail@domain.com"  
SUBJECT="MYSQL Backup success" 

### Die on demand with message ###
die(){
 echo "$@"
 exit 999
}
 
### Make sure bins exists.. else die
verify_bins(){
 echo "Checking comands ..."
 if [ ! -x $MYSQL ] && die; then
  echo "File $MYSQL does not exists. Make sure correct path is set in $0."
  die
 fi
 echo "Success!"
}
 
### Make sure we can connect to server ... else die
verify_mysql_connection(){
 echo "Checking MySQL connection..."
 mysql --user=${MUSER} --password=${MPASS} -h${MHOST} -e exit 2>/dev/null
 if [ $? -eq 0 ] || die; then      
  echo "Success!"
 else
  echo "Error: Cannot connect to MySQL Server. Make sure username and password are set correctly in $0"
  die
    fi
}

### Make a MysqlBackup
backup_mysql(){
 if [ ! -d $BCK_TGZ ]; then
  echo "Backup directory $BCK_TGZ not exists! Make it!"
  mkdir -p $BCK_TGZ  
 fi
 
 if [ ! -d $BCK_TMP ]; then
  echo "Backup directory $BCK_TMP not exists! Make it!"
  mkdir -p $BCK_TMP  
 fi
 
 echo "Clean $BCK_TMP content"
 rm -fR $BCK_TMP/*  
 
 echo "---=======================--";
 echo "       Start Mysql backup databases";
 echo "---=======================--";
 
 local DBS="$(mysql -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
 for db in $DBS 
 do
  if [ $db != 'mysql' ] && [ $db != 'performance_schema' ] && [ $db != 'information_schema' ] && [ $db != 'test' ]; then   
   local tTime=$(date +"${TIME_FORMAT}")
   local FILE="${BCK_TMP}/${db}.${tTime}.sql"
   mysqldump -u ${MUSER} -h ${MHOST} -p${MPASS} $db > $FILE   
   echo "Make database $db backup: $FILE";   
  fi  
 done
 
 echo "---=======================--";
 echo "       Backup databases completed ";
 echo "---=======================--";
}

### Make files compresion and copy in backup server
backup_files(){
 echo "Compress files and copy in $BCK_SERVER"
 local tTime=$(date +"${TIME_FORMAT}")
 local FILE="${BCK_TGZ}/${tTime}.tar.gz"
 cd ${BCK_TMP}
 tar -zcvpf $FILE *
 cd -
 echo "Compress success in $FILE"
 scp  ${FILE} root@${BCK_SERVER}:${BCK_SERVER_DIR}
 echo "Backup sucessfull!!"
 notify
}

### Notify by email when backup is success ###
notify(){ 
 local NOW=$(date +"${HUMAN_TIME_FORMAT}")
 local MESSAGE="/tmp/emailmessage"
 local MAIL_BIN="/usr/bin/mail"
 if [ -f ${MAIL_BIN} ]; then
  echo "Backup success at ${NOW}" > ${MESSAGE} 
  ${MAIL_BIN} -s ${SUBJECT}" "${EMAIL} < ${MESSAGE} 
 else
  echo "${MAIL_BIN} not exists!!!"
 fi 
}
 
### main ####
NOW=$(date +"${HUMAN_TIME_FORMAT}")
echo "********************************* BACKUP SCRIPT START ***************************"
echo "*************************************** ${NOW} **********************************"

verify_bins
verify_mysql_connection
backup_mysql
backup_files

NOW=$(date +"${HUMAN_TIME_FORMAT}")
echo "********************************* BACKUP SCRIPT END ***************************"
echo "*************************************** ${NOW} **********************************"

No hay comentarios:

Publicar un comentario