Mysql – Backup remote database using thesqldump

database-backupMySQLrsync

I am trying to achieve a quick way to do a backup and download a remote mysql database to my local machine, using just the terminal.
The remote server uses ssh keys for authentication.
I can do it by running seven sequential commands on the terminal, some executed in the server, others from my local machine:

1) connect to remote server using ssh key;
2) dump the database to a remote directory;
3) logout from server;
4) download the backup to my local machine;
5) connect to server again;
6) remove the backup from the remote directory;
7) logout

#1 ssh -i my_rsa_key my_user@my_domain.tld
#2 mysqldump -u my_db_user -pmy_db_password my_db > my_path_to_backup_directory/backup.sql
#3 logout
#4 rsync -chavzP -e 'ssh -i my_rsa_key -C -c blowfish' my_user@my_domain.tld:/my_path_to_backup_directory/backup.sql  /my_path_to_local_directory
#5 ssh -i my_rsa_key my_user@my_domain.tld
#6 rm my_path_to_backup_directory/backup.sql
#7 logout

Question 1: is this workflow ok or is there a "cleaner" way of doing this?
Question 2: can I automate these commands in a single batch, for me to just type one command in the terminal and get all seven steps done? I have tried chaining the commands using

&&

but I guess it doesn't work since I am executing part of the commands from my local machine (such as rsync) and part from the remote (such as mysqldump).

All help appreciated,
thank you.

Best Answer

Give ssh the mysqldump command to run but don't redirect it to a file. That will bring stdout from the mysqldump back to your local machine where you can redirect that to a local file.

ssh -i my_rsa_key my_user@my_domain.tld 'mysqldump -u my_db_user -pmy_db_password my_db' > /local/path/to/store/backup.sql