linux下使用ssh遠程執行命令大量匯出資料庫到本地_linux shell

前天正在跟前端的同事調試功能。伺服器開好，類比的玩家登入好，就在倒計時。這時突然運營的同事跑過來說要統計幾個伺服器玩家的一些情況，也就是需要從幾個服的資料庫匯出部分玩家的資料。好吧，我看了一下時間，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使用者名稱為xzc，然後想通過在遠程伺服器上執行命令ls，那麼指令碼應該這麼寫：

複製代碼 代碼如下:

ssh xzc@180.97.33.108 -p 998 "ls"

如果ssh的認證key已配置好，那麼會把登入後目前的目錄下的檔案列出來，當然第一次登入會提示儲存遠程伺服器的指紋。現在是要匯出資料庫的資料，那麼需要把ls命令換成匯出資料庫的命令。

複製代碼 代碼如下:

echo "select * from user;" | mysql xzc_db -uxzc -pxzcpwd
#或者
mysql xzc_db -uxzc -pxzcpwd -e "select * from user"

上面兩命令都可以使用資料庫使用者xzc，密碼xzcpwd從資料庫xzc_db列印出user表，使用的是預設的本機資料庫地址localhost，預設的連接埠。如果不是預設，需要指定。

　　 登入OK了，列印也OK了，那麼下一步就是匯出到檔案了。這在bash也就是一個 > 的事。把上面的命令拼起來就是：

複製代碼 代碼如下:

ssh xzc@180.97.33.108 -p 998 'echo "select * from user;" | mysql xzc_db -uxzc -pxzcpwd' > 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='xzc_ssh'DB_USER='xzc_db'DB_PWD='xzc_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連接埠",如"xzc_game_s99 127.0.0.1 22"# 需要在目前的目錄下建立sql命令列表檔案SQLS,格式為"sql語句 匯出的檔案",如"select * from user; user.xls"# 多個sql請注意用;分開，sql必須以;結束# 檔案名稱中不能包含空格，最終匯出的檔案為"資料庫名_檔案名稱",如"xzc_game_s99_user.xls"mkdir -p $EXP_PATHcat 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 $portdone

目前的目錄下的檔案如下，其中SERVERS是伺服器列表，裡面指定資料庫名，ip，ss連接埠，SQLS則指定sql指令及匯出的檔案名稱。這兩個檔案裡以#開頭的都不會處理：

xzc@xzc-HP-ProBook-4446s:~/案頭/remote_cmd$ lsremote_cmd.sh SERVERS SQLSxzc@xzc-HP-ProBook-4446s:~/案頭/remote_cmd$ cat SERVERS xzc_game_s99 120.0.0.99 6162xzc_game_s91 120.0.0.91 6162xzc_game_s92 120.0.0.92 6162xzc_game_s93 120.0.0.93 6162xzc_game_s94 120.0.0.94 6162#xzc_game_s91 120.0.0.91 6162xzc@xzc-HP-ProBook-4446s:~/案頭/remote_cmd$ cat SQLS #select * money from money; money.xlsselect * from user; user.xlsxzc@xzc-HP-ProBook-4446s:~/案頭/remote_cmd$

到這裡，指令碼基本完成了要求。