Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

This script is run on a development machine. remote is the live host.

# --------------
# database

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

# mysql dump
mysqldump -u someuser -h remote -P 3307 -p"p4ssw0rd" db > localfile.sql

# somehow close ssh tunnel ???

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

# -----------------
# other sync stuff
# ...

When I just run the mysql portion of the script, I'm getting this output:

Pseudo-terminal will not be allocated because stdin is not a terminal.
Enter password: 

We have ssh keys setup and I can ssh to the remote properly. However, when run in the script context, things are going wrong. I think I'm just a total bash noob. I'm guessing that if I simply change the structure of my approach, I should have no problem. I'm just not sure how to do that...

share|improve this question
add comment

3 Answers

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
add comment

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
add comment

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
add comment

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.