在命令行中通过 ssh 在远程机器上运行 MySQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17544954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Run MySQL query on remote machine through ssh in command line
提问by lk121
I am trying to run MySQL query on remote machine with this command:
我正在尝试使用以下命令在远程机器上运行 MySQL 查询：
ssh [email protected] "mysql -uroot -proot -e \"use test";""
I am not able to use that database.
我无法使用该数据库。
Please suggest a working command.
请建议一个工作命令。
回答by kun tang
Try this:
尝试这个：
mysql -h host -u root -proot -e "show databases;";
回答by King-Wzrd
Try this:
尝试这个：
ssh root@host "mysql database -e 'query to run on table_name; more queries to run;'"
Same can be done with user@hostif that user has permission to execute SQL queries let alone launch mysql in general. Using -eis the same as --execute, which will run whatever you put within the trailing quotes (single or double) and quit. The standard output format would be the same as you would see using --batch.
user@host如果该用户有权执行 SQL 查询，更不用说启动 mysql 了，也可以这样做。Using-e与 相同--execute，它将运行您在尾随引号（单引号或双引号）中放置的任何内容并退出。标准输出格式与您使用--batch.
回答by Vicky T
MySql seems to have a special command line syntax which includes the database.
MySql 似乎有一个特殊的命令行语法，其中包括数据库。
mysql -u user -p -e 'SQL Query' database
mysql -u 用户 -p -e 'SQL 查询' 数据库
This documentation is rather old but I got it to work
这个文档很旧，但我让它工作了
http://www.cyberciti.biz/faq/run-sql-query-directly-on-the-command-line/
http://www.cyberciti.biz/faq/run-sql-query-directly-on-the-command-line/
Final working command with ssh:
使用 ssh 的最终工作命令：
ssh user@host "mysql -u user -e 'show tables;' databasename"
ssh user@host "mysql -u user -e '显示表；' 数据库名称”
回答by OZZIE
This ended up working for me in a bash script:
这最终在 bash 脚本中为我工作：
query='USE [database]; SELECT ...'
mysql='mysql -u [username] -p[password] -e '"'""$query""'"
ssh [username]@[server] -t "$mysql"
If you want to make it more safe then add a prompt for the password instead of storing it somewhere potentially unsafe.
如果您想让它更安全，请添加密码提示，而不是将其存储在可能不安全的地方。
回答by ling
This worked for me after a few tests (basically same answer as @King-Wzrd):
经过几次测试后，这对我有用（与@King-Wzrd 的答案基本相同）：
ssh -t kom "mysql -uroot -p -e 'show databases;'"
ssh -t kom "mysql -uroot -p < /home/ling/websites/jin_test/.deploy/tmp.sql"
The "trick" was the quotes around the command.
“技巧”是命令周围的引号。
The -toption allows for prompting password interactively via the remote shell.
该-t选项允许通过远程shell提示交互密码。
The komhere is just a ssh config identifier defined in my ~/.ssh/configfile (see more here: https://www.cyberciti.biz/faq/create-ssh-config-file-on-linux-unix/).
这里的kom只是在我的~/.ssh/config文件中定义的一个 ssh 配置标识符（在这里查看更多信息：https: //www.cyberciti.biz/faq/create-ssh-config-file-on-linux-unix/）。
