linux 之mysql备份脚本

####编写脚本，支持让用户自主选择，使用mysqldump还是xtraback全量备份。

#date 2020.2.20
#author zhang
#描述 用户自己选择使用什么工具进行备份
#$1 账户; $2 密码 $3 地址

stty erase "^H"
[ -f /etc/init.d/functions ] && . /etc/init.d/functions

#备份文件夹路径
backup_path="/usr/local/src/backup_mysql"
[ -d $backup_path ] && echo "$backup_path is exist" || mkdir -p $backup_path

# mysqldump
fun_mysqldump(){

mysqldump_cmd="mysqldump"
if [ "$passwd" != "null" ];then
${mysqldump_cmd} -u$user -p$passwd -h$host -A -F -E -R --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $backup_path/$backup_file
else
${mysqldump_cmd} -u$user -h$host -A -F -E -R --master-data=1 --flush-privileges --triggers --default-character-set=utf8 --hex-blob > $backup_path/$backup_file
fi
[ $? -eq 0 ] && action " backup successful ,please cat \n $backup_path\/$backup_file" /bin/true || action "mysql backup is failed " /bin/false
exit 0
}

test_connect_mysql(){
if [ "$2" != "null" ];then
if `mysql -u"$1" -p"$2" -h"$3" -e "show databases;" &> /dev/null`;then
user=$1
passwd=$2
host=$3
else
echo "connection is lose,please check user,passd or host" && exit 3
fi
else
if `mysql -u "$1" -h"$3" -e "show databases;" &>/dev/null`;then
user=$1
host=$3

else
echo "connection is lose, check your user or host" && exit 3
fi
fi
}

#设置 $1 $2 $3
set_user_pass_host(){
[ "$1" != "" ] && user=$user || user="root";
[ "$2" != "" ] && passwd=$passwd || passwd="null";
[ "$3" != "" ] && host=$host || host="127.0.0.1";
}

input_user_pass_host(){
read -p "input the user:" user
read -p "input the passwd:" passwd
read -p "input host(default 127.0.0.1):" host
}

#xtrabackup
fun_xtrabackup(){
[ ! -e `whereis xtrabackup &> /dev/null` ] && yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm -y;yum install percona-xtrabackup -y &> /dev/null

if [ "$passwd" != "null" ];then
xtrabackup --user=$1 --password=$2 --host=$3 --backup --target-dir=$4 &> /dev/null
else
xtrabackup --user=$1 --host=$3 --backup --target-dir=$4 &> /dev/null
fi
[ $? -eq 0 ] && action "xtrabackup completed OK!" /bin/true || action "xtrabackup completed failed" /bin/false
exit

}

while true
do
cat << EOF
please input a number choose backup tools
1 mysqldump
2 xtrabackup
3 quit
EOF
read -p "you select:" select_id
case $select_id in
1)
input_user_pass_host
set_user_pass_host $user $passwd $host
if test_connect_mysql $user $passwd $host;then
read -p "please input mysql backup file name (default backup.all.sql):" backup_file
if [ "$backup_file" = "" ] ;then

backup_file="backup.all.sql"

fi
fun_mysqldump $user $passwd $host $backup_path/$backup_file
fi
;;
2)
input_user_pass_host
set_user_pass_host $user $passwd $host
if test_connect_mysql $user $passwd $host;then
read -p "please input target-dir (default $backup_path/):" target_dir
if [ "$target_dir" = "" ];then

fun_xtrabackup $user $passwd $host $backup_path
else
mkdir $target_dir -p
fun_xtrabackup $user $passwd $host $target_dir
fi
fi
;;
3)
echo "exit" && exit 6
;;
*)
echo "is error" && exit 7
;;
esac
done