mysqlbackup.sh

#!/bin/bash
# backup MySQL databases
# author: lostsnow@gmail.com

# backup directory
backup_parent_dir="/opt/backups/mysql"
mysql_dir=/usr/local/mysql

# MySQL settings
mysql_host=$1
mysql_user="root"
mysql_password=""
mysql_port="3306"
expire_backup_delete="ON"
expire_days=3

mysql=${mysql_dir}/bin/mysql
mysqldump=${mysql_dir}/bin/mysqldump

if [ -z ${mysql_host} ] ; then
    echo "MySQL hostname incorrect"
    exit 1
fi

backup_parent_dir=${backup_parent_dir}/${mysql_host}

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

# Create backup directory and set permissions
backup_time=`date +%Y%m%d%H%M`
backup_Ymd=`date +%Y-%m-%d`
backup_3ago=`date -d '3 days ago' +%Y-%m-%d`
backup_dir=${backup_parent_dir}/$backup_Ymd
echo "${mysql_host} Backup directory: ${backup_dir}"
mkdir -p "${backup_dir}"
chmod 700 "${backup_dir}"

# Get MySQL databases
mysql_databases=`echo 'show databases' | ${mysql} --host=${mysql_host} --port=${mysql_port} --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" ] && [ "${database}" != "mysql" ]; then
        echo "${mysql_host} Creating backup of \"${database}\" database"
        ${mysqldump} --host=${mysql_host} --port=${mysql_port} --user=${mysql_user} --password=${mysql_password} --single-transaction ${database} | gzip > "${backup_dir}/${database}-${backup_time}.gz"
        flag=`echo $?`
        if [ $flag == "0" ];then
            echo "${mysql_host} database ${database} success backup to $backup_dir/${database}-${backup_time}.gz"
            chmod 600 "${backup_dir}/${database}-${backup_time}.gz"
        else
            echo "${mysql_host} database ${database} backup fail!"
        fi
    fi
done

if [[ ${expire_backup_delete} == "ON" ]] && [[ ${backup_parent_dir} != "" ]];then
    `find ${backup_parent_dir}/ -type d -mtime +${expire_days} | xargs rm -rf`
    echo "${mysql_host} Expired backup data delete complete!"
fi
echo "${mysql_host} All database backup success!"