0

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.

  • Use SSH port forwarding and run mysqldump from your local machine maybe? – HBruijn Nov 18 '15 at 17:24
2

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
  • Thank you David, seems the effective way to do it. However, I get the backup.sql file on the local path, but with no content - 0 bytes file. Do you have an idea on why this happens? EDIT: tried reseting the terminal and it worked, don't know what happened. But thanks! Great solution. – BMM Nov 18 '15 at 19:24
0

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.

  • thanks for helping, but found the solution I intended above! (by using just the terminal). Thanks! – BMM Nov 18 '15 at 19:27

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.