操作流程:
先登录服务器,备份数据库;
将备份文件拉取到本机。
主要涉及知识点:expect
、mysqldump
、scp
1. 源码 新建 Shell 文件:mysqlDumpRemote2Local.sh
#!/bin/bash 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 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
2. 使用示例 ➜ ./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