通过 SSH 从 BASH 编写 MySQL 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9951058/
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
scripting MySQL from BASH over SSH
提问by Jason
I have a CentOS server that I want to script from BASH running it on my laptop.
我有一个 CentOS 服务器，我想从 BASH 编写脚本，在我的笔记本电脑上运行它。
run a script locally that: - logs into the server over ssh and executes some mysql statements - copies files that I need to where I need them
在本地运行一个脚本： - 通过 ssh 登录到服务器并执行一些 mysql 语句 - 将我需要的文件复制到我需要的地方
THe file copying is easy. I can do that.
文件复制很容易。我可以做到。
But how can one connect to a MySQL server over the SSH port and execute statements? I think I am just stuck on the connect part. executing the statements I can build up on a variable and batch execute.
但是如何通过 SSH 端口连接到 MySQL 服务器并执行语句呢？我想我只是停留在连接部分。执行我可以在变量上建立的语句并批量执行。
I have an SSH pub/priv keypair from my laptop to this server as well.
我也有一个从我的笔记本电脑到这台服务器的 SSH pub/priv 密钥对。
any help?
有什么帮助吗？
回答by K. Norbert
You can specify commands to run on the remote machine, as the last argument to ssh:
您可以指定要在远程计算机上运行的命令，作为 ssh 的最后一个参数：
ssh user@remote 'mysql -u user ...'
The problem with this is that it will be a hassle to deal with the various '" escaping in the mysql command(s).
这样做的问题是处理 mysql 命令中的各种 '" 转义会很麻烦。
A better way, in my opinion, is to open an SSH tunnel to the remote machine when you connect:
在我看来，更好的方法是在连接时打开到远程机器的 SSH 隧道：
ssh -L 12341:127.0.0.1:3306 user@server &
This would connect your local 12341 port, to the remote machine's 3306 (mysqld) port. After the connection is done, you can connect to it from your local machine like:
这会将您本地的 12341 端口连接到远程机器的 3306 (mysqld) 端口。连接完成后，您可以从本地计算机连接到它，例如：
mysql -h 127.0.0.1 -p 12341
So you can place your SQL statements into a file, and cat it into mysql:
所以你可以把你的 SQL 语句放到一个文件中，然后把它放到 mysql 中：
cat commands | mysql -h 127.0.0.1 -p 12341
Don't forget to kill the SSH connection after you are done.
完成后不要忘记终止 SSH 连接。
Note that tunneling requires the remote server to have PermitTunnel "yes"in it's sshd_config.
请注意，隧道连接要求远程服务器在其 sshd_config 中有PermitTunnel “yes”。
回答by undef
just use ssh to run mysqlon the remote server. For example
只需使用 sshmysql在远程服务器上运行即可。例如
ssh user@server 'mysql -uimauser -p imadb -e "select * from table"'.
ssh user@server 'mysql -uimauser -p imadb -e "select * from table"'.
Everything in quotes will be run remotely by ssh.
引号中的所有内容都将由 ssh 远程运行。
回答by álvaro Justen
You can do what @WishCow said or you can put all MySQL statements in a .sqlfile, copy this file to server and then call mysql to execute these statements, something like this:
您可以按照@WishCow 所说的进行操作，也可以将所有 MySQL 语句放在一个.sql文件中，将该文件复制到服务器，然后调用 mysql 来执行这些语句，如下所示：
echo "show databases;" > test.sql
echo "use some_database;" >> test.sql
echo "show tables;" >> test.sql
scp test.sql remote-user@remote-server:
ssh remote-user@remote-server 'mysql -u mysql-user -pmysql-pass < test.sql'
