Linux use SSH remote execution command to bulk export database to local _linux shell

The front end of the day before the colleague debugging function. The server is good, the simulated player login is good, in the countdown. At this time the sudden operation of the colleague came to say to count the number of server players, that is, the need to export some of the user data from the database. Well, I looked at the time, 11:47. Thought, with the front end debugging, go to eat a meal then lunch break then give it in the afternoon. Did not think of each other to say "just guide a database, take so long?" "And I said it directly to my boss. I'll do it, all right, I'll lead. Can be a problem, the usual statistics are made by PHP, bulk deployment of these are carried out by the operation of the dimension. The server has no corresponding tool at all. And the server is on the Ali Cloud, the user of the database is limited IP segment login, I am in the IP can not log in. So, had to terminate debugging, cut IP, write SQL, and then use NAVICAT Manual server to export data to Excel.

Think about it afterwards, or write a script, or it will be a pit later.
From the environment, the database can not be directly logged in, cannot directly export. However, the operation dimension provides key to log on to the remote server via SSH and then export the data to the local area.
First configure SSH through key to log on to the server. Here skip ...

Then the command is executed via SSH. Check out the SSH Help document first:

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] [-P port]
      [-Q cipher | cipher-auth | mac | kex | key]
      [R [bind_a Ddress:]port:host:hostport] [-S Ctl_path] [-W Host:port]
      [-W Local_tun[:remote_tun]] [user@]hostname [command]

The last one is the execution of the instructions. If the remote server address is 180.97.33.108, the open SSH port is 998,ssh username Xzc, and then you want to execute command LS on the remote server, then the script should write this:

Copy Code code as follows:

SSH xzc@180.97.33.108-p 998 "ls"

If the SSH authentication key is configured, then the files in the current directory after the login will be listed, of course, the first login will be prompted to save the remote server fingerprint. Now that you want to export the database data, you need to replace the LS command with the Export Database command.

Copy Code code as follows:

echo "SELECT * from user;" | MySQL Xzc_db-uxzc-pxzcpwd
#或者
MySQL xzc_db-uxzc-pxzcpwd-e "select * from User"

Both commands can use the database user Xzc, the password xzcpwd from the database xzc_db print out the user table, using the default local database address localhost, the default port. If it is not the default, it needs to be specified.

Login OK, print OK, then the next step is to export to the file. This is a > thing in bash. To spell the above commands is:

Copy Code code as follows:

SSH xzc@180.97.33.108-p 998 ' echo ' select * from user; MySQL xzc_db-uxzc-pxzcpwd ' > user.txt

This will export the user table to the local user.txt. Note that "> user.txt" if placed in ' is executed at the remote server, the exported file is on the remote server. File now also has, but txt, so to the operation is not very good. Well, then export Excel. Unfortunately, I checked the n more data, also can not find MySQL does not rely on Third-party plug-ins or tools to export native Excel methods. And Navicat export is the real Excel, if the use of text tools such as notepad++ will be garbled, and the exported file will not have coding problems. Fortunately, if a txt to tab segmentation, Excel can also recognize it. So the name of User.txt renamed User.xls can be. But the problem with doing this is that Excel handles the content in its own way. For example, the conversion of a large number into the form of scientific notation. All this has to be done manually.

Finally, the script is written in bulk operation. Attached I used the script one:

#! / bin / bash

# Remotely execute remote instructions via ssh
# Key authentication needs to be deployed first to ensure that ssh only needs IP and port to connect
# If you need to interact with the remote server, refer to the -t and -tt parameters of ssh
# If you need to repeatedly log in to the server to execute multiple instructions, please use ssh channel reuse
# Reference: http://en.wikibooks.org/wiki/OpenSSH/Cookbook/Multiplexing
# To use the channel, please pay attention to exit the channel, such as "ssh github.com -O exit" or "ssh github.com -O stop"
# --by coding my life

#Set ssh username, database username, database password, and export data respectively
SSH_USER = 'xzc_ssh'
DB_USER = 'xzc_db'
DB_PWD = 'xzc_db_pwd123'
EXP_PATH = export_data /

# Execute remote command
# $ 1 server ip
# $ 2 ssh port
# $ 3 instruction
function exec_remote_command ()
{
  ssh $ SSH_USER @ $ 1 -p $ 2 '$ 3'
}

# Execute remote SQL and export data
# $ 1 server ip
# $ 2 ssh port
# $ 3 instructions, multiple SQL instructions such as select * from user; select * from bag; can also be executed, but the results will be written to the same file
# s4 server
# $ 5 export file
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 #If you want to export to a remote server, put> $ EXP_PATH $ 4_ $ 5 in cmd
}

# $ 1 District service name
# $ 2 ip
# $ 3 port
function exec_sqls ()
{
  cat SQLS | while read sql; do
  
    fc = $ {sql: 0: 1}
    if ["#" == "$ fc"]; then #Commented is not processed
      continue
    fi

    #sql statements contain spaces and can no longer be distinguished by spaces. After the last space is the exported file name
    exp_file = "$ {sql ## *}" #Two # indicates that the regular matches the maximum length * and a space (the space after the *), and intercepts the remaining assignment to exp_file
    sql_cmd = "$ {sql %% $ exp_file}" #Two% means to delete content after %% from right to left
    
    export_remote_sql $ 2 $ 3 "$ sql_cmd" $ 1 "$ exp_file"
  done
}

# Need to create a server list file SERVERS in the current directory, the format is "database name ip ssh port", such as "xzc_game_s99 127.0.0.1 22"
# Need to create the sql command list file SQLS in the current directory, the format is "sql statement exported file", such as "select * from user; user.xls"
# Please use multiple SQL; separate, SQL must end with;
# The file name cannot contain spaces. The final exported file is "database_name", such as "xzc_game_s99_user.xls"

mkdir -p $ EXP_PATH

cat SERVERS | while read server; do

  fc = $ {server: 0: 1}
  if ["#" == "$ fc"]; then #Commented is not processed
    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 

The files in the current directory are as follows, where servers is the list of servers, which specifies the database name, Ip,ss port, and SQLS specifies the SQL directive and the exported file name. The two files that start with # will not be processed:

xzc@xzc-hp-probook-4446s:~/desktop/remote_cmd$ ls
remote_cmd.sh SERVERS sqls
xzc@xzc-hp-probook-4446s:~/Desktop/ remote_cmd$ cat SERVERS 
xzc_game_s99 120.0.0.99 6162
xzc_game_s91 120.0.0.91 6162 xzc_game_s92 120.0.0.92
6162
xzc_game_s93 120.0.0.93 6162
xzc_game_s94 120.0.0.94 6162
#xzc_game_s91 120.0.0.91

6162 xzc@xzc-hp-probook-4446s:~/Desktop/remote_cmd$ cat sqls 
#select * money from: Money.xls
select * from user; us Er.xls
xzc@xzc-hp-probook-4446s:~/Desktop/remote_cmd$

Here, the script basically completes the requirement.