mysql import sql via cli from remote server

Question

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?

Answer 1

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