适用场景：远程服务器上的 MySQL 不允许远程访问，此时我们需要先登录服务器，备份数据库后，将备份文件拉取到本机。
源码 mysqlDumpRemote2Local.sh
#!/bin/bash
################################################
# TODO: 登录远程服务器，备份指定数据库并下载到本地
# 示例：
# ./mysqlDumpRemote2Local.sh
#
# Author: whoru.S.Q
# Link: /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 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_1113135359.sql
Warning: Using a password on the command line interface can be insecure.
[root@dbhs backup]# tar -zcvf test_1113135359.tar.gz test_1113135359.sql
test_1113135359.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_1113135359.tar.gz /Users/xxxxx/mydev/linux-learning/shell
root@218.29.103.28's password:
test_1113135359.tar.gz 100% 66KB 941.5KB/s 00:00
==> 操作结束，文件位置：/Users/xxxxx/mydev/linux-learning/shell/test_1113135359.tar.gz
