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?
Add a comment
|
3 Answers
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
3 Comments
Nikko
This helped me a lot when I was migrating servers. Hope the asker can select this as the answer to the question.
Mouradif
What if I don't have ssh access but I can connect to the distant mysql server via my local "mysql --host=mydomain.com --user=user --pass=pass ? How do I retrieve the *.sql file I need to dump ?
marcovtwout
Is there a limit to database dump sizes when using the first method (pipe)?
Just thought I'd add to this as I was seriously low on space on my local VM, but if the .sql file exists already on the remote server you could do;
ssh <ip-address> "cat /path/to/db.sql" | mysql -u <user> -p<password> <dbname>
