Dumping mysql through ssh tunnel

Shell Programming and Scripting
1

Hello wise scripters
This is my first post and I am glad to join this community.

I am trying to make work a mysqldump via ssh tunnel in a shell script.
The problem is that running this sequence of commands manually works fine, but when I put these in a bash script they give me the following error

"mysqldump: Got error: 2003: Can't connect to MySQL server on '127.0.0.1' (111) when trying to connect"

piece of script:

ssh -N xxx.xxx.xxx.xxx -L 3307:127.0.0.1:3306& 
mysqldump -P 3307 -h 127.0.0.1 -u user -ppassword remote_db_name | mysql local_db_namei -u user -ppassword

I hope someone can help to get out or at least give the right hint to try something else

Thanks!

2

Does your SSH connect? Is port forwarding allowed on the remote host? Is your SSH session still running when your script tries to run the dump?

3

It probably means what it says -- it can't connect to your mysql server. You should make sure ssh actually works in your script. Try

ssh -N xxx.xxx.xxx.xxx -L 3307:127.0.0.1:3306 echo "hello world"

just to see if it prints hello world. If not, there's an issue with your ssh.

4

Can I suggest also using -g and -T:

$ ssh -g -T -N xxx.xxx.xxx.xxx -L 3307:127.0.0.1:3306&

For debugging purposes you could also run with the -v option in another terminal without background & and see if mysqldump is connecting properly you should see something like this:

$ ssh -v -g -T -N xxx.xxx.xxx.xxx -L 3307:127.0.0.1:3306
...
debug1: Local forwarding listening on 0.0.0.0 port 3307.
debug1: channel 1: new [port listener]
debug1: Requesting no-more-sessions@openssh.com
debug1: Entering interactive session.
debug1: Connection to port 3307 forwarding to 127.0.0.1 port 3306 requested.
debug1: channel 2: new [direct-tcpip]
debug1: channel 2: free: direct-tcpip: listening port 3307 for 127.0.0.1 port
3306, connect from 127.0.0.1 port 65247 to 127.0.0.1 port 3307, nchannels 3
5

The SSH connection could work, but if the remote host is configured to disallow port forwarding the local dump process could still fail to connect.

I'm not familiar enough with the behavior of various versions of SSH to know what happens if port forwarding is disallowed. The basic SSH connection could still work.

6

He implies it works in shell but not in script, so I think it's safe enough to assume port forwarding is allowed. Autologin may be failing in some fashion however.

7

As said by Corona688, I implied that all the commands worked fine in shell.

I checked out if it is an autologin (sshpass) issue and i think thats not the case because if i run all commands in script except "mysqldump -P 3307 -h 127.0.0.1 -u dbuser -pdbpass dbname --result-file=dump.sql" all works fine and i can (remote) dump the DB.

Any suggestion on how i can troubleshoot this??? looking at mysql logs didn't help

thanks in advance

edit: i will post the entire script asap

8

How did you go with suggestions/debugging recommendations in post #4?

1 Like
9

Thanks Chubler_XL!

thanks to the option "-v" i was on the right way to find the solution... the verbose output let me see that the script launches the remote dump BEFORE the tunnel was completely established, thats because the "&" (fork) used establishing the tunnel made it goes asynchronous.

The easy solution was putting a "sleep 3" right after the tunnel command.

great thanks!