Shulou(Shulou.com)06/03 Report--

This article mainly introduces "how to realize the batch export of database to local by using ssh remote execution command under linux". In the daily operation, it is believed that many people have doubts about how to use ssh remote execution command to export database to the local problem under linux. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful for you to answer the doubt of "how to use ssh remote execution command under linux to export the database in batch to the local"! Next, please follow the editor to study!

First configure ssh to log in to the server through key.

Then the command is executed through ssh. First take a look at the ssh help documentation:

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]

The last one is to carry out the instructions. If the remote server address is 180.97.33.108, the ssh port is 998, and the ssh user name is xzc, and then you want to execute the command ls on the remote server, the script should write:

The code is as follows:

Ssh xzc@180.97.33.108-p 998 "ls"

If ssh's authentication key is configured, the files in the current directory after login will be listed, of course, the first login will prompt you to save the fingerprint of the remote server. Now that you want to export the data from the database, you need to replace the ls command with the command to export the database.

The code is as follows:

Echo "select * from user;" | mysql xzc_db-uxzc-pxzcpwd

# or

Mysql xzc_db-uxzc-pxzcpwd-e "select * from user"

The above two commands can use the database user xzc, the password xzcpwd to print the user table from the database xzc_db, using the default local database address localhost, the default port. If it is not the default, you need to specify.

Log in to OK and print OK, so the next step is to export to a file. This is also a > thing in bash. Put the above commands together as follows:

The code is as follows:

Ssh xzc@180.97.33.108-p 998 'echo "select * from user;" | mysql xzc_db-uxzc-pxzcpwd' > user.txt

This exports the user table to the local user.txt. Note that "> user.txt" is executed on the remote server if placed in'', and the exported file is on the remote server. The file is available now, but it's txt, so it's not good to hand it over to the operator. Then export excel. Unfortunately, I looked up a lot of information and couldn't find a way for mysql to export native excel without relying on third-party plug-ins or tools. But what navicat exports is the real excel, if you open it with a text tool such as notepad++, it will be garbled, and the exported file will not have coding problems. Fortunately, if a txt is segmented by tab, excel is also recognizable. So change the name of user.txt to user.xls. But the problem with this is that excel will handle the content in its own way. For example, convert a large number into scientific notation. All these have to be dealt with manually.

Finally, it is written as a script for batch operation. Attached is a script I used:

#! / bin/bash# remotely executes remote instructions through ssh # you need to deploy key authentication first to ensure that ssh only needs ip and port to connect # if you need to interact with a remote server, please refer to the-t and-tt parameters of ssh # if you need to log in to the server repeatedly to execute multiple instructions Please use ssh channel reuse # reference: http://en.wikibooks.org/wiki/OpenSSH/Cookbook/Multiplexing# needs to pay attention to exit channel when using channel For example, "ssh github.com-O exit" or "ssh github.com-O stop" #-by coding my life# sets the ssh user name, database user name, database password, Export data 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 Export data # $1 server ip# $2 ssh port # $3 instruction, multiple sql instructions such as select * from user Select * from bag; can also be executed, but the result 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-p2 "$cmd" > $EXP_PATH$4_$5 # if you want to export to a remote server, put > $EXP_PATH$4_$5 in cmd} # $1 server name # $2 ip# $3 port function exec_sqls () {cat SQLS | while read sql; do fc=$ {sql:0:1} if ["#" = "$fc"] Then # annotated continue fi # sql statements contain spaces that can no longer be distinguished by spaces. The last space is followed by the exported file name exp_file= "${sql##*}" # two # for regular matching * with maximum length and a space (* followed by space) Intercept the rest of the assignment to exp_file sql_cmd= "${sql%% $exp_file}" # two% means delete% from right to left export_remote_sql $2 $3 "$sql_cmd" $1 "$exp_file" done} # you need to create a server list file SERVERS in the current directory in the format "database name ip ssh port" For example, "xzc_game_s99 127.0.0.1 22" # you need to create a sql command list file SQLS under the current directory in the format of "files exported by sql statements", such as "select * from user" User.xls "# multiple sql please pay attention to use; separate, sql must end with; # the file name cannot contain spaces, and the final exported file is" database name _ file name ", such as" xzc_game_s99_user.xls "mkdir-p $EXP_PATHcat SERVERS | while read server; do fc=$ {server:0:1} if [" # "=" $fc "] Then # annotated ones do not handle 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

The files in the current directory are as follows, where SERVERS is the server list, which specifies the database name and ip,ss port, and SQLS specifies the sql directive and the exported file name. Neither of these files begins with # will be processed:

Xzc@xzc-HP-ProBook-4446s:~/ Desktop / remote_cmd$ lsremote_cmd.sh SERVERS SQLSxzc@xzc-HP-ProBook-4446s:~/ Desktop / 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.94 6162#xzc_game_s91 120.0.0.91 6162xzc@ Xzc-HP-ProBook-4446s:~/ Desktop / remote_cmd$ cat SQLS # select * money from money Money.xlsselect * from user; user.xlsxzc@xzc-HP-ProBook-4446s:~/ Desktop / remote_cmd$ at this point, the study on "how to use ssh remote execution commands under linux to export databases to the local batch" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.