shell 备份mysql库到远程_使用 Shell 备份远程服务器上的 MySQL 数据库到本机

适用场景：远程服务器上的 MySQL 不允许远程访问，此时我们需要先登录服务器，备份数据库后，将备份文件拉取到本机。

源码 mysqlDumpRemote2Local.sh

#!/bin/bash

################################################

# TODO: 登录远程服务器，备份指定数据库并下载到本地

# 示例：

# ./mysqlDumpRemote2Local.sh

#

# Author: whoru.S.Q

# Link: https://github.com/whorusq/linux-learning/blob/master/shell/mysqlDumpRemote2Local.sh

# Version: 1.0

################################################

# 待操作待服务器列表

# 格式："描述,数据库名,数据库用户名,数据库密码,数据库端口号,SSH登录用户名,SSH服务器IP,SSH登录密码,SSH端口号"

HOSTS=(

"test,db_demo,root,pwd123456,3306,root,192.168.1.127,pwd3333,22"

)

HOSTS_LEN=`echo ${#HOSTS[*]}`

HOST_KEY=

# 服务器上备份文件的存放目录

DB_BACKUP_PATH=/opt/backup

# 当前脚本所处目录

BASE_PATH=$(cd "$(dirname "$0")";pwd)

# 入口

function main {

#检查基础设置

if [ "$HOST_LEN" == "0" ]; then

echo "您还没有配置待操作的服务器参数"

exit 1

fi

# 显示菜单

MENU="\n"

INDEX=1

for host in ${HOSTS[*]}

do

local host_name=`echo $host | awk -F ',' '{ print $1" ===> "$2}'`

MENU=$MENU"\t"$INDEX". "$host_name"\n"

INDEX=`expr $INDEX + 1`

done

echo -en $MENU"\n"

# 检查选择

checkChoice

# 开始导出操作

doDump

}

function checkChoice {

read -p "请输入序号选择待备份的数据库：" HOST_NUM

len=`echo "$HOST_NUM"|sed 's/[1-9]//g'`

if [ -n "$len" ]; then

ifGoon "输入有误，只支持1-9的数字，是否重新输入[y/n]："

else

KEY=`expr $HOST_NUM - 1`

if [ $KEY -le $HOSTS_LEN ]; then

HOST_KEY=$KEY

else

ifGoon "未知的序号，是否重新输入[y/n]："

fi

fi

}

function ifGoon {

echo -en "\033[32m==>\033[0m "

read -p $1 GOON

if [ "$GOON" == "y" ]; then

checkChoice

else

exit 0

fi

}

function doDump {

if [ -n "$SERVER_KEY" ]; then

echo "未知的序号"

exit 0

fi

# 从对应的配置中解析数据库和 SSH 参数

HOST=${HOSTS[$HOST_KEY]}

HOST_NAME=`echo $HOST | awk -F ',' '{ print $1 }'`

DB_NAME=`echo $HOST | awk -F ',' '{ print $2 }'`

DB_USER=`echo $HOST | awk -F ',' '{ print $3 }'`

DB_PWD=`echo $HOST | awk -F ',' '{ print $4 }'`

DB_PORT=`echo $HOST | awk -F ',' '{ print $5 }'`

SSH_USER=`echo $HOST | awk -F ',' '{ print $6 }'`

SSH_IP=`echo $HOST | awk -F ',' '{ print $7 }'`

SSH_PWD=`echo $HOST | awk -F ',' '{ print $8 }'`

SSH_PORT=`echo $HOST | awk -F ',' '{ print $9 }'`

# 备份文件名

DUMP_FILENAME=$HOST_NAME"_"`date +%Y%m%d%H%M%S`

echo -e "\n\033[32m==>\033[0m 操作开始 "

sleep 1

echo -e "\n\033[32m==>\033[0m 登录 ${HOST_NAME} 正式服务器，备份数据库"

sleep 1

expect -c "

spawn ssh ${SSH_USER}@${SSH_IP} -p ${SSH_PORT}

expect {

\"yes/no\" {send \"yes\n\"; exp_continue;}

\"*assword\" { send \"${SSH_PWD}\r\n\"; exp_continue ; sleep 3; }

\"Last*\" { send_user \"\n 登录成功 \n\";}

}

expect \"*]#\"

send \"ls ${DB_BACKUP_PATH} &>/dev/null && cd ${DB_BACKUP_PATH} || mkdir -p ${DB_BACKUP_PATH} && cd ${DB_BACKUP_PATH} \r\"

send \"mysqldump -u${DB_USER} -p${DB_PWD} -P ${DB_PORT} ${DB_NAME} > ${DUMP_FILENAME}.sql \r\"

send \"tar -zcvf ${DUMP_FILENAME}.tar.gz ${DUMP_FILENAME}.sql \r\"

send \"exit \r\"

interact

"

echo -e "\n\033[32m==>\033[0m 退出 ${HOST_NAME} 正式服务器 \n"

sleep 1

echo -e "\n\033[32m==>\033[0m 将备份文件拉取到本机 \n"

sleep 1

expect -c "

spawn scp -P $SSH_PORT $SSH_USER@$SSH_IP:$DB_BACKUP_PATH/$DUMP_FILENAME.tar.gz $BASE_PATH

expect {

\"*assword\" { send \"${SSH_PWD}\r\n\"; exp_continue ; }

}

"

echo -e "\n\033[32m==>\033[0m 操作结束，文件位置："$BASE_PATH/$DUMP_FILENAME.tar.gz" \n"

}

# 运行

main

使用示例：

➜ ./mysqlDumpRemote2Local.sh

1. test ===> db_demo

请输入序号选择待备份的数据库：1

==> 操作开始

==> 登录 test 正式服务器，备份数据库

spawn ssh root@192.168.1.127 -p 22

root@192.168.1.127's password:

Last login: Tue Nov 13 13:43:53 2018 from xxxxxxxxx

登录成功

[root@ test ~]# ls /opt/backup &>/dev/null && cd /opt/backup || mkdir -p /opt/backup && cd /opt/backup

[root@ test backup]# mysqldump -uroot -ppwd123456 -P 3306 db_demo > test_20181113135359.sql

Warning: Using a password on the command line interface can be insecure.

[root@dbhs backup]# tar -zcvf test_20181113135359.tar.gz test_20181113135359.sql

test_20181113135359.sql

[root@dbhs backup]# exit

logout

Connection to 192.168.1.127 closed.

==> 退出 test 正式服务器

==> 将备份文件拉取到本机

spawn scp -P 22 root@192.168.1.127:/opt/backup/test_20181113135359.tar.gz /Users/xxxxx/mydev/linux-learning/shell

root@218.29.103.28's password:

test_20181113135359.tar.gz 100% 66KB 941.5KB/s 00:00

==> 操作结束，文件位置：/Users/xxxxx/mydev/linux-learning/shell/test_20181113135359.tar.gz