前天正在跟前端的同事调试功能。服务器开好,模拟的玩家登录好,就在倒计时。这时突然运营的同事跑过来说要统计几个服务器玩家的一些情况,也就是需要从几个服的数据库导出部分玩家的数据。好吧,我看了一下时间,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_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指令及导出的文件名。这两个文件里以#开头的都不会处理:
xzc@xzc-HP-ProBook-4446s:~/桌面/remote_cmd$ ls
remote_cmd.sh SERVERS SQLS
xzc@xzc-HP-ProBook-4446s:~/桌面/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:~/桌面/remote_cmd$ cat SQLS
#select * money from money; money.xls
select * from user; user.xls
xzc@xzc-HP-ProBook-4446s:~/桌面/remote_cmd$
到这里,脚本基本完成了要求。
相关推荐
总结,通过SSH远程执行命令批量导出数据库到本地,可以有效提高工作效率,尤其是在多服务器、多数据库的情况下。但要注意数据格式的兼容性和脚本的灵活性,以便适应不同的需求。在实际操作中,确保遵循最佳安全实践...
4. **传输文件**:通过SFTP或FTP协议,安全地在本地和远程服务器之间传输文件,如导入导出数据文件。 5. **日志记录**:记录会话中的所有交互,便于后期分析和问题排查。 在实际工作中,Oracle DBA还会结合其他工具...
- **上传文件**:Finalshell支持用户直接从本地文件系统选择文件或文件夹,通过SSH协议安全地上传到远程服务器。这在需要更新网站文件、部署应用或传输大数据时特别有用。 - **下载文件**:同样,用户可以方便地从...
5. **数据导入与导出**:支持批量导入和导出数据,可以从CSV、XML、Excel等多种格式导入,也可以导出为这些格式或其他数据库兼容格式。 6. **备份与恢复**:自动或计划备份数据库,以及快速恢复功能,确保数据安全...
SSH,全称Secure Shell,是一种网络协议,用于在不安全的网络上安全地执行命令、传输数据和管理远程系统。SSH生成工具则是专门用来简化SSH配置和自动化SSH连接过程的软件,它通常包括密钥对生成、配置文件管理以及...
在数据导入导出方面,DBEAVER提供了灵活的选项,可以将数据从CSV、Excel或其他数据库格式导入或导出到目标数据库,支持批量操作,极大地简化了数据迁移和备份的工作。 此外,DBEAVER还具备数据库对比和同步功能,...
`sshcmd`库就是这样的一个工具,它简化了通过SSH执行远程命令的过程,避免了开发者需要手动处理SSH连接和命令执行的繁琐步骤。 **sshcmd库的使用** `sshcmd`库主要包含以下核心功能: 1. **建立SSH连接**:使用...
SSH,全称为Secure Shell,是一种网络协议,用于在不安全的网络上安全地执行远程命令和传输数据。在IT行业中,SSH常用于管理服务器、配置网络服务以及进行安全的数据交换。SSH提供了加密机制,确保了在传输过程中...
8. **SSH和SSL连接**:Navicat支持通过SSH和SSL建立安全的远程数据库连接,确保数据传输的安全性。 9. **触发器和存储过程**:对于支持的数据库,Navicat允许用户创建、编辑和管理触发器和存储过程,增强数据库逻辑...
6. **导入与导出**:SQLyog允许用户批量导入和导出数据,支持多种文件格式,如CSV、TXT、XML等。这在数据迁移或者数据整合时非常有用。 7. **安全性管理**:SQLyog提供了用户权限管理功能,可以方便地创建、修改和...
1. **连接管理**:用户可以设置和保存多个数据库连接,包括远程和本地,支持SSH隧道,确保数据传输的安全性。 2. **数据可视化**:通过图表和报表,Navicat可以帮助用户直观地理解数据分布和关系。 3. **数据编辑**...
5. **数据导入导出**:支持从多种格式(如CSV、XML、Excel等)导入数据,也能将数据库内容导出为各种格式。 6. **数据备份**:定期自动备份数据库,防止数据丢失。 7. **数据库设计**:支持创建、修改和设计数据库...
6. 数据导入与导出:Navicat支持从多种文件格式(如CSV、Excel、XML等)导入数据到数据库,同时也可将数据导出为各种格式,方便数据交换。 7. 数据备份:定期自动备份数据库,确保数据安全。可以设置备份计划,根据...
还有数据导入导出功能,可以将数据从其他格式(如CSV、Excel)导入到数据库,或者将数据库数据导出为各种格式。此外,Navicat还具备任务计划器,可以定时执行备份、数据同步等操作,自动化日常维护工作。 Navicat的...
5. **数据传输**:在多个数据库之间迁移数据是常见的需求,Navicat允许用户将数据从一个数据库迁移到另一个,或者进行数据的批量导入和导出。 6. **图表生成**:Navicat可以从数据库中的数据生成各种图表,如柱状图...
在数据库管理方面,Navicat支持数据导入导出、数据同步、备份和恢复等功能。用户可以批量处理数据,执行SQL脚本,甚至设置计划任务自动执行这些操作,确保数据的一致性和安全性。 在开发和调试方面,Navicat ...
在MySQL备份和还原场景中,SecureCRT可以用于连接到远程服务器,执行SQL脚本或系统命令,从而实现数据库的备份。 1. **连接管理**:SecureCRT支持保存和管理多个服务器的连接配置,方便快速接入。 2. **多窗口操作*...
另外,数据导入/导出功能支持从多种文件格式导入数据到数据库,或者将数据库中的数据导出为各种格式。 ### 5. 数据同步与备份 Navicat的数据库同步功能可以帮助用户分析两个数据库之间的差异,并生成同步脚本来...