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: -l remoteuser sleep 600

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

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

/usr/bin/mysqldump --compress -h -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?


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
  • 1
    +1 for sidestepping the issue. This does require that mysqldump be available on the remote host, and I believe shows the password on the remote server process list, but assuming those things aren't a problem, that sounds like a much better solution. – Mark Jul 6 '09 at 16:34
  • 3
    In reply to marks comment "Mark Jul 6 '09 at 16:34" about the password in the remote server process list (I have not enough reputation to add a comment): You can create a .my.cnf file in the users home directory on the remote server and specify the password there: [client] password="secret" Then just use mysqldump (here with compression to speed up the data transfer): $ ssh user@host "mysqldump foobar | gzip -9" | gzip -d > foobar.sql – Thomas Schuster Sep 3 '11 at 20:56

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: -l remoteuser &
kill $PID

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


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
  • I suspect that this command does not compress until AFTER the transfer, you may need to quote the "mysql ... | gzip" bit so the pipe gets evaluated remotely – The Mighty Chris Sep 25 '15 at 2:04

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 mkfifo /tmp/PIPO

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

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

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

ssh -l remoteuser cat /tmp/PIPO

Et voilà!


This is how I would write it,

scp backup-db.sh remoteuser@
ssh remoteuser@ exec /root/backups/backup-db.sh

Where the script is,

# backup-db.sh
DUMPARGS=--compress -h -P 4444 -u user -ppassword

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

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.