Backup remote database using mysqldump

Question

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.

Answer 1

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

Answer 2

I have a script that I use everyday to dump my production server to my local.

     #!/bin/sh
        ### System Setup ###
        NOW=`date +%Y-%m-%d`
        KEEPDAYS=20
        $localBackup=/backups/mysql
        #
        ### SSH Info ###
        SHOST="ssh.yourhost.com"
        SUSER="username"
        SDIR="/backup/mysql"
        #
        ### MySQL Setup ###
        MUSER="username"
        MPASS="password"
        MHOST="hostname"
        DBS="SCHEMA1 SCHEMA2 SCHEMA3"
        #
        ### Start MySQL Backup ###
        attempts=0
        for db in $DBS                            # for each listed database
        do
                echo "Start $db"
                attempts=`expr $attempts + 1`           # count the backup attempts
                ssh -C $SUSER@$SHOST mkdir $SDIR/$NOW                   #create the backup dir
                FILE=$SDIR/$NOW/$db.sql.gz        # Set the backup filename
                                                    # Dump the MySQL and gzip it up
                ssh -C $SUSER@$SHOST "mysqldump -q -u $MUSER -h $MHOST -p$MPASS $db | gzip -9 > $FILE"
                echo "End $db"
        done
        ### Make local dir with today's date
        mkdir /"$localBackup"/$NOW
        scp -C $SUSER@$SHOST:/$SDIR/$NOW/* /backups/mysql/$NOW              # copy all the files to backup server
        ssh -C $SUSER@$SHOST rm -rf $SDIR/$NOW             # delete files on db server
                                                  # deleting of old files on backup
        ###########################################################################
        ################### Save last month's last backup (this month's first day) #########################
        ###########################################################################
        dayToKeep=`date +%d`
        if [ "$dayToKeep" = "01" ]; then
                permanents="/"$localBackup"/permanents/"
                cp -rl /"$localBackup"/$NOW $permanents
        fi

        # Deletes everything older than $KEEPDAYS days
        find "$localBackup" -type d -path "$permanents" -prune -o -daystart -mtime +$KEEPDAYS -exec rm -rf {} \;

I use the -C parameter for the scp but it's not needed since the dump is already compressed, it's just that i'm used to it.

I assume the user that runs the script has a key without password (cron). If not, just add the -i parameter.