Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

5 Answers 5

up vote 13 down vote accepted

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
share|improve this answer
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
1  
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: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)

share|improve this answer

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à!

share|improve this answer

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
share|improve this answer

This is how I would write it,

scp backup-db.sh remoteuser@208.77.188.166:/root/backups/
ssh remoteuser@208.77.188.166 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.