13

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?

5 Answers 5

36

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
2
  • 2
    +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, 2009 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 Sep 3, 2011 at 20:56
5

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)

4

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
2
  • 1
    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 Sep 25, 2015 at 2:04
  • I confirm that this runs gzip locally and hence after the transfer. If you want to run gzip on remote, you need to use appropriate quotes: "mysql ... | gzip"
    – A.J.
    Jan 31, 2022 at 13:25
3

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

2

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .