通过cli从远程mysql导入sql服务器mysql import sql via cli from remote server

- 此内容更新于:2014-12-30
主题:

我知道如何导入一个sql通过cli文件: 但如果转储。是本地的sql文件。我怎么能使用远程服务器上的文件?

原文:

i know how to import an sql file via the cli:

mysql -u USER -p DBNAME < dump.sql

but that's if the dump.sql file is local. how could i use a file on a remote server?

解决方案:
原文:

You didn't say what network access you have to the remote server.

Assuming you have SSH access to the remote server, you could pipe the results of a remote mysqldump to the mysql command. I just tested this, and it works fine:

ssh remote.com "mysqldump remotedb" | mysql localdb

I put stuff like user, password, host into .my.cnf so I'm not constantly typing them -- annoying and bad for security on multiuser systems, you are putting passwords in cleartext into your bash_history! But you can easily add the -u -p -h stuff back in on both ends if you need it:

ssh remote.com "mysqldump -u remoteuser -p'remotepass' remotedb" | mysql -u localuser -p'localpass' localdb

Finally, you can pipe through gzip to compress the data over the network:

ssh remote.com "mysqldump remotedb | gzip" | gzip -d | mysql localdb
Nikko的回复:这帮助我很多时候迁移服务器。希望请求者可以选择这是这个问题的答案。

(原文:This helped me a lot when I was migrating servers. Hope the asker can select this as the answer to the question.)

Dave Kiss的回复:当SQL文件已经存在呢?

(原文:What about when the SQL file already exists?)

解决方案:
我使用wget下载它到一个文件或管道。
原文:

I'd use wget to either download it to a file or pipe it in.

解决方案:
您必须ssh到远程服务器,运行mysql客户端。
原文:

You must ssh into the remote server and run the mysql client there.