Temporary SSH Tunnel for backup purposes

I'd like to write a shell script (currently using bash) to automatically back up the content of several MySQL schemas on a remote server. The remote server is locked down to only allow SSH access so I have to create an SSH tunnel before running mysqldump against the various schemas.

I can create a tunnel without any issue, however I'd like to be able to automatically close it after the database dump has completed.

Currently my script is doing this:

/usr/bin/ssh -T -f -L 4444:127.0.0.1:3306 -l remoteuser 208.77.188.166 sleep 600

/usr/bin/mysqldump --compress -h 127.0.0.1 -P 4444 -u user -ppassword db1 | gzip > /root/backups/snapshot/db1.sql.gz

/usr/bin/mysqldump --compress -h 127.0.0.1 -P 4444 -u user -ppassword db2 | gzip > /root/backups/snapshot/db2.sql.gz

/usr/bin/mysqldump --compress -h 127.0.0.1 -P 4444 -u user -ppassword db3 | gzip > /root/backups/snapshot/db3.sql.gz

Where the connection is kept open for 600 seconds, obviously however if one of the first dumps takes longer than that then the connection is closed before the other dumps complete. I'd like to retain separate files for each schema backup (so will avoid the --databases of mysqldump for now).

Any suggestions?

Solution 1:

You don't need to bother with all that tunneling :-).

Just let mysqldump stream its data using the SSH connection:

ssh usr@host mysqldump -u dbuser -ppasswd my-database-name >dumpfile

Solution 2:

Add the -N option, the -f option and the sleep 600, this will open the tunnel without running it in the background. Then you can run the command with &, get the PID, then kill the ssh process once the jobs have completed.

/usr/bin/ssh -T -L 4444:127.0.0.1:3306 -l remoteuser 208.77.188.166 &
PID=$!
do_stuff
kill $PID

(I've tested this with bash - you may need to change things for a different shell)

Solution 3:

A slight variation on sleske's suggestion, you can pipe the mysqldump output through gzip to compress before transfer:

ssh SSH-USER@SERVER mysqldump -u DB-USER -pDB-PASSWORD DB-NAME | gzip -c > DB-NAME.sql.gz

Solution 4:

As sleske said, why bother in this particular case ? However there is a solution to control an ssh tunnel in the general case : use a named pipe. First create the pipe like this :

ssh -l remoteuser 208.77.188.166 mkfifo /tmp/PIPO

Then you write (blocking to the pipe) in your ssh to create the tunnel :

/usr/bin/ssh -T -f -L 4444:127.0.0.1:3306 -l remoteuser 208.77.188.166 "echo T > /tmp/PIPO"

When you want to close the tunnel, just read the pipe :

ssh -l remoteuser 208.77.188.166 cat /tmp/PIPO

Et voilà!

Solution 5:

This is how I would write it,

scp backup-db.sh [email protected]:/root/backups/
ssh [email protected] exec /root/backups/backup-db.sh

Where the script is,

#!/bin/sh
# backup-db.sh
DUMPARGS=--compress -h 127.0.0.1 -P 4444 -u user -ppassword
BACKUP_PATH=/root/backups/snapshot

/usr/bin/mysqldump $DUMPARGS db1 | bzip2 > $BACKUP_PATH/db1.sql.bz2
/usr/bin/mysqldump $DUMPARGS db2 | bzip2 > $BACKUP_PATH/db2.sql.bz2
/usr/bin/mysqldump $DUMPARGS db3 | bzip2 > $BACKUP_PATH/db3.sql.bz2

Finally, the archive can be scped back with another command.
Yes, I did not pipe or tunnel.