I'm trying to issue a remote mysqldump command, redirect stdout to a dmp file, then tar that up.

I'm a bit confused as to how to do the redirection bits over ssh:


ssh user@host.com mysqldump $dbname -u admin -p > dbdump.dmp && tar cvzf dbdump.tar.gz dbdump.dmp


1) I'm not providing the password because I want it to prompt me. Will an ssh remote command deal with this? 2) What's the deal with the syntax? Do I want to use quotations, or don't I? What happens with the redirects and pipes? Do those have to be escaped or formatted in some special fashion.


When you connect to another host, SSH executes the given command on the server side (if there is none given, it starts a shell), forwarding all the stdin/stdout/stderr streams. The ssh command ends at the >. This means the output of the mysql command is redirected to the file dbdump.dmp on the client side. Then, still on the client side, a tar archive is created from that file.

If you put the whole command string in quotes like Dom proposed, it'll be run completely on the remote side, that way you'll have the files on your server.

In theory, the password thing should work, too (since the stdin stream is not redirected but is just passed), but it didn't here.

You may put the password in the .my.cnf (if you want extra security, create an extra user just for the backup). You may also use public key login for that account, that way it'll work completely without your interaction.

BTW: Why do you create a tar archive of the dump? If you only need the compression, gzip or something like that is entirely sufficient.

| improve this answer | |

On which computer do you want the tar be executed ? if it is on the server, you need to quote all the line like : ssh user@host.com "mysqldump $dbname -u admin -p > dbdump.dmp && tar cvzf dbdump.tar.gz dbdump.dmp" If you don't, I think the end of line will be executed locally and won't work as you don't have the dbdump file For 1), there is no problem. If a password is needed, it will be asked. [EDIT] OOPS, I double check, you speak about MySQL password, and not SSH ! I think however that it will be asked [EDIT2] Just check and it works with the quotes : first the SSH password is ask, and after, the MySQL password

| improve this answer | |

This worked for me (after a few tests):

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 -t option allows for prompting password interactively via the remote shell.

The kom here is just a ssh config identifier defined in my ~/.ssh/config file (see more here: https://www.cyberciti.biz/faq/create-ssh-config-file-on-linux-unix/).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.