使用ssh远程执行命令批量导出数据库到本地

共计 4385 个字符，预计需要花费 11 分钟才能阅读完成。

前天正在跟前端的同事调试功能。服务器开好，模拟的玩家登录好，就在倒计时。这时突然运营的同事跑过来说要统计几个服务器玩家的一些情况，也就是需要从几个服的数据库导出部分玩家的数据。好吧，我看了一下时间，11：47。心想，跟前端调试完，去吃个饭再午休一下那就下午再给吧。没想对方来一句 ” 就导个数据库而已，要这么久么？”，而且还是直接跟我上司说的。我嚓，好吧，我导。可问题来了，平时的统计是由 php 做的，批量部署这些是由运维做的。服务端完全没有对应的工具。而且服务器是在阿里云上的，数据库的用户是限制了 ip 段登录的，我所在的 ip 没法登录的。于是，只好终止调试，切 ip，写 sql, 然后用 navicat 手动一个个服务器导出数据到 excel。

事后想想，还是写个脚本吧，不然以后还是会被坑的。

从环境来看，数据库不能直接登录，没法直接导出。不过可以由运维提供 key 通过 ssh 登录到远程服务器再将数据导出到本地。

先配置 ssh 通过 key 登录服务器。这里略过 …

然后就是通过 ssh 执行命令。先看一下 ssh 的帮助文档：

usage: ssh [-1246AaCfgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]
          [-D [bind_address:]port] [-E log_file] [-e escape_char]
          [-F configfile] [-I pkcs11] [-i identity_file]
          [-L [bind_address:]port:host:hostport] [-l login_name] [-m mac_spec]
          [-O ctl_cmd] [-o option] [-p port]
          [-Q cipher | cipher-auth | mac | kex | key]
          [-R [bind_address:]port:host:hostport] [-S ctl_path] [-W host:port]
          [-w local_tun[:remote_tun]] [user@]hostname [command]

最后一项就是执行指令的。假如远程服务器地址为 180.97.33.108，开启的 ssh 端口为 998，ssh 用户名为 linuxidc，然后想通过在远程服务器上执行命令 ls，那么脚本应该这么写：

ssh linuxidc@180.97.33.108 -p 998 “ls”

如果 ssh 的认证 key 已配置好，那么会把登录后当前目录下的文件列出来，当然第一次登录会提示保存远程服务器的指纹。现在是要导出数据库的数据，那么需要把 ls 命令换成导出数据库的命令。

echo “select * from user;” | mysql linuxidc_db -uxzc -plinuxidcpwd
# 或者
mysql linuxidc_db -uxzc -plinuxidcpwd -e “select * from user”

上面两命令都可以使用数据库用户 linuxidc，密码 xzcpwd 从数据库 linuxidc_db 打印出 user 表，使用的是默认的本地数据库地址 localhost，默认的端口。如果不是默认，需要指定。

登录 OK 了，打印也 OK 了，那么下一步就是导出到文件了。这在 bash 也就是一个 > 的事。把上面的命令拼起来就是：

ssh linuxidc@180.97.33.108 -p 998 ‘echo “select * from user;” | mysql linuxidc_db -uxzc -plinuxidcpwd’ > user.txt

这样就把 user 表导出来本地的 user.txt 中了。注意 ”> user.txt” 如果放到 ” 里则是在远程服务器执行，导出的文件在远程服务器。文件现在也有了，不过是 txt，这样交给运营不太好吧。那就导出 excel 吧。不过遗憾的是我查了 N 多资料，也找不到 mysql 不依赖第三方插件或工具导出原生 excel 的方法。而 navicat 导出的可是货真价实的 excel，如果用 notepad++ 之类的文本工具打开是会乱码的，并且导出的文件不会有编码问题。幸好如果一个 txt 以 tab 分割的话，excel 也是能认得出来的。于是把 user.txt 改名 user.xls 就可以了。但这样做的问题是 excel 会按自己的方式处理内容的。比如把一个很大的数字转换成科学记数法形式。这些都得手动去处理一下了。

最后，就是写成脚本批量操作了。附上我使用的脚本一个：

#!/bin/bash

# 通过 ssh 远程执行远程指令
# 需要先部署 key 认证，保证 ssh 只需要 ip、port 即可连接
# 如果需要和远程服务器交互，请参考 ssh 的 -t、-tt 参数
# 如果需要反复登录服务器执行多条指令，请使用 ssh 的通道重用
# 参考：http://en.wikibooks.org/wiki/OpenSSH/Cookbook/Multiplexing
# 使用通道需要注意退出通道，如 ”ssh github.com -O exit” 或者 ”ssh github.com -O stop”
#                                              –by coding my life

# 分别设置 ssh 用户名、数据库用户名、数据库密码、导出数据
SSH_USER=’linuxidc_ssh’
DB_USER=’linuxidc_db’
DB_PWD=’linuxidc_db_pwd123′
EXP_PATH=export_data/

# 执行远程命令
# $1 服务器 ip
# $2 ssh 端口
# $3 指令
function exec_remote_command()
{
    ssh $SSH_USER@$1 -p $2 ‘$3’
}

# 执行远程 sql, 导出数据
# $1 服务器 ip
# $2 ssh 端口
# $3 指令, 多个 sql 指令如 select * from user;select * from bag; 也可执行，但结果将会写到同一个文件
# s4 服务器
# $5 导出文件
function export_remote_sql()
{
    echo export from $4 …
    cmd=”echo \”$3\” | mysql $4 -u$DB_USER -p$DB_PWD –default-character-set=utf8″

    ssh $SSH_USER@$1 -p $2 “$cmd” > $EXP_PATH$4_$5    #如果要导出到远程服务器，将把 > $EXP_PATH$4_$5 放到 cmd 中
}

# $1 区服名
# $2 ip
# $3 端口
function exec_sqls()
{
    cat SQLS | while read sql ; do
   
        fc=${sql:0:1}
        if [“#” == “$fc”]; then    #被注释的不处理
            continue
        fi

        #sql 语句中包含空格，不能再以空格来区分。最后一个空格后的是导出的文件名
        exp_file=”${sql##*}”                #两个 #表示正则以最大长度匹配 * 和一个空格 (* 后面的空格), 截取余下的赋值给 exp_file
        sql_cmd=”${sql%% $exp_file}”        #两个 % 表示从右至左删除 %% 以后的内容
       
        export_remote_sql $2 $3 “$sql_cmd” $1 “$exp_file”
    done
}

# 需要在当前目录下创建服务器列表文件 SERVERS, 格式为 ” 数据库名 ip ssh 端口 ”, 如 ”linuxidc_game_s99 127.0.0.1 22″
# 需要在当前目录下创建 sql 命令列表文件 SQLS, 格式为 ”sql 语句 导出的文件 ”, 如 ”select * from user; user.xls”
# 多个 sql 请注意用; 分开，sql 必须以; 结束
# 文件名中不能包含空格，最终导出的文件为 ” 数据库名_文件名 ”, 如 ”linuxidc_game_s99_user.xls”

mkdir -p $EXP_PATH

cat SERVERS | while read server ; do

    fc=${server:0:1}
    if [“#” == “$fc”]; then    #被注释的不处理
        continue
    fi

    name=`echo $server|awk ‘{print $1}’`
    ip=`echo $server|awk ‘{print $2}’`
    port=`echo $server|awk ‘{print $3}’`

    exec_sqls $name $ip $port
done

当前目录下的文件如下，其中 SERVERS 是服务器列表，里面指定数据库名，ip，ss 端口，SQLS 则指定 sql 指令及导出的文件名。这两个文件里以 #开头的都不会处理：

linuxidc@www.linuxidc.com:~/ 桌面 /remote_cmd$ ls
remote_cmd.sh  SERVERS  SQLS
linuxidc@www.linuxidc.com:~/ 桌面 /remote_cmd$ cat SERVERS
linuxidc_game_s99 120.0.0.99 6162
linuxidc_game_s91 120.0.0.91 6162
linuxidc_game_s92 120.0.0.92 6162
linuxidc_game_s93 120.0.0.93 6162
linuxidc_game_s94 120.0.0.94 6162
#linuxidc_game_s91 120.0.0.91 6162

linuxidc@www.linuxidc.com:~/ 桌面 /remote_cmd$ cat SQLS
#select * money from money; money.xls
select * from user; user.xls
linuxidc@www.linuxidc.com:~/ 桌面 /remote_cmd$

到这里，脚本基本完成了要求。

Linux 下使用 SSH 图文详解教程 http://www.linuxidc.com/Linux/2011-06/37690.htm

如何为 Linux 系统中的 SSH 添加双重认证 http://www.linuxidc.com/Linux/2014-08/105998.htm

在 Linux 中为非 SSH 用户配置 SFTP 环境 http://www.linuxidc.com/Linux/2014-08/105865.htm

Linux 上 SSH 服务的配置和管理 http://www.linuxidc.com/Linux/2014-06/103627.htm

SSH 入门学习基础教程 http://www.linuxidc.com/Linux/2014-06/103008.htm

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2015-04/115761.htm