7

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

3 Answers 3

7

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.

2
  • Oh damn, seems I didn't see the forest for the trees ;-)
    – Marian
    Mar 16, 2012 at 15:58
  • 1
    This requires that mysqldump is installed on the remote machine and is the desired version etc.
    – AndreKR
    Apr 12, 2015 at 6:58
4

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)

1
  • 1
    Using -f instead of & allows to enter a password first
    – AndreKR
    Apr 12, 2015 at 7:22
0

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

You must log in to answer this question.

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