Take the 2-minute tour ×

I am trying to create a sync.sh script that coordinates with our less-than-ideal version control for our website. This script will migrate a live copy of the database to our development environment and a couple other things. I'm having trouble with the mysql migration portion.


# database

# ssh tunnel
ssh -L 3307:remote:3306 user@remote

# mysql dump

# somehow close ssh tunnel ???

# populate local db with sql dump file
mysql -ulocal db < localfile.sql

# other sync stuff
# ...


Enter password: 


share|improve this question

3 Answers 3

up vote 5 down vote accepted

Just pull the dump with one command:

ssh user@host 'mysqldump -u -p databasename' > /path/to/local/file.sql

You can also compress these as well if your database is big:

ssh user@host 'mysqldump -u -p databasename | gzip' > /path/to/local/file.sql.gz

and then gunzip it after.

share|improve this answer
Thank you for helping me make life simpler! –  naomi Mar 14 '12 at 18:03
Oh damn, seems I didn't see the forest for the trees ;-) –  Marian Mar 16 '12 at 15:58

The ssh -L 3307:remote:3306 user@remote command will never return, because it will just open a shell and keep it open.

SSH behaves as follows: It keeps the connection open as long as either a command is executed or the tunnel is used. So what you want to do is ssh user@remote sleep 10 &. This gives the mysqldump command enough time to open the connection, but as soon as mysqldump closes that connection, the tunnel is unused and SSH exits.

btw, you may have to use 3307:localhost:3306, if the server does not allow external connections. (This works because the host name is resolved on the server side, not on your client)

share|improve this answer

Perhaps another approach is warranted- two scripts. Script A would be the script that needs to be executed on the remote host. Script B would be a controlling script that 1) copies Sctipt A to the remote server, 2) SSHs to the remote server and executes the copied script, 3) copies the resulting dump file back and 4) loads the local db

share|improve this answer

Your Answer


