SSH + MysqlDump Remote Backup Script

Question

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:

i.e.

ssh [email protected] mysqldump $dbname -u admin -p > dbdump.dmp && tar cvzf dbdump.tar.gz dbdump.dmp

Issues:

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.

Answer 1

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.

Answer 2

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 [email protected] "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

Answer 3

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/).