#!/bin/bash
# Simple script to backup MySQL databases

# Parent backup directory
backup_parent_dir="/var/dbdumps/mysql"

# MySQL settings
mysql_user="root"
mysql_password=""
#name of database which you want to backup
mysql_database=""

# Read MySQL password from stdin if empty
if [ -z "${mysql_password}" ]; then
  echo -n "Enter MySQL ${mysql_user} password: "
  read -s mysql_password
  echo
fi

# Read MySQL Database name from stdin if empty
if [ -z "${mysql_database}" ]; then
  echo -n "Enter Database name: "
  read -s mysql_database
  echo
fi

# Check MySQL password
echo exit | mysql --user=${mysql_user} --password=${mysql_password} -B 2>/dev/null
if [ "$?" -gt 0 ]; then
  echo "MySQL ${mysql_user} password incorrect"
  exit 1
else
  echo "MySQL ${mysql_user} password correct."
fi

#delete last week's backup, so we keep daily backup but upto last week
last_week_date=`date -d '-7 day' '+%Y_%m_%d'`
last_backup_dir="${backup_parent_dir}/${last_week_date}"
echo "Removing Last week's backup"
rm -rf ${last_backup_dir}


# Create backup directory and set permissions
backup_date=`date +%Y_%m_%d`
backup_dir="${backup_parent_dir}/${backup_date}"

echo "Backup directory: ${backup_dir}"
mkdir -p "${backup_dir}"
chmod 700 "${backup_dir}"

echo "Creating backup of \"${mysql_database}\" database"
mysqldump --user=${mysql_user} --password=${mysql_password} ${mysql_database} | gzip -9 > "${backup_dir}/${mysql_database}.sql.gz"
chmod 600 "${backup_dir}/${mysql_database}.sql.gz"
echo "Backup Created at: ${backup_dir}/${mysql_database}.sql.gz"

#if you want to backup all databases change above code to this

# Get MySQL databases
# mysql_databases=`echo 'show databases' | mysql --user=${mysql_user} --password=${mysql_password} -B | sed /^Database$/d`

# # Backup and compress each database
# for database in $mysql_databases
# do
#   if [ "${database}" == "information_schema" ] || [ "${database}" == "performance_schema" ]; then
#         additional_mysqldump_params="--skip-lock-tables"
#   else
#         additional_mysqldump_params=""
#   fi
#   echo "Creating backup of \"${database}\" database"
#   mysqldump ${additional_mysqldump_params} --user=${mysql_user} --password=${mysql_password} ${database} | gzip > "${backup_dir}/${database}.gz"
#   chmod 600 "${backup_dir}/${database}.gz"
# done