MySQL Database Backup Script

Here I  have given command to take backup of all database in one file

# mysqldump -u root -p --all-databases > all_dbs.sql

If you want to take backup all database in separate file with automatic retention and email alert you can use below shell script.

#!/bin/bash
#==============================================================================
#TITLE:            mysql_backup.sh
#DESCRIPTION:      script for automating the daily mysql backups
#USAGE:            ./mysql_backup.sh
#CRON:

  # example cron for daily db backup @ 9:15 am
  # min  hr mday month wday command
  # 15   9  *    *     *    /Users/[your user name]/scripts/mysql_backup.sh

#RESTORE FROM BACKUP
  #$ gunzip < [backupfile.sql.gz] | mysql -u [uname] -p[pass] [dbname]
#==============================================================================
# CUSTOM SETTINGS
#==============================================================================
# directory to put the backup files
BACKUP_DIR=/backup/mysqlbkp

# MYSQL Parameters
MYSQL_UNAME=backup
MYSQL_PWORD=B@ckUp@1@3

# Email Parameters
MAIL="mail1.example.com", "mail2.example.com"
MAILER="$(which mail)"
STATUSFILE="/tmp/statusfile.txt"

# Don't backup databases with these names 
# Example: starts with mysql (^mysql) or ends with _schema (_schema$)
IGNORE_DB="(^mysql|_schema$)"

# include mysql and mysqldump binaries for cron bash user
PATH=$PATH:/usr/local/mysql/bin
# Number of days to keep backups
KEEP_BACKUPS_FOR=2 #days
#==============================================================================
# METHODS
#==============================================================================
# YYYY-MM-DD

TIMESTAMP=$(date +%F)
function delete_old_backups()
{
  echo "Deleting $BACKUP_DIR/*.sql.gz older than $KEEP_BACKUPS_FOR days" 
  find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +$KEEP_BACKUPS_FOR -exec rm {} \;
}

function mysql_login() {
  local mysql_login="-u $MYSQL_UNAME" 
  if [ -n "$MYSQL_PWORD" ]; then
    local mysql_login+=" -p$MYSQL_PWORD" 
  fi
  echo $mysql_login
}

function database_list() {
  local show_databases_sql="SHOW DATABASES WHERE \`Database\` NOT REGEXP '$IGNORE_DB'"

  echo $(mysql $(mysql_login) -e "$show_databases_sql"|awk -F " " '{if (NR!=1) print $1}')
}

function echo_status(){
  printf '\r'; 
  printf ' %0.s' {0..100} 
  printf '\r'; 
  printf "$1"'\r'
}


function backup_database(){
    backup_file="$BACKUP_DIR/$TIMESTAMP.$database.sql.gz" > $STATUSFILE
    output+="$database => $backup_file\n"
    echo_status "...backing up $count of $total databases: $database" >> $STATUSFILE
    $(mysqldump $(mysql_login) $database | gzip -9 > $backup_file)
}

function backup_databases(){
  local databases=$(database_list)
  local total=$(echo $databases | wc -w | xargs)
  local output=""
  local count=1
  for database in $databases; do
  backup_database
  local count=$((count+1))
  done
  echo -ne $output | column -t >> $STATUSFILE
}

function hr(){
  printf '=%.0s' {1..100}
  printf "\n"
}

#==============================================================================

#Run Rsync After Backup

#To sync first configure password less ssh from source to destination server

#==============================================================================
echo_status "Start rsync to destination server" >> $STATUSFILE
rsync /backup/mysqlbkp/*  -ave  "ssh -c arcfour -p 2255" --recursive --delete-during  root@10.0.0.1:/home/mysql_Backup_10SRV >> $STATUSFILE
echo_status "complete rsync to destination server" >> $STATUSFILE
#==============================================================================
# RUN SCRIPT
#==============================================================================
delete_old_backups
hr
backup_databases
hr 
printf "All backed up!\n\n" >> $STATUSFILE
$MAILER -s "MySQL Database Backup report for 10.10.10.1 $NOW" -- $MAIL < $STATUSFILE
rm $STATUSFILE